[Challenge] 上班時間統計

Beyond Relational TSQL Challenge 2

• 資料來源（題目內有資料來源的 script）

ID          StartDate               EndDate
----------- ----------------------- -----------------------
1           2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2           2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3           2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4           2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5           2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6           2009-01-27 21:15:00.000 2009-01-28 09:15:00.000

• 結果

StartDate               EndDate                 Hours       Minutes 
----------------------- ----------------------- ----------- ----------- 
2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 7           0 
2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 15          0 
2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 9           0 
2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 7           45 
2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0           0 
2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 1           15

• 題目說明

上班時間定義是從星期一至星期五的0800至1700；如果開始日期是星期五中午1200至星期一上午1000，只能計算星期五中午1200至星期五下午1700和星期一上午0800至1200這段時間。

• 個人解法：

SET DATEFORMAT MDY

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 

;
WITH CTE AS
(
  SELECT ID,StartDate,EndDate,0 AS Counter -- 利用 Counter 欄位來計算分鐘
  FROM @t
  UNION ALL
  SELECT ID,DATEADD(mi,1,StartDate),EndDate,Counter + 1 -- 一次加一分鐘來跑遞迴 
  FROM CTE
  WHERE StartDate < EndDate -- 遞迴結束條件
)
SELECT T.ID,T.StartDate,T.EndDate,
  ISNULL(T3.TotalMin / 60,0) AS hh,
  ISNULL(T3.TotalMin % 60,0) AS mi
FROM @t AS T 
  LEFT JOIN
    (
      SELECT T2.ID,SUM(DayMin) AS TotalMin
      FROM
        (
          SELECT T1.ID,T1.Date,MAX(T1.Counter) - MIN(T1.Counter) AS DayMin
          FROM
            (
              SELECT ID,StartDate,Counter,
                     CONVERT(char(10),StartDate,112) AS Date,
                     CAST(CONVERT(char(10),StartDate,112) + '08:00:00.000' AS datetime) AS DayStart,
                     CAST(CONVERT(char(10),StartDate,112) + '17:00:00.000' AS datetime) AS DayEnd
              FROM CTE
            ) AS T1
          WHERE 
             DATEPART(dw,T1.StartDate) NOT IN (1,7) 
             -- 星期一至星期五 
             AND T1.StartDate BETWEEN DayStart AND DayEnd
             -- 工作時間從早上 0800 至 1700
          GROUP BY T1.ID,T1.Date
        ) AS T2
      GROUP BY T2.ID
    ) AS T3 ON T.ID = T3.ID
OPTION (MAXRECURSION 0) -- 放在T-SQL語法最後面

• 個人解法邏輯
1. 在 CTE 中，每筆資料利用 StartDate 為起始時間，一次遞迴一分鐘，EndDate 為遞迴結束時間，同時新增一個 Counter 欄位來累積分鐘。
2. 根據上班時間定義，把資料的上班時間和下班時間列出來。
3. ID 資料有跨天數情形，針對 ID 和 StartDate 群組，來了解每一天到底上班幾分鐘，同時判斷資料是否在星期一至星期五且上班時間為0800至1700間；再針對 ID 群組，對每個 ID 內的每天上班分鐘進行加總（每個 ID 上班總分鐘數）。
4. 上班總分數 /（除法） 和 % （模除）來求得時數和分鐘。
   

參考資料：
• Beyond Relational TSQL Challenge 2 - Calculate the Business Hours
• TSQL Challenge 2 – Winners