~楓花雪岳~: [SQL] 次序函數應用-判斷是否為連續日期

星期五, 1月 25, 2013

[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 欄位內的日期資料會是相同日期，依此來判斷是否為連續日期。

~楓花雪岳~

星期五, 1月 25, 2013

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

沒有留言:

張貼留言