Date-based query Q

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: Uk

RE: Date-based query Q

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:

Re: Date-based query Q

Hello Aidan, I ran into a similar situation once, except that I needed a row not for every day, but for every month. My solution at that time was, in fact, to create a table with a row for each month for the next hundred years (only 1200 rows). I also wrote (and documented) a small program to exte

RE: Date-based query Q

Thanks for everyone's help with this one, btw. In the end I bit the bullet and added a dates table. -- Aidan Whitehall Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 Queen's Awards Winner 2003 ___

RE: RE: Date-based query Q

Same idea as what Iain suggests, dreadful implementation : SQL> select trunc(ukdate) ukdate, count(*) from test 2 group by trunc(ukdate); UKDATE COUNT(*) -- -- 01/01/2003 5 02/01/2003 6 04/01/2003 6 SQL> get x 1 select y.full_ukdate ukdate,

RE: RE: Date-based query Q

How about this? SELECT col_date, SUM(COUNT) FROM ( SELECT ukdate, COUNT(*) COUNT FROM tomtest GROUP BY ukdate UNION SELECT ADD_MONTHS(TO_DATE('12012002','mmdd'),ROWNUM) dba_month,0 FROM DBA_OBJECTS WHERE ROWNUM < 13) GROUP BY ukdate Tom Mercadante Oracle Certified Professiona