SQL連續日期累計計數

我正在做一些花名冊分析，需要確定員工何時連續作業了 5 天或更長時間。在我的表中，我可以提取如下所示的資料（注意，還有更多列，這只是一個縮減示例）：

我所追求的是這樣的：

我真的不知道如何獲得連續幾天的累積計數，所以你能提供的任何幫助都會很棒

uj5u.com熱心網友回復：

可能有人會想出一個絕妙的解決方案，但這會做。您的問題看起來像“差距和島嶼”問題。找到日期范圍的孤島，我們可以很容易地找出其余的。在下面的 SQL 中，@mindate 不是必須的，但它更容易。

CREATE TABLE #temptable
(
    [Emp] CHAR(4),
    [startDate] DATE,
    [First_Entry] BIT
);
INSERT INTO #temptable
(
    [Emp],
    [startDate],
    [First_Entry]
)
VALUES
('1234', N'2016-06-23', 1),
('1234', N'2016-06-24', 1),
('1234', N'2016-06-24', 0),
('1234', N'2016-06-25', 1),
('1234', N'2016-06-26', 1),
('1234', N'2016-06-27', 1),
('1234', N'2016-06-28', 1),
('1234', N'2016-06-29', 1),
('1234', N'2016-06-29', 0),
('1234', N'2016-06-30', 1),
('1234', N'2016-07-02', 1),
('1234', N'2016-07-03', 1),
('1234', N'2016-07-03', 0),
('1234', N'2016-07-04', 1),
('1234', N'2016-07-04', 0),
('1234', N'2016-07-05', 1),
('1234', N'2016-07-06', 1),
('1234', N'2016-07-09', 1),
('1234', N'2016-07-10', 1),
('1234', N'2016-07-11', 1),
('1234', N'2016-07-12', 1);


DECLARE @minDate DATE;
SELECT @minDate = DATEADD(d, -1, MIN(startDate))
FROM #temptable;

WITH firstOnly
AS (SELECT *
    FROM #temptable
    WHERE First_Entry = 1),
     grouper (emp, startDate, grp)
AS (SELECT Emp,
           startDate,
           DATEDIFF(d, @minDate, startDate) - ROW_NUMBER() OVER (PARTITION BY Emp ORDER BY startDate)
    FROM firstOnly),
     islands (emp, START, [end])
AS (SELECT emp,
           MIN(startDate),
           MAX(startDate)
    FROM grouper
    GROUP BY emp,
             grp),
     consecutives (emp, startDate, consecutive_days)
AS (SELECT f.Emp,
           f.startDate,
           --       i.START,
           --       i.[end],
           ROW_NUMBER() OVER (PARTITION BY f.Emp, i.START ORDER BY i.START)
    FROM firstOnly f
        INNER JOIN islands i
            ON f.startDate
               BETWEEN i.START AND i.[end])
SELECT t.Emp,
       t.startDate,
       t.First_Entry,
       c.consecutive_days,
       CAST(CASE
                WHEN c.consecutive_days < 5 THEN
                    0
                ELSE
                    1
            END AS BIT) Over_5,
       CASE
           WHEN c.consecutive_days < 5 THEN
               'Worked < 5'
           ELSE
               'Worked >= 5'
       END [Status]
FROM consecutives c
    INNER JOIN #temptable t
        ON t.Emp = c.emp
           AND t.startDate = c.startDate;

DROP TABLE #temptable;

uj5u.com熱心網友回復：

這是一個孤島和差距問題，你可以嘗試使用LAG視窗函式來獲取startDate每一天的前一行Emp，十個使用SUM視窗函式來計算哪些天是連續的。

最后，我們可以用CASE WHEN運算式來判斷一天是否大于5。

;WITH CTE AS (
    SELECT  [Emp],
        [startDate],
        [First_Entry],
        SUM(CASE WHEN DATEDIFF(dd,f_Dt,startDate) <= 1 THEN 0 ELSE 1 END) OVER(PARTITION BY Emp ORDER BY startDate) grp 
    FROM (
      SELECT *,
           LAG(startDate,1,startDate) OVER(PARTITION BY Emp ORDER BY startDate) f_Dt
      FROM T
    ) t1
)
SELECT  [Emp],
    [startDate],
    [First_Entry],
    SUM(CASE WHEN First_Entry = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY Emp,grp ORDER BY startDate) Consecutive_Days,
    (CASE WHEN SUM(CASE WHEN First_Entry = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY Emp,grp ORDER BY startDate) >= 5 THEN 1 ELSE 0 END) Over_5,
     (CASE WHEN SUM(CASE WHEN First_Entry = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY Emp,grp ORDER BY startDate) >= 5 THEN 'Worked >= 5' ELSE 'Worked < 5' END) Status
FROM CTE 

sqlfiddle

標籤：sql sql服务器 日期 差距和岛屿

上一篇：使用條件將日期列添加到資料框中

下一篇：應用程式腳本：我無法正確過濾列中的日期