Forum Stats

  • 3,837,605 Users
  • 2,262,272 Discussions
  • 7,900,326 Comments

Discussions

Time Span?

712463
712463 Member Posts: 46
edited Oct 15, 2009 4:43PM in SQL & PL/SQL
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

Best Answer

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    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.
«1

Answers

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    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
    Frank Kulash Member, Moderator Posts: 42,219 Red Diamond
    edited Oct 15, 2009 11:15AM
    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
    712463 Member Posts: 46
    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
    712463 Member Posts: 46
    edited Oct 15, 2009 11:22AM
    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
    Hoek Member Posts: 16,087 Gold Crown
    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
    Boneist Member Posts: 4,983 Gold Trophy
    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
    21205 Member Posts: 6,168 Gold Trophy
    Boneist wrote:
    Using the tabibitosan method:
    ... simply beautiful...
    :)
  • 712463
    712463 Member Posts: 46
    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
    Boneist Member Posts: 4,983 Gold Trophy
    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 Member Posts: 16,087 Gold Crown
    edited Oct 15, 2009 1:05PM
    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
This discussion has been closed.