需要SQL server查询才能获取句点

Question

我有下表：其中每个成员每天有一个col1值。我需要为每个成员获取col1为零的句点。

Member_ID   | col1            | Date         
ADavis      | 0               | 11-10-2013    
ADavis      | 0               | 11-11-2013   
ADavis      | 0               | 11-12-2013    
DSmith      | 1               | 11-10-2013    
DSmith      | 0               | 11-11-2013    
FRowden     | 0               | 11-10-2013     
FRowden     | 0               | 11-11-2013    
ADavis      | 1               | 11-13-2013   
ADavis      | 0               | 11-14-2013    
DSmith      | 1               | 11-12-2013    
DSmith      | 1               | 11-13-2013    
FRowden     | 1               | 11-12-2013    
FRowden     | 0               | 11-13-2013

输出

Member_ID   | Start_date      | End_date    
ADavis      | 11-10-2013      | 11-12-2013  
ADavis      | 11-14-2013      | 11-14-2013  
DSmith      | 11-11-2013      | 11-11-2013  
FRowden     | 11-10-2013      | 11-11-2013  
FRowden     | 11-13-2013      | 11-13-2013

Answer 1

您可以使用row_numbers():的差异

select Member_ID, min(date), max(date)
from (select *, row_number() over (partition by Member_ID order by date) seq1,
row_number() over (partition by Member_ID, col1 order by date) seq2
from table t
) t
where col1 = 0
group by Member_ID, (seq1 - seq2);

Answer 2

您可以观察到，如果您从日期中减去一个连续的数字，那么在日期是连续的情况下，您将获得一个常数值。

这提供了将连续的日子组合在一起所需的洞察力：

select member_id, min(date), max(date
from (select t.*,
row_number() over (partition by member_id order by date) as seqnum
from t
where col1 = 0
) t
group by member_id, dateadd(day, -seqnum, date);