Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

date ranges - possible to use analytic functions?

787568Jul 29 2010 — edited Jul 29 2010
The next datastructure needs to be converted to a daterange datastructure. 
START_DATE END_DATE      AMMOUNT
---------- ---------- ----------
01-01-2010 28-02-2010         10
01-02-2010 31-03-2010         20
01-03-2010 31-05-2010         30
01-09-2010 31-12-2010         40
Working solution: 
with date_ranges
as   ( select to_date('01-01-2010','dd-mm-yyyy') start_date
       ,      to_date('28-02-2010','dd-mm-yyyy') end_date
       ,      10                                 ammount
       from   dual
       union all
       select to_date('01-02-2010','dd-mm-yyyy') start_date
       ,      to_date('31-03-2010','dd-mm-yyyy') end_date
       ,      20                                 ammount
       from   dual
       union all
       select to_date('01-03-2010','dd-mm-yyyy') start_date
       ,      to_date('31-05-2010','dd-mm-yyyy') end_date
       ,      30                                 ammount
       from   dual
       union all
       select to_date('01-09-2010','dd-mm-yyyy') start_date
       ,      to_date('31-12-2010','dd-mm-yyyy') end_date
       ,      40                                 ammount
       from   dual
      )
select   rne.start_date
,        lead (rne.start_date-1,1)  over (order by rne.start_date) end_date
,        ( select sum(dre2.ammount)
           from   date_ranges dre2
           where  rne.start_date >= dre2.start_date
           and    rne.start_date <= dre2.end_date
         ) range_ammount
from     ( select dre.start_date
           from   date_ranges dre
           union -- implicit distinct
           select dre.end_date + 1
           from   date_ranges dre
         ) rne
order by rne.start_date
/
Output: 
START_DATE END_DATE   RANGE_AMMOUNT
---------- ---------- -------------
01-01-2010 31-01-2010            10
01-02-2010 28-02-2010            30
01-03-2010 31-03-2010            50
01-04-2010 31-05-2010            30
01-06-2010 31-08-2010
01-09-2010 31-12-2010            40
01-01-2011

7 rows selected.
However, I would like to use an analytic function to calculate the range_ammount. Is this possible?

Edited by: user5909557 on Jul 29, 2010 6:19 AM
This post has been answered by Frank Kulash on Jul 29 2010
Jump to Answer

Comments

Frank Kulash
Answer
Hi,

Welcome to the forum!

Yes, you can replace the scalar sub-queriy with an analytic SUM, like this: 
WITH  change_data   AS
(
	SELECT	start_date	AS change_date
	,	ammount		AS net_amount
	FROM	date_ranges
		--
    UNION
		--
	SELECT	end_date + 1	AS change_date
	,	-ammount   	AS net_amount
	FROM	date_ranges
)
,	got_range_amount	AS
(
	SELECT	change_date		AS start_date
	,	LEAD (change_date) OVER (ORDER BY  change_date) - 1
		     		   	AS end_date
	,	SUM (net_amount)   OVER (ORDER BY  change_date)
		    		   	AS range_amount
	FROM    change_data
)
,	got_grp		AS
(
	SELECT	start_date
	,	end_date
	,	range_amount
	,	ROW_NUMBER () OVER ( ORDER BY	   start_date, end_date)
	      - ROW_NUMBER () OVER ( PARTITION BY  range_amount
	      		      	     ORDER BY  	   start_date, end_date
				   ) 	   AS grp
	FROM    got_range_amount
)
SELECT	  MIN (start_date)	AS start_date
,	  MAX (end_date)	AS end_date
,	  range_amount
FROM	  got_grp
GROUP BY  grp
,     	  range_amount
ORDER BY  grp
;
This should be much more efficient.

The code is longer than what you posted. That's largely because it consolidates consecutive groups with the same amount.
For example, if we add this row to the sample data: 
--
       union all
       select to_date('02-01-2010','dd-mm-yyyy') start_date
       ,      to_date('30-12-2010','dd-mm-yyyy') end_date
       ,      0                                 ammount
       from   dual
The query you posted produces: 
START_DAT END_DATE  RANGE_AMMOUNT
--------- --------- -------------
01-JAN-10 01-JAN-10            10
02-JAN-10 31-JAN-10            10
01-FEB-10 28-FEB-10            30
01-MAR-10 31-MAR-10            50
01-APR-10 31-MAY-10            30
01-JUN-10 31-AUG-10             0
01-SEP-10 30-DEC-10            40
31-DEC-10 31-DEC-10            40
01-JAN-11
I assume you only want a new row of output when the range_amount changes., that is: 
START_DAT END_DATE  RANGE_AMOUNT
--------- --------- ------------
01-JAN-10 31-JAN-10           10
01-FEB-10 28-FEB-10           30
01-MAR-10 31-MAR-10           50
01-APR-10 31-MAY-10           30
01-JUN-10 31-AUG-10            0
01-SEP-10 31-DEC-10           40
01-JAN-11                      0
Of course, you could modify the original query so that it did this, but it would end up about as complex as the query above, but less efficient.
Conversely, if you prefer the longer output, then you don't need the suib-query got_grp in the query above.

Thanks for posting the CREATE TABLE and INSERT statments; that's very helpful.
There are some people who have been using this forum for years who still have to be begged to do that.
Marked as Answer by 787568 · Sep 27 2020
787568
Nice solution!

Thanks :)
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 26 2010
Added on Jul 29 2010
2 comments
2,660 views