Start dates overlap

Database
Enthusiast

Start dates overlap

Dears

I'm struggling to find a solution for the start dates overlap to continue with my analysis

unique_id     struc_name    per_id  username  start_dt  end_dt

001bbf    C10CHA00005     10225712    ?       14/10/2010    06/02/2013

001bbf2    H10CHA00002     10225712    ?      07/02/2013    08/02/2013

6c2e85    H10NIL00006     10225712    xxxx    07/02/2013    31/12/9999

i'm trying for an output as like :

unique_id     struc_name    per_id  username  start_dt  end_dt

001bbf    C10CHA00005     10225712    ?       14/10/2010    06/02/2013

001bbf2    H10CHA00002     10225712    ?      07/02/2013    08/02/2013

6c2e85    H10NIL00006     10225712    xxxx    09/02/2013    31/12/9999

i request the forum to help with your suggestions and solution

Tags (1)
6 REPLIES
Enthusiast

Re: Start dates overlap

any one please suggest me how to achieve this output

Junior Contributor

Re: Start dates overlap

You didn't specify the rules how to determnine the output.

Maybe

case
when
min(end_dt)
over (partition by username
order by start_dt, end_dt
rows between 1 preceding and 1 preceding) >= start_dt
then
min(end_dt)
over (partition by username
order by start_dt, end_dt
rows between 1 preceding and 1 preceding)
else start_dt
end
Enthusiast

Re: Start dates overlap

Hi Dieter

Thanks for your reply

the rule is based on the Per_id .

unique_id     struc_name    per_id  username  start_dt  end_dt

001bbf    C10CHA00005     10225712    ?       14/10/2010    06/02/2013

001bbf2    H10CHA00002     10225712    ?      07/02/2013    08/02/2013

6c2e85    H10NIL00006     10225712    xxxx    07/02/2013    31/12/9999

for 2nd and 3rd record if we observe the start date is same where as the 2nd record is having the end date with start date + 1  and 3rd record have is open end date means still active .

so when every there 2 records for the same per_id having the same start date depends on the end date the next record start should change .

output as like :

unique_id     struc_name    per_id  username  start_dt  end_dt

001bbf    C10CHA00005     10225712    ?       14/10/2010    06/02/2013

001bbf2    H10CHA00002     10225712    ?      07/02/2013    08/02/2013

6c2e85    H10NIL00006     10225712    xxxx    09/02/2013    31/12/9999

Junior Contributor

Re: Start dates overlap

Change my query to PARTITON BY per_id, your logic is probably quite similar, based on the previous row's value.

Re: Start dates overlap

Hi,

Thanks in advance for your help. I need some help in figuring out a query to find out overlap periods. Currently we are not using period functioanlity. We need to report only overlapping rows for a column combination group1 group2

GROUP1  GRP2      start_dt  end_dt

1               AAA    2015-01-01  2015-12-31

1               AAA    2016-01-01  2016-12-31

1             AAA     2015-02-01  2015-02-31

1             AAA     2015-03-01    2015-03-31

2           BBB     2015-01-01   2015-12-31

2           BBB       2015-03-01   2015-03-31

2            BBB            2016-01-01   2016-01-31

Output should be

GROUP1  GRP2      start_dt  end_dt

1               AAA    2015-01-01  2015-12-31

1             AAA     2015-02-01  2015-02-31

1             AAA     2015-03-01    2015-03-31

2           BBB     2015-01-01   2015-12-31

2           BBB       2015-03-01   2015-03-31

How to write the query to report only rows which has overlap with previous rows? I tried OLAP functions, recursion etc. How to go bakc and compare all previous rows weather start date falls vbetween start and end dates of previous rows for that key combiantion?

Enthusiast

Re: Start dates overlap

SELECT

GROUP1,GRP2,STRT_DT,END_DT
FROM
(SELECT
GROUP1,GRP2,STRT_DT,END_DT,
COALESCE(MAX(END_DT)OVER(PARTITION BY GROUP1,GRP2 ORDER BY STRT_DT ASC,END_DT ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING,DATE '1111-01-01') AS PRVS_END_DT,
ROW_NUMBER()OVER(PARTITION BY GROUP1,GRP2 ORDER BY STRT_DT,END_DT) AS RECRD_ID,
CASE WHEN RECRD_ID=1 THEN 0
WHEN STRT_DT-PRVS_END_DT <0 THEN 1 ELSE 0 END AS GAP
) A
WHERE GAP=1

try this . I have worked on a similar scenario before, this should work. let me know if u see an issue.

cheers,

Mani