Home » SQL & PL/SQL » SQL & PL/SQL » Date Range Data Extraction using SQL (Oracle 10g)
Date Range Data Extraction using SQL [message #354485] Sun, 19 October 2008 16:31 Go to next message
entryLevelUser
Messages: 2
Registered: October 2008
Junior Member
I do have a sql which gives how many entries on a day occurred with in date period. I would like to know how many entries occurred with in a date range (In a week, month etc). Would appreciate if some one let me know.

select count(tbl1.entries) as cnt1
, tbl1.dt
from tbl1, tbl2
where tbl1.id = tbl2.id
and tbl1.dt between to_date('11/20/2006','MM/DD/YYYY') and
to_date('11/20/2007','MM/DD/YYYY') group by tbl1.dt

I want the following output using a sql that number of entries occured in a week range between two dates:

start_date end_date cnt
11/20/2006 11/26/2006 10
11/27/2006 12/4/2006 5
12/5/2006 12/12/2006 20
…..
…..
11/1/2007 11/7/2007 6

11/14/2007 11/20/2007 15

Thanks.
Re: Date Range Data Extraction using SQL [message #354486 is a reply to message #354485] Sun, 19 October 2008 18:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
There are various ways that you could do this. One method would be to use TO_CHAR(tbl1.dt,'IW') to get the week number of the year and group by that, then you could select MIN(tbl1.dt) for your start date and MAX(tbl1.dt) for your end date.
Re: Date Range Data Extraction using SQL [message #354492 is a reply to message #354485] Sun, 19 October 2008 21:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are in a warehousing environment, you likely have a date or time dimension. Alter this dimension to map your dates to periods. Then create a version of your query that joins to these for the periods you want.

Kevin
Re: Date Range Data Extraction using SQL [message #354666 is a reply to message #354486] Mon, 20 October 2008 12:49 Go to previous messageGo to next message
entryLevelUser
Messages: 2
Registered: October 2008
Junior Member
Thanks for the reply.

Its working with group by to_char() function giving week - 'IW', Day - 'DD', Month - 'MM'. But I would like to know If I would like to give the input range in days: 7 days, 14 days, 30 days, 60 days etc...

I would appreciate if you can please let me know how we can do that.

Thanks.
Re: Date Range Data Extraction using SQL [message #356663 is a reply to message #354666] Fri, 31 October 2008 15:55 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You can use one of various row generation techniques to generate a list of start and end dates for any range of days that you like, then you can join to that list to obtain your results. I have provided a demonstration below.

-- create variable and assign value for days in range:
SCOTT@orcl_11g> VARIABLE days NUMBER
SCOTT@orcl_11g> EXEC :days := 30

PL/SQL procedure successfully completed.


-- generate list of start and end dates using days in range:
SCOTT@orcl_11g> SELECT	TO_DATE ('11/20/2006', 'MM/DD/YYYY') + (:days * (ROWNUM - 1)) start_date,
  2  	     TO_DATE ('11/20/2006', 'MM/DD/YYYY') + (:days * ROWNUM) - 1   end_date
  3  FROM    DUAL
  4  CONNECT BY LEVEL <= CEIL (365/:days)
  5  ORDER   BY start_date
  6  /

START_DATE  END_DATE
----------- -----------
20-NOV-2006 19-DEC-2006
20-DEC-2006 18-JAN-2007
19-JAN-2007 17-FEB-2007
18-FEB-2007 19-MAR-2007
20-MAR-2007 18-APR-2007
19-APR-2007 18-MAY-2007
19-MAY-2007 17-JUN-2007
18-JUN-2007 17-JUL-2007
18-JUL-2007 16-AUG-2007
17-AUG-2007 15-SEP-2007
16-SEP-2007 15-OCT-2007
16-OCT-2007 14-NOV-2007
15-NOV-2007 14-DEC-2007

13 rows selected.


-- join to the list of dates to get your desires results:
SCOTT@orcl_11g> SELECT	d.start_date, d.end_date, COUNT (o.created)
  2  FROM    all_objects o,
  3  	     (SELECT  TO_DATE ('11/20/2006', 'MM/DD/YYYY') + (:days * (ROWNUM - 1)) start_date,
  4  		      TO_DATE ('11/20/2006', 'MM/DD/YYYY') + (:days * ROWNUM) - 1   end_date
  5  	      FROM    DUAL
  6  	      CONNECT BY LEVEL <= CEIL (365/:days)) d
  7  WHERE   o.created (+) >= d.start_date AND o.created (+) < d.end_date + 1
  8  GROUP   BY d.start_date, d.end_date
  9  ORDER   BY start_date
 10  /

START_DATE  END_DATE    COUNT(O.CREATED)
----------- ----------- ----------------
20-NOV-2006 19-DEC-2006                0
20-DEC-2006 18-JAN-2007                0
19-JAN-2007 17-FEB-2007                0
18-FEB-2007 19-MAR-2007                0
20-MAR-2007 18-APR-2007                0
19-APR-2007 18-MAY-2007                0
19-MAY-2007 17-JUN-2007                0
18-JUN-2007 17-JUL-2007                0
18-JUL-2007 16-AUG-2007                0
17-AUG-2007 15-SEP-2007                0
16-SEP-2007 15-OCT-2007            67330
16-OCT-2007 14-NOV-2007              584
15-NOV-2007 14-DEC-2007               24

13 rows selected.

SCOTT@orcl_11g>

Previous Topic: EXECUTE IMMEDIATE ; ROWTYPE as USING
Next Topic: how to copy a record type's values to a object type
Goto Forum:
  


Current Time: Mon Dec 05 02:40:11 CST 2016

Total time taken to generate the page: 0.10081 seconds