--- In firebird-support@yahoogroups.com, "women_lover_best"
<talbronstien@g...> wrote:

>
> right ryan,
> it is returning duplicate record for last record..so what should i

do?

You are joking right? Remove the second unnecessary suspend. It is
really quite simple, if you want it to return a record, call suspend.
If you call suspend 50 times, you will get 50 records.

Like this


BEGIN
/* Procedure body */

for
select U.firstname, U.lastname, SH.transactiondate, '12am',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '00:00:00'
and cast(SH.transactiontime as time) < '01:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '1am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '01:00:00'
and cast(SH.transactiontime as time) < '02:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
UNION

select U.firstname, U.lastname, SH.transactiondate, '2pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '02:00:00'
and cast(SH.transactiontime as time) < '03:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
UNION

select U.firstname, U.lastname, SH.transactiondate, '3am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '03:00:00'
and cast(SH.transactiontime as time) < '04:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '4am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '04:00:00'
and cast(SH.transactiontime as time) < '05:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '5am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '05:00:00'
and cast(SH.transactiontime as time) < '06:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '6am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '06:00:00'
and cast(SH.transactiontime as time) < '07:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '7am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '07:00:00'
and cast(SH.transactiontime as time) < '08:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
UNION
select U.firstname, U.lastname, SH.transactiondate, '8am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '08:00:00'
and cast(SH.transactiontime as time) < '09:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
UNION
select U.firstname, U.lastname, SH.transactiondate, '9am ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '09:00:00'
and cast(SH.transactiontime as time) < '10:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '10am',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '10:00:00'
and cast(SH.transactiontime as time) < '11:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '11am',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '11:00:00'
and cast(SH.transactiontime as time) < '12:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '12pm',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '12:00:00'
and cast(SH.transactiontime as time) < '13:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
UNION
select U.firstname, U.lastname, SH.transactiondate, '1pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '13:00:00'
and cast(SH.transactiontime as time) < '14:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '2pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '14:00:00'
and cast(SH.transactiontime as time) < '15:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '3pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '15:00:00'
and cast(SH.transactiontime as time) < '16:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '4pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '16:00:00'
and cast(SH.transactiontime as time) < '17:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '5pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '17:00:00'
and cast(SH.transactiontime as time) < '18:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
UNION
select U.firstname, U.lastname, SH.transactiondate, '6pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '18:00:00'
and cast(SH.transactiontime as time) < '19:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '7pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '19:00:00'
and cast(SH.transactiontime as time) < '20:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '8pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '20:00:00'
and cast(SH.transactiontime as time) < '21:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '9pm ',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '21:00:00'
and cast(SH.transactiontime as time) < '22:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '10pm',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '22:00:00'
and cast(SH.transactiontime as time) < '23:00:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate

UNION
select U.firstname, U.lastname, SH.transactiondate, '11pm',count
(pk_transactionid) from saleshead sh,Users U
where pk_transactionid in (select distinct pk_transactionid from
salesdetails where saletype not in(6,8)) and
cast(SH.transactiontime as time) >= '23:00:00'
and cast(SH.transactiontime as time) < '23:59:00'
and U.pk_userid=sh.fk_userid
group by U.firstname, U.lastname ,SH.transactiondate
INTO
:USERFNAME, :USERLNAME, :RSALEDATE, :PERIOD, :NSALES
do suspend;

END