RE: Sql question

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 29 Jun 2001 11:58:32 -0700
Message-ID: <F001.0033DFBC.20010629120630@fatcity.com>

John,

If you include a join to another table with more rows than the difference in dates, you can use a technique similar to the following. Note that I joined to DBA_Objects in an in-line view constraining by ROWNUM to equal the number of days in the date range. There are other ways to this as well:

SQL> select * from jbs;

ORDER_DATE QTY
---------- ----------

03/02/2001         10
03/03/2001          1
03/06/2001          8
03/02/2001         10
03/03/2001          1
03/06/2001          8

6 rows selected.

SQL> Select X.D_Date, sum(nvl(Qty,0))
  2 From JBS,

  3        (Select to_date('&&START_DATE','MM/DD/YYYY') + rownum -1 D_Date
  4         From   dba_objects
  5         Where  rownum <=  to_date('&&END_DATE','MM/DD/YYYY') -
  6                           to_date('&&START_DATE','MM/DD/YYYY') + 1) X

  7 Where X.D_Date = Order_Date (+)
  8 Group By X.D_Date
  9 /
Enter value for start_date: 03/02/2001
old 3: (Select to_date('&&START_DATE','MM/DD/YYYY') + rownum -1 D_Date
new 3: (Select to_date('03/02/2001','MM/DD/YYYY') + rownum -1 D_Date Enter value for end_date: 03/06/2001

old   5:        Where  rownum <=  to_date('&&END_DATE','MM/DD/YYYY') -
new   5:        Where  rownum <=  to_date('03/06/2001','MM/DD/YYYY') -
old   6:                          to_date('&&START_DATE','MM/DD/YYYY') + 1)
X
new   6:                          to_date('03/02/2001','MM/DD/YYYY') + 1) X

D_DATE     SUM(NVL(QTY,0))
---------- ---------------
03/02/2001              20
03/03/2001               2
03/04/2001               0
03/05/2001               0
03/06/2001              16

Regards,

Larry G. Elkins
elkinsl_at_flash.net
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Shaw, John
> B
> Sent: Friday, June 29, 2001 1:37 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Sql question
>
>
> I want to get every date between two user entered date's whether or not it
> exists in the table and then the quantity of data for the date if any
> exists.
> My table t1 has date and quantity values (may be more than one quantity
> field per date)
> and I want 0 for all dates in the range where no data exists.
> Is there a sql to get this without making some kind of date table?
> so if the user enters 03/02/01 and 03/06/01 they get.
> 03/02/01 10
> 03/03/01 1
> 03/04/01 0
> 03/05/01 0
> 03/06/01 8
> They want to load into a spreadsheet.
> tia.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Shaw, John B
> INET: jbshaw_at_ingr.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Fri Jun 29 2001 - 13:58:32 CDT