星期五, 1月 25, 2013

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

此論壇問題是要計算員工班別從哪一天開始並計算連續幾天是同一個班別。
[SQL] 次序函數應用-判斷是否為連續日期-1
    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 = '早班'
[SQL] 次序函數應用-判斷是否為連續日期-2
判斷連續日期為此問題重點,利用 ROW_NUMBER() 產生 ROWNO 欄位後,把此 ROWNO 資料去減掉 WorkDate,從上圖的 Grp 欄位可以看出,假如該班別為連續日期,Grp 欄位內的日期資料會是相同日期,依此來判斷是否為連續日期。

沒有留言:

張貼留言