[SQL] 次序函數應用-判斷是否為連續日期

此論壇問題是要計算員工班別從哪一天開始並計算連續幾天是同一個班別。

T-SQL 解法

DECLARE @Temp TABLE (EmpNO char(10) , [WorkDate] datetime ,WorkType  char(4))
INSERT INTO @Temp VALUES('001','2013/01/10','早班')
INSERT INTO @Temp VALUES('001','2013/01/11','早班')
INSERT INTO @Temp VALUES('001','2013/01/12','早班')
INSERT INTO @Temp VALUES('001','2013/01/13','早班')
INSERT INTO @Temp VALUES('001','2013/01/14','休息')
INSERT INTO @Temp VALUES('001','2013/01/15','休息')
INSERT INTO @Temp VALUES('001','2013/01/16','休息')
INSERT INTO @Temp VALUES('001','2013/01/17','晚班')
INSERT INTO @Temp VALUES('001','2013/01/18','早班')
INSERT INTO @Temp VALUES('001','2013/01/21','早班')
INSERT INTO @Temp VALUES('001','2013/01/22','早班')
INSERT INTO @Temp VALUES('001','2013/01/23','早班')
INSERT INTO @Temp VALUES('001','2013/01/24','早班')

SELECT 
  T.EmpNO , 
  T.WorkType , 
  MIN(WorkDate) AS StartDate , -- 找出該班別起始日期
  COUNT(*) AS [Counts]         -- 該班別連續天數
FROM
  (
    SELECT 
      EmpNO , 
      WorkType , 
      WorkDate ,
      -- 重點邏輯
      DATEADD(DD , -1 * ROW_NUMBER() OVER (PARTITION BY EmpNO , WorkType ORDER BY WorkDate) ,    WorkDate) AS Grp
    FROM @Temp
  ) AS T
GROUP BY T.EmpNO , T.WorkType , T.Grp
ORDER BY EmpNO , StartDate

邏輯分析：以 001 早班為例說明

SELECT 
   EmpNO , 
   WorkType , 
   WorkDate ,
   ROW_NUMBER() OVER (PARTITION BY EmpNO , WorkType ORDER BY WorkDate) AS ROWNO ,
   DATEADD(DD , -1 * ROW_NUMBER() OVER (PARTITION BY EmpNO , WorkType ORDER BY WorkDate) , WorkDate) AS Grp
FROM @Temp
WHERE EmpNO = '001' 
          AND WorkType = '早班'

判斷連續日期為此問題重點，利用 ROW_NUMBER() 產生 ROWNO 欄位後，把此 ROWNO 資料去減掉 WorkDate，從上圖的 Grp 欄位可以看出，假如該班別為連續日期，Grp 欄位內的日期資料會是相同日期，依此來判斷是否為連續日期。

延伸閱讀
• [SQL] 次序函數應用-重覆資料不顯示
• [SQL] 次序函數應用-資料分組
• [Challenge] 從 Log 中計算 Server 停機時間

參考資料
• 論壇問題出處
• 論壇問題應用 - 一樣的TSQL 思考邏輯
• Gaps and Islands problem (SQL) 連續範圍