RE: RE: Date-based query Q

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 29 Oct 2003 06:09:26 -0800
Message-ID: <F001.005D4F0D.20031029060926@fatcity.com>

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) y

 14* where x.ukdate (+) = y.full_ukdate
SQL> / UKDATE COUNT(*)
---------- ----------

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).

Received on Wed Oct 29 2003 - 08:09:26 CST