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.

Time Span?

712463Oct 15 2009 — edited Oct 15 2009

I am trying to get the net days for a time span, starting from first step through to the last step. I have a few issues that I have to account for:
• The problem is overlap time. Some steps overlap; with their start days coming before the end of the previous step.
• There could be gaps from end of 1 step till start of the next.

I know I could min and max step days or count total days from table but that does not take the overlap into consideration. I have provided an example below.

CREATE TABLE claims_step_table (  
table_id       INTEGER PRIMARY KEY, 
record_id      NUMBER,  
step_code      VARCHAR2(20 Byte),
start_date     DATE,
end_date       DATE,
days           NUMBER
) 


truncate table claims_step_table;
INSERT INTO claims_step_table VALUES (1,  123456,'P96','01-JAN-09','10-JAN-09',10);
INSERT INTO claims_step_table VALUES (2,  123456,'L61','10-JAN-09','15-JAN-09',5);
INSERT INTO claims_step_table VALUES (3,  123456,'H45','12-JAN-09','20-JAN-09',8);
INSERT INTO claims_step_table VALUES (4,  123456,'O87','10-FEB-09','15-FEB-09',5);
COMMIT;


P96      |----------- 10 --------|
L61                              |-----5-----|
H45                                   |--------8----------|
O87                                                                      |-----5-----|

So the net should be 25 days because there is a 3 day overlap between step L61 and H45. Thanks for your help ahead of time.

Rich

GOOD CATCH, my fault record #4 should have been in FEB, cut and paste error.

Edited by: LostOne on Oct 15, 2009 8:19 AM

This post has been answered by Hoek on Oct 15 2009

Jump to Answer

Hoek

Answer

Hi,

You could use lag/lead here:

SQL> select sum(days) - sum(overlap) net
  2  from ( select days
  3         ,      start_date
  4         ,      end_date
  5         ,      case when next_start_date < last_end_date
  6                     then last_end_date-next_start_date
  7                     else 0
  8                end overlap  
  9         from ( select t.table_id
 10                ,      t.record_id
 11                ,      t.step_code
 12                ,      t.days
 13                ,      start_date
 14                ,      end_date
 15                ,      lag(start_date) over ( partition by record_id order by start_date) last_start_date
 16                ,      lag(end_date) over ( partition by record_id order by end_date) last_end_date
 17                ,      lead(start_date) over ( partition by record_id order by start_date) next_start_date
 18                ,      lead(end_date) over ( partition by record_id order by end_date) next_end_date
 19                from   claims_step_table t
 20              )
 21        );

       NET
----------
        25

You might need to adjust my example a bit to cover all possibilities that can occur, but this gives you an idea.

Marked as Answer by 712463 · Sep 27 2020

Frank Kulash

Hi,

Sorry, I don't understand.
Shouldn't the total from the data you posted:

INSERT INTO claims_step_table VALUES (1,  123456,'P96','01-JAN-09','10-JAN-09',10);
INSERT INTO claims_step_table VALUES (2,  123456,'L61','10-JAN-09','15-JAN-09',5);
INSERT INTO claims_step_table VALUES (3,  123456,'H45','12-JAN-09','20-JAN-09',8);
INSERT INTO claims_step_table VALUES (4,  123456,'O87','10-JAN-09','15-JAN-09',5);

be 20 days, since the last row (table_id=4) completely overlaps with previous rows?

Assuming you do want 20 for the results, I think the easiest thing to do is to generate all the days between start_date and end_date, and do a COUNT (DISTINCT ...).
The following assumes that you need a separate total for each record_id:

WITH	cntr	AS
(
	SELECT	LEVEL - 1	AS n
	FROM	dual
	CONNECT BY	LEVEL <= 1 + ( SELECT	MAX (end_date) - MIN (start_date)
			      	       FROM	claims_step_table
				     )
)
SELECT	  s.record_id
,	  COUNT (DISTINCT s.start_date + c.n)	AS net_days
FROM	  claims_step_table	s
JOIN	  cntr			c	ON	c.n	<= s.end_date - s.start_date
GROUP BY  s.record_id
;

This assumes that all the dates have the same hours, minutes and seconds. If that's not the case, then use TRUNC to makle it so.

Thanks for posting the CREATE TABLE and INSERT statements! That really helps.

712463

Great, works fine... i can mod if needed, i had no idea what direction to go. This site is awsome, the more i read the more i learn!!!

712463

cut and paste error record #4 should have been in FEB not JAN sorry about that. I edited the inserts. Yes, you were right though, if it would have been in Jan the run should have been for 20 days.

Edited by: LostOne on Oct 15, 2009 8:22 AM

Hoek

This site is awsome, the more i read the more i learn!!!

The same goes for me, I completely agree on that
this forum is almost too addictive, like coffee ;)

Boneist

Using the tabibitosan method:

with    cst as (select 1 table_id, 123456 record_id, 'P96' step_code, to_date('01/01/2009', 'dd/mm/yyyy') start_date, to_date('10/01/2009', 'dd/mm/yyyy') end_date, 9 days from dual union all
                select 2 table_id, 123456 record_id, 'L61' step_code, to_date('10/01/2009', 'dd/mm/yyyy') start_date, to_date('15/01/2009', 'dd/mm/yyyy') end_date, 5 days from dual union all
                select 3 table_id, 123456 record_id, 'H45' step_code, to_date('12/01/2009', 'dd/mm/yyyy') start_date, to_date('20/01/2009', 'dd/mm/yyyy') end_date, 8 days from dual union all
                select 4 table_id, 123456 record_id, 'O87' step_code, to_date('22/01/2009', 'dd/mm/yyyy') start_date, to_date('27/01/2009', 'dd/mm/yyyy') end_date, 5 days from dual union all
                select 5 table_id, 123457 record_id, 'P96' step_code, to_date('22/02/2009', 'dd/mm/yyyy') start_date, to_date('24/02/2009', 'dd/mm/yyyy') end_date, 2 days from dual union all
                select 6 table_id, 123457 record_id, 'L61' step_code, to_date('01/01/2009', 'dd/mm/yyyy') start_date, to_date('11/01/2009', 'dd/mm/yyyy') end_date, 10 days from dual union all
                select 7 table_id, 123455 record_id, 'L61' step_code, to_date('18/01/2009', 'dd/mm/yyyy') start_date, to_date('19/01/2009', 'dd/mm/yyyy') end_date, 5 days from dual union all
                select 8 table_id, 123457 record_id, 'H45' step_code, to_date('02/01/2009', 'dd/mm/yyyy') start_date, to_date('05/01/2009', 'dd/mm/yyyy') end_date, 5 days from dual union all
                select 9 table_id, 123455 record_id, 'H45' step_code, to_date('22/01/2009', 'dd/mm/yyyy') start_date, to_date('27/01/2009', 'dd/mm/yyyy') end_date, 5 days from dual),
-- end of mimicking your data
tabibitosan as (select table_id,
                       record_id,
                       step_code,
                       start_date,
                       end_date,
                       dense_rank() over (order by start_date, end_date, table_id) - 
                         row_number() over (partition by record_id order by start_date, end_date) difference
                from   cst),
    results as (select record_id,
                       max(end_date) - min(start_date) no_of_days
                from tabibitosan
                group by record_id, 
                         difference)
select record_id,
       sum(no_of_days) total_days
from   results
group by record_id
order by record_id;

 RECORD_ID TOTAL_DAYS
---------- ----------
    123455          6
    123456         24
    123457         12

NB. you had 10 days covering 1st Jan - 10th Jan, but 5 days covering 10th Jan - 15th Jan - this isn't consistent!
Regardless, you do not need to store the number of days difference in the table - it only encourages errors like that! Just calculate the difference on the fly.

21205

Boneist wrote:
Using the tabibitosan method:

... simply beautiful...
:)

712463

I only have read on ALL tables, im a BA here so i only look at the data, someone's much smarter than me create it...lol. I did not want to use the numbers in the table for the reason you stated above, but according to the buisness the run for the example should be 25. I only work here not make the rules, i c ur point.

Boneist

LostOne wrote:
I only have read on ALL tables, im a BA here so i only look at the data, someone's much smarter than me create it...lol. I did not want to use the numbers in the table for the reason you stated above, but according to the buisness the run for the example should be 25. I only work here not make the rules, i c ur point.

Damned users and their pesky requirements! *{;-)

What's the logic for turning the start and end dates into the number of days? It doesn't seem consistent!

Hoek

hoek's been completely puzzled by this post for quite a while

¿Huh? What/who's "Tabibitosan"?

more time passes by

AHA! Finally I found it ;-)
1640417

Nice one, Boneist!
And ofcourse: Nice one, Aketi!

Edited by: hoek on Oct 15, 2009 7:05 PM

Boneist

hoek wrote:
And ofcourse: Nice one, Aketi!

Indeed! I've just started using Aketi's technique, so now (of course) I'm converting everything into nails so I can use my nice new hammer *{;-)

Hoek

* looks embarassed at his ancient screwdriver *

Boneist

hoek wrote:
* looks embarassed at his ancient screwdriver *

No need! That's the beauty of this forum; you too can make your very own shiny hammer to hit things(*) with!

(*) I said {noformat}*things* not people!{noformat} *{;-)

1 - 13

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 12 2009

Added on Oct 15 2009

13 comments

3,824 views

SQL & PL/SQL

Time Span?

Comments

Post Details