All Forums Database
sunny.j 90 posts Joined 10/10
21 Oct 2014
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

sunny.j 90 posts Joined 10/10
21 Oct 2014

any one please suggest me how to achieve this output

dnoeth 4628 posts Joined 11/04
21 Oct 2014

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 

 

Dieter

sunny.j 90 posts Joined 10/10
22 Oct 2014

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
 

dnoeth 4628 posts Joined 11/04
22 Oct 2014

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

Dieter

raghu.juvvadi 1 post Joined 02/08
08 Jun 2015

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?

manib0907 61 posts Joined 04/15
09 Jun 2015
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

Cheers,
Mani

You must sign in to leave a comment.