Could you do this by building a dates table and joining to that? I remember in a previous company having such a set-up when we regularly used a dates table made up of columns such as DDMMYYYY, MMDDYYYY, Period, Month etc etc..
I appreciate am lot of this has been superseded by dateformat and the like, but I think it would give you to result you require? Better than looping through each date and running a separate query.... -----Original Message----- From: Aidan Whitehall [mailto:[EMAIL PROTECTED] Sent: 28 October 2003 11:58 To: [EMAIL PROTECTED] Subject: [ cf-dev ] Quick query question... 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] DISCLAIMER: This e-mail is confidential and may also be legally privileged. If you are not the intended recipient, use of the information contained in this e-mail (including disclosure, copying or distribution) is prohibited and may be unlawful. Please inform the sender and delete the message immediately from your system. This e-mail is attributed to the sender and may not necessarily reflect the views of the Patsystems Group and no member of the Patsystems Group accepts any liability for any action taken in reliance on the contents of this e-mail (other than where it has a legal or regulatory obligation to do so) or for the consequences of any computer viruses which may have been transmitted by this e-mail. The Patsystems Group comprises Patsystems plc and its subsidiary group of companies. -- ** 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]
