Oracle-L: RE: Date-based query Q

From: Nicoll, Iain <IAIN.D.NICOLL_at_saic.com>
Date: Wed, 29 Oct 2003 04:44:25 -0800
Message-ID: <F001.005D4EFB.20031029044425@fatcity.com>

You colud try joining to an in-line view something like

SELECT :XDATE+(ROWNUM-1) DDATE
FROM DBA_OBJECTS
WHERE ROWNUM <= (:YDATE - :xdate)+1

where dba_objects could be any table with enough rows to ensure you always covered the complete range.

-----Original Message-----
Aidan Whitehall
Sent: 29 October 2003 10:49
To: Multiple recipients of list ORACLE-L

This is probably a no-brainer...

We have some date-based data for which most days have several records but where some days have none. I'm COUNT()ing the number of records for each day (between day x and day y) and need a record set that also includes a row for those days which have no records:

UkDate	Total
1/1/2003	5
2/1/2003	6
3/1/2003	0
4/1/2003	6

I could post-process the record set to achieve this, but is there any way in 9i to do an aggregate query with an outer join on a date range (if that makes sense)?

Someone made the suggestion of creating another table with a row for every day under the sun in it, against which you could inner join the main query, but I'm not keen on that (that is just a gut response though).

Any ideas? Thanks!

--

Aidan Whitehall <mailto:aidanwhitehall_at_fairbanks.co.uk> 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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Aidan Whitehall
INET: AidanWhitehall_at_Fairbanks.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Nicoll, Iain
INET: IAIN.D.NICOLL_at_saic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------