current row = 1
loop through your date range, day by day
numrecords = 0
output the current date
loop while (using array notation) test and see if the date in record[current row] matches the current day
numrecords = numrecords + 1
end loop
output numrecords
end loop
__________/ Aidan Whitehall \__________
I've got some date-based data where, on some days, there are no records in the table. I'm summing the number of records for each day and want to end up with a recordset along the lines of:
Date Total 1/1/2003 5 2/1/2003 6 3/1/2003 0 4/1/2003 6
As some days have no records, a vanilla aggregate query won't have a record for those days where there is no data, so was wondering if there was any way in Oracle of doing an aggregate query with an outer join on a date range (if that makes sense), so that those days with no records have a row with 0 for Total.
The only other way I can think of doing it is creating a new query object, looping through the date range specified in the first query and using QoQ to populate the new query... but can it be done in the database?
I've got a feeling the answer is going to be soooooo obvious.
Thanks
-- Aidan Whitehall <mailto:[EMAIL PROTECTED]> Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 Queen's Awards Winner 2003 <http://www.fairbanks.co.uk/go/awards>
________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
-- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
-- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
