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 John, as I suppose your invoices table contain records from more than one product declare & use a cursor for your 2nd select statement as it obviously returns more than one row. Regards Tolis +---------------------------------------------------------------------+ | V+K Relational Solutions email: tvarnas@compulink.gr | | Deligiorgi 26 tvarnas@orbit.de | | 546 42 Thessaloniki Voice: (30) 31 820270 | | Greece Fax: (30) 31 865463 | +---------------------------------------------------------------------+