Re: 4gl: Week Ending Queyr
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
>
From what I gather about the problem, you could try this approach to
solving the problem. Essentially, it involves
1) Doing the grouping within the report.
2) Sending the 'week number' information to the report.
Your code could look like the following
DEFINE
l_weekno INTEGER, # Integer is important
...
LET l_basedate = MDY(12,8,1990) # Any arbitrary Sunday before stDate
DECLARE inv_curs CURSOR FOR
SELECT * INTO l_iv.*
FROM invoices
WHERE shift_code = cShift
AND invoice_date BETWEEN stDate and eDate
FOREACH inv_curs
LET l_weekno = (l_basedate - l_iv.invoice_date) / 7
OUTPUT TO REPORT (l_iv.*, l_weekno)
...
Then, within the report
ORDER BY ...,l_weekno,... # Sequence the variables as required
AFTER GROUP OF l_weekno
PRINT....
----------------------
Rudy Fernandes
GIC, Kuwait
----------------------