查找每个状态的最小重叠

Question

我需要在所有组中查找状态为Missing/notready的日期范围(只有重叠的日期范围，其中所有组的状态都为Missing/noready(''

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

未准备就绪的输出：(以下是每组未准备就绪状态的日期(

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

丢失：(以下是每组具有丢失状态的日期(

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

''

注意->每个ID可以有任意数量的组。数据库是雪花

Answer 1

您可以通过取消抓取和计数来完成此操作。假设给定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);

注意：它一次处理一个状态，这就是这个问题所问的。如果您想处理所有事件类型，请提出一个新的问题，并提供适当的示例数据、所需结果和解释。