Re: 4gl: Week Ending Queyr
If I understand correctly and you want invoice
totals by week, product then this might work:
declare pr_tot_cur cursor for
select product, sum(),count(), (invoice_date + ((7-weekday(invoice_date)) mod 7))
where shift_code = cshift
group by 4,1
order by 4,1
foreach pr_tot_cur into r_inv.*, wk_date
.
.
end foreach
Although I'd probably do it this way if there was an index on
invoice_date and there were more than a few invoices:
select min(invoice_date), max(invoice_date)
into s_date, e_date
from invoices
where shift_code = cshift
#Round the dates up to a Sunday
let s_date=s_date+((7-weekday(s_date)) mod 7)
let e_date=e_date+((7-weekday(e_date)) mod 7)
let sel_str =
"select product, sum(), count() ",
"from invoices ",
"where shift_code=",cshift," ",
"and invoice_date between ? and ? ",
"group by product ",
"order by product "
prepare pr_tot_id from sel_str
declare pr_tot_cur cursor for pr_tot_id
for c_date=s_date to e_date step 7
#I'm not sure i've ever tried dates in a for loop
open pr_tot_cur using c_date-6, c_date
foreach pr_tot_cur into r_inv.*
.
.
end foreach
end for
John Cokos wrote:
>
> Help!
>
> I need to produce a report that groups data by "week-ending".
> the "week" ends on Sunday. How the @#$% do I group records
> for this. I've tried to every unique 'invoice_date' where
> WeekDay(invoice_date) = 0 into an array, then do a foreach
> grouping all data where the invoice_date is between
> invoice_date - 6 and invoice_date, but I either get garbage or
> an error stating that a subquery has retrieved not exactly one row.
> (There is no subquery, it is a straigt select)....
> DECLARE date_cur CURSOR FOR
> SELECT UNIQUE invoice_date FROM invoices
> WHERE shift_code = cShift {Entered by user}
> AND WeekDay(invoice_date) = 0>
> FOREACH date_cur INTO qDate
> LET stDate = qDate - 6
> LET eDate = qDate
>
> SELECT product, sum(invoice_amt), count(*)
> INTO r_Inv.product, r_Inv.invoice_amt, r_Inv.num
> WHERE shift_code = cShift
> AND invoice_date BETWEEN stDate and eDate
> GROUP BY product
> ORDER BY product>
> {The query above generates the "Not Exactly one row" error }
>
> DISPLAY BY NAME r_Inv.*
>
> Any Ideas on what I'm doing wrong ....
>
> Thanks
> John Cokos