Time Span?

712463 · Oct 15, 2009 10:37AM

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

Hoek · Oct 15, 2009 10:55AM

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.

Hoek · Oct 15, 2009 10:55AM

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.

Frank Kulash · Oct 15, 2009 11:05AM

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 · Oct 15, 2009 11:06AM

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 · Oct 15, 2009 11:21AM

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 · Oct 15, 2009 11:22AM

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 · Oct 15, 2009 11:23AM

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 · Oct 15, 2009 11:39AM

Boneist wrote:
Using the tabibitosan method:

... simply beautiful...

712463 · Oct 15, 2009 11:43AM

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 · Oct 15, 2009 11:53AM

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 · Oct 15, 2009 1:04PM

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

Forum Stats

Quick Links

Categories

Time Span?

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Time Span?

Best Answer

Answers