Home » SQL & PL/SQL » SQL & PL/SQL » Measure time overlap between 3 date ranges (Oracle 11g)
icon5.gif  Measure time overlap between 3 date ranges [message #580789] Thu, 28 March 2013 04:38 Go to next message
mavericka
Messages: 5
Registered: March 2013
Junior Member
Hi,
I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.
The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).
So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]

Here is the scenario:
---------------------

CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,
IXFER_IN_DATE DATE,
IXFER_OUT_DATE DATE,
QUAL_START_DATE DATE,
QUAL_END_DATE DATE );
COMMIT;
/
INSERT INTO PAYEES
values ('30-Jun-2013','TESTID01','6-Jun-2013','16-Jun-2013','8-Jun-2013','14-Jun-2013','15-Jun-2013','19-Jun-2013');
commit;
/
INSERT INTO PAYEES
values ('30-Jun-2013','TESTID02','10-Jun-2013','11-Jun-2013','12-Jun-2013','22-Jun-2013','08-Jun-2013','19-Jun-2013');
COMMIT;
---------------------------------------------------------
---------------------------------------------------------
Now Active days is to be pulled for Two payees - TESTD01 and TESTD02 for the period of June month.
Desired Results :

Payee InActive_days Active_Days Period
---------------------------------------------------------------------------------------
TESTD01 13 17 June
TESTD02 14 16 June


I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.

Please help!
Re: Measure time overlap between 3 date ranges [message #580790 is a reply to message #580789] Thu, 28 March 2013 04:40 Go to previous messageGo to next message
mavericka
Messages: 5
Registered: March 2013
Junior Member
All dates here are MM/DD/YYYY format. And the results expected is in terms of days Only. Thanks!
Re: Measure time overlap between 3 date ranges [message #580793 is a reply to message #580790] Thu, 28 March 2013 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
All dates here are MM/DD/YYYY format.


I don't think so, in addition:
INSERT INTO PAYEES
values ('30-Jun-2013','TESTID01','6-Jun-2013','16-Jun-2013','8-Jun-2013','14-Jun-2013','15-Jun-2013','19-Jun-2013');

Regards
Michel
Re: Measure time overlap between 3 date ranges [message #580805 is a reply to message #580793] Thu, 28 March 2013 05:38 Go to previous messageGo to next message
mavericka
Messages: 5
Registered: March 2013
Junior Member
My Apologies. Please ignore the format if possible. The above format was given to give an easier understanding, nothing else.
Date format dosent really bother me as of now as long as i can get the days that get overlapped among the 3 date ranges.

Many thanks!
Re: Measure time overlap between 3 date ranges [message #580813 is a reply to message #580805] Thu, 28 March 2013 06:28 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
See if this gets you started:
WITH DATA AS (SELECT TRUNC(SYSDATE) date1_start, TRUNC(SYSDATE + 3) date1_end,
TRUNC(SYSDATE + 2) date2_start, TRUNC(SYSDATE + 4) date2_end FROM dual)
SELECT date1_start, date1_end, date2_start, date2_end,
CASE WHEN NOT (date1_end < date2_start
               OR date1_start > date2_end)
     THEN LEAST(date1_end, date2_end) - GREATEST(date1_start, date2_start)
     ELSE 0 
     END AS overlap
FROM DATA;


You'll probably need a different calculation for all 3 overlapping and for each combination of 2 overlapping.

[Updated on: Thu, 28 March 2013 06:29]

Report message to a moderator

Re: Measure time overlap between 3 date ranges [message #580828 is a reply to message #580813] Thu, 28 March 2013 08:08 Go to previous messageGo to next message
mavericka
Messages: 5
Registered: March 2013
Junior Member
This will definelty get me starting to workaround those inactive days.Thanks!
Re: Measure time overlap between 3 date ranges [message #580832 is a reply to message #580813] Thu, 28 March 2013 08:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1959
Registered: January 2010
Senior Member
5 permutations is too much for me Smile. I'd stick to generic start-of-group method:

with t1 as (
             select  run_period,
                     payeeid,
                     loa_start_date start_dt,
                     loa_end_date end_dt
               from  payees
            union all
             select  run_period,
                     payeeid,
                     ixfer_in_date start_dt,
                     ixfer_out_date end_dt
               from  payees
            union all
             select  run_period,
                     payeeid,
                     qual_start_date start_dt,
                     qual_end_date end_dt
               from  payees
           ),
     t2 as (
            select  run_period,
                    payeeid,
                    start_dt,
                    end_dt,
                    case
                      when max(end_dt) over(
                                            partition by run_period,payeeid
                                            order by start_dt,end_dt
                                            rows between unbounded preceding and 1 preceding
                                           ) >= start_dt then 0
                      else 1
                    end start_of_group
              from  t1
           ),
     t3 as (
            select  run_period,
                    payeeid,
                    start_dt,
                    end_dt,
                    sum(start_of_group) over(partition by run_period,payeeid) grp
              from  t2
           ),
     t4 as (
            select  run_period,
                    payeeid,
                    max(end_dt) - min(start_dt) inactive_days
              from  t3
              group by grp,
                       run_period,
                       payeeid
           )
select  payeeid,
        sum(inactive_days) inactive_days,
        to_char(run_period,'dd') - sum(inactive_days) active_days
  from  t4
  group by run_period,
           payeeid
  order by run_period,
           payeeid
/

PAYEEID              INACTIVE_DAYS ACTIVE_DAYS
-------------------- ------------- -----------
TESTID01                        13          17
TESTID02                        14          16

SQL> 


SY.
Re: Measure time overlap between 3 date ranges [message #580834 is a reply to message #580832] Thu, 28 March 2013 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems like more code than a 5 permutation case. Plus you're doing 3 passes on the table.
Re: Measure time overlap between 3 date ranges [message #580837 is a reply to message #580834] Thu, 28 March 2013 09:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1959
Registered: January 2010
Senior Member
And then scratch your head over 5 level god knows how many branches CASE every time you look at the code. I prefer lengthy but modular easy to read code. And about 3 passes. This is what OP gets when designing denormalized tables. What will OP do if tomorrow HR decides there is a new type of absense?

SY.
Re: Measure time overlap between 3 date ranges [message #580895 is a reply to message #580789] Fri, 29 March 2013 09:49 Go to previous messageGo to next message
mavericka
Messages: 5
Registered: March 2013
Junior Member
Hi,

Thanks for that update. This too works.
Being a novice, Im not sure how and which way does the 'grp' help me here.
Can I tweak it in this way ?
---------------
select nonelligible_days from
(select
max(end_dt) - min(start_dt) nonelligible_days from
( select run_period,
payeeid,
start_dt,
end_dt from
( select run_period,payeeid,loa_start_date start_dt, loa_end_date end_dt
from cas_participants
where payeeid = v_sid and run_period = v_run_period
union all
select run_period,payeeid,ixfer_in_date start_dt,ixfer_out_date end_dt
from cas_participants
where payeeid = v_sid and run_period = v_run_period
union all
select run_period,payeeid,WW_start_date start_dt,WW_end_date end_dt
from cas_participants
where payeeid = v_sid and run_period = v_run_period
)
) where payeeid = v_sid and run_period = v_run_period
group by
run_period,
payeeid)

----------------------------------------------------
This too pulls the same results per your query.
I am unable to figure out when would the above query fail if I dont use 'grp'(partition clause)?

Thanks!
Re: Measure time overlap between 3 date ranges [message #580901 is a reply to message #580895] Fri, 29 March 2013 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: Measure time overlap between 3 date ranges [message #581072 is a reply to message #580895] Mon, 01 April 2013 08:52 Go to previous message
Solomon Yakobson
Messages: 1959
Registered: January 2010
Senior Member
Opps, you are right. I incorrectly used start-of-group method, grp calculation to be precise. It is missing order by cluse. It should be:

sum(start_of_group) over(partition by run_period,payeeid order by start_dt,end_dt) grp


not:

sum(start_of_group) over(partition by run_period,payeeid) grp


Now:

with t1 as (
             select  run_period,
                     payeeid,
                     loa_start_date start_dt,
                     loa_end_date end_dt
               from  payees
            union all
             select  run_period,
                     payeeid,
                     ixfer_in_date start_dt,
                     ixfer_out_date end_dt
               from  payees
            union all
             select  run_period,
                     payeeid,
                     qual_start_date start_dt,
                     qual_end_date end_dt
               from  payees
           ),
     t2 as (
            select  run_period,
                    payeeid,
                    start_dt,
                    end_dt,
                    case
                      when max(end_dt) over(
                                            partition by run_period,payeeid
                                            order by start_dt,end_dt
                                            rows between unbounded preceding and 1 preceding
                                           ) >= start_dt then 0
                      else 1
                    end start_of_group
              from  t1
           ),
     t3 as (
            select  run_period,
                    payeeid,
                    start_dt,
                    end_dt,
                    sum(start_of_group) over(
                                             partition by run_period,payeeid
                                              order by start_dt,end_dt
                                            ) grp
              from  t2
           ),
     t4 as (
            select  run_period,
                    payeeid,
                    max(end_dt) - min(start_dt) inactive_days
              from  t3
              group by grp,
                       run_period,
                       payeeid
           )
select  payeeid,
        sum(inactive_days) inactive_days,
        to_char(run_period,'dd') - sum(inactive_days) active_days
  from  t4
  group by run_period,
           payeeid
  order by run_period,
           payeeid
/

PAYEEID              INACTIVE_DAYS ACTIVE_DAYS
-------------------- ------------- -----------
TESTID01                        13          17
TESTID02                        14          16
TESTID04                         5          25

SQL> 


Still not same result. Why? You never explained duration calculation. TESTD04 has 2 periods:

10-JUN-13 10-JUN-13


and

20-JUN-13 25-JUN-13


So if for second period you calculate duration as 5 = 25-JUN-13 - 20-JUN-13 then for first perior it should be 0 = 10-JUN-13 - 10-JUN-13 while you want it as 1. Then shouldn't second period duration be 6? Ald it is logical. If I take 20-JUN-13 through 25-JUN-13 off I am taking 6 days off, not 5. Anyway, to accomodate your logic I'll use greatest(end_date - start_date,1):

with t1 as (
             select  run_period,
                     payeeid,
                     loa_start_date start_dt,
                     loa_end_date end_dt
               from  payees
            union all
             select  run_period,
                     payeeid,
                     ixfer_in_date start_dt,
                     ixfer_out_date end_dt
               from  payees
            union all
             select  run_period,
                     payeeid,
                     qual_start_date start_dt,
                     qual_end_date end_dt
               from  payees
           ),
     t2 as (
            select  run_period,
                    payeeid,
                    start_dt,
                    end_dt,
                    case
                      when max(end_dt) over(
                                            partition by run_period,payeeid
                                            order by start_dt,end_dt
                                            rows between unbounded preceding and 1 preceding
                                           ) >= start_dt then 0
                      else 1
                    end start_of_group
              from  t1
           ),
     t3 as (
            select  run_period,
                    payeeid,
                    start_dt,
                    end_dt,
                    sum(start_of_group) over(
                                             partition by run_period,payeeid
                                              order by start_dt,end_dt
                                            ) grp
              from  t2
           ),
     t4 as (
            select  run_period,
                    payeeid,
                    greatest(max(end_dt) - min(start_dt),1) inactive_days
              from  t3
              group by grp,
                       run_period,
                       payeeid
           )
select  payeeid,
        sum(inactive_days) inactive_days,
        to_char(run_period,'dd') - sum(inactive_days) active_days
  from  t4
  group by run_period,
           payeeid
  order by run_period,
           payeeid
/

PAYEEID              INACTIVE_DAYS ACTIVE_DAYS
-------------------- ------------- -----------
TESTID01                        13          17
TESTID02                        14          16
TESTID04                         6          24

SQL> 


SY.
Previous Topic: Reference cursor to generate the sequence number
Next Topic: Find the longest path
Goto Forum:
  


Current Time: Mon Jul 28 08:03:06 CDT 2014

Total time taken to generate the page: 0.15381 seconds