| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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','mmddyyyy'),ROWNUM) dba_month,0
FROM DBA_OBJECTS
WHERE ROWNUM < 13)
GROUP BY ukdate
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, October 29, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
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,
2 nvl(x.cnt, 0) "COUNT(*)"
3 from (select trunc(ukdate) ukdate,
4 count(*) cnt 5 from test 6 group by trunc(ukdate)) x, 7 (select a.rn + b.mindate - 1 full_ukdate 8 from (select rownum rn 9 from all_tab_columns) a, 10 (select min(ukdate) mindate, 11 max(ukdate) maxdate 12 from test) b 13 where a.rn <= b.maxdate - b.mindate + 1) y14* where x.ukdate (+) = y.full_ukdate
01/01/2003 5 02/01/2003 6 03/01/2003 0 04/01/2003 6
Do you _really_ want that :-) ?
Didn't find analytical functions of much help on this one ...
SF
>----- ------- Original Message ------- -----
>From: "Nicoll, Iain" <IAIN.D.NICOLL_at_saic.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Wed, 29 Oct 2003 04:44:25
>
>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>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: sfaroult_at_oriolecorp.com 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: =?utf-8?B?TWVyY2FkYW50ZSwgVGhvbWFzIEY=?= INET: NDATFM_at_labor.state.ny.us 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).Received on Wed Oct 29 2003 - 08:54:23 CST
 |
 |