Find minimum overlap of Each Status

Question

I need to find date ranges where status is Missing/Not Ready in all the groups ( Only the overlapping date ranges where all the groups have status of missing/notready) '''

ID.   Group.    Eff_Date.             Exp_Date           Status
1.    1             1/1/18 10:00       3/4/18 15:23       Ready
1     1             3/4/18 15:24.      7/12/18 13:54.    Not Ready
1.    1           7/12/18 13:55.   11/22/19 11:20    Missing    
1.    1.            11/22/19 11:21.   9/25/20 1:12.     Ready   
1.    1.            9/25/20 1:13       12/31/99.           Missing          

1.    2             1/1/16 10:00       2/2/17 17:20       Ready
1     2             2/2/17 17:21.      5/25/18 1:23.      Missing
1.    2           5/25/18 1:24       9/2/18 4:15         Not Ready  
1.    2            9/2/18 4:16.         6/3/21 7:04.        Missing 
1.    2            6/3/21 7:04.    12/31/99.           Ready

Output for not ready: ( below are the dates where each group has not ready status)

5/25/18 1:24.   7/12/18 13:54 Not Ready

Missing: ( Below are the date where each group has Missing status)

9/25/20 1:13   6/3/21 7:04    Missing

'''

Note-> Each ID can have any number of groups. Database is Snowflake

Can you share what is output expected, not clear from your query — akshindesnowflake, Jun 21 '21 at 15:20

score 0 · Answer 1 · answered Jun 21 '21 at 15:22

You can do this by unpivoting and counting. Assuming that the periods do not overlap for a given id:

with x as (
      select eff_date as date, 1 as inc
      from t
      where status = 'Missing'
      union all
      select end_date, -1 as inc
      from t
      where status = 'Missing'
    )
select date, next_date, active_on_date
from (select date,
             sum(sum(inc)) over (order by date) as active_on_date,
             lead(date) over (order by date) as next_date
      from x
      group by date
     ) x
where active_on_date = (select count(distinct id) from t);

Note: This handles one status at a time, which is what this question is asking. If you want to handle all event types, then ask a new question with appropriate sample data, desired results, and explanation.

Find minimum overlap of Each Status

1 Answers1