如何确定sql server中的连续日期记录

I have a data structure like this:

我有这样的数据结构:

ID     Status     Date
---    ------     ------------
1      I          2013-10-01
1      A          2013-10-02
1      A          2013-10-03
1      I          2013-10-04
1      I          2013-10-05
1      I          2013-10-06
1      A          2013-10-07
1      I          2013-10-08

I want to determine how many status I are consecutive. In this case 3 (2013-10-04, 05 and 06).

我想确定我连续多少状态。在这种情况下3(2013-10-04,05和06)。

something like this:

像这样的东西:

Status   ID   Total_consecutive
-------  ---  ------------------
A        1    2
I        1    3

thanks in advance.

提前致谢。

2 个解决方案

#1

I believe this will give you the correct result:

我相信这会给你正确的结果:

declare @t table
(
    ID INT,    
    [Status] CHAR(1),    
    [Date] DATE
);
insert @t (ID, [Status], [Date])
values (1, 'I', '2013-10-01'),
(1, 'A', '2013-10-02'),
(1, 'A', '2013-10-03'),
(1, 'I', '2013-10-04'),
(1, 'I', '2013-10-05'),
(1, 'I', '2013-10-05'),
(1, 'I', '2013-10-05'),
(1, 'I', '2013-10-06'),
(1, 'A', '2013-10-10')

;with a as
(
select ID, [Status], [Date], 
row_number() over (partition by id order by [Status], [Date])-
row_number() over (partition by id order by [Date], [Status]) rn1

from @t
)
select ID, [Status], min([Date]) Start, max([Date]) [End], 
           count(*) [Total_consecutive] 
from a
group by id, [Status], rn1
having count(*) > 1

Result:

ID  Status  Start       End         Total_consecutive
1   A       2013-10-02  2013-10-03  2
1   I       2013-10-04  2013-10-06  5

#2

This solution will give you also the start date and the end date:

此解决方案还将为您提供开始日期和结束日期:

DECLARE @MyTable TABLE
(
    ID INT,    
    [Status] CHAR(1),    
    [Date] DATE
);
INSERT @MyTable (ID, [Status], [Date])
SELECT 1, 'I', '2013-10-01' UNION ALL
SELECT 1, 'A', '2013-10-02' UNION ALL
SELECT 1, 'A', '2013-10-03' UNION ALL
SELECT 1, 'I', '2013-10-04' UNION ALL
SELECT 1, 'I', '2013-10-05' UNION ALL
SELECT 1, 'I', '2013-10-06' UNION ALL
SELECT 1, 'A', '2013-10-07' UNION ALL
SELECT 1, 'I', '2013-10-08';

SELECT  y.ID, 
        y.[Status], 
        y.GroupID,
        Start = MIN(y.[Date]),
        [End] = MAX(y.[Date]),
        [Days]= DATEDIFF(DAY, MIN(y.[Date]), MAX(y.[Date])) + 1
FROM
(
SELECT  x.ID, 
        x.[Status], 
        x.[Date],
        GroupID = DATEDIFF(DAY, 0, x.[Date]) - ROW_NUMBER() OVER(PARTITION BY x.[ID], x.[Status] ORDER BY x.[Date])
FROM    @MyTable x
) y
GROUP BY y.ID, y.[Status], y.GroupID
HAVING MIN(y.[Date]) <> MAX(y.[Date]);

Results:

ID Status GroupID Start      End        Days
-- ------ ------- ---------- ---------- ----
1  A      41546   2013-10-02 2013-10-03 2
1  I      41547   2013-10-04 2013-10-06 3

#1