[Challenge] 克服日期計算的恐懼

Beyond Relational TSQL Beginner's Challenge 2

• 說明：

邀請您來挑戰日期計算。許多人對於日期計算有恐懼，而挑戰目標就是要克服日期計算的恐懼。這個挑戰是利用年、月、星期和每個月第幾個星期來計算日期；例如：2010年01月的第二個禮拜日是幾號，答案是 '2010-01-10'

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

Yr    Mon    Dy  Dyno
-------------------------------
2010  Jan  Sun    2
2005  Jan  Mon    3
1995  Feb  Sun    1
2000  Feb  Wed    4
1982  Mar  Tue    2
2010  Mar  Tue    8

• 結果

Yr     Mon    Dy   Dyno  Date
----------------------------------------------
1982    Mar    Tue    2   1982-03-09
1995    Feb    Sun    1   1995-02-05
2000    Feb    Wed    4   2000-02-23
2005    Jan    Mon    3   2005-01-17
2010    Jan    Sun    2   2010-01-10
2010    Mar    Tue    8   Invalid Date

• 規則
1. 如果這個日期不存在，必須顯示'Invalid Date'
2. T-SQL 必須為 'SELECT' 或 ';WITH' 開始的單一查詢
3. 產生的結果必須根據日期排序
   

• 個人解法

DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)
INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
    SELECT 2010,'Jan','Sun',2 UNION ALL
 SELECT 2005,'Jan','Mon',3 UNION ALL
 SELECT 1995,'Feb','Sun',1 UNION ALL
 SELECT 2000,'Feb','Wed',4 UNION ALL
 SELECT 1982,'Mar','Tue',2 UNION ALL
 SELECT 2010,'Mar','Tue',8

;
WITH CTE_Data AS
(
  SELECT T1.* , 
         DATEADD(DD,-1,DATEADD(MM,1,T1.DateStart)) AS DateEnd -- 月底日期
  FROM
    (
      SELECT T.* , 
             CASE 
               WHEN ISDATE(T.Date) = 1 THEN CAST(T.Date AS datetime)
               ELSE NULL
             END AS DateStart -- 月初日期
      FROM 
        (
          SELECT Yr,Mon,Dy,Dyno,
                 ROW_NUMBER() OVER (ORDER BY Yr) AS RandomOrder, -- 隨便給一個編號來當成唯一碼
                 CAST(Yr AS CHAR(4)) + 
                 CASE Mon
                   WHEN 'Jan' THEN '01'
                   WHEN 'Feb' THEN '02'
                   WHEN 'Mar' THEN '03'
                   WHEN 'Apr' THEN '04'
                   WHEN 'May' THEN '05'
                   WHEN 'Jun' THEN '06'
                   WHEN 'Jul' THEN '07'
                   WHEN 'Aug' THEN '08'
                   WHEN 'Sep' THEN '09'
                   WHEN 'Oct' THEN '10'
                   WHEN 'Nov' THEN '11'
                   WHEN 'Dec' THEN '12'
                   ELSE NULL
                 END + 
                '01' AS Date, -- 日期字串
                CASE Dy
                  WHEN 'Sun' THEN 1
                  WHEN 'Mon' THEN 2
                  WHEN 'Tue' THEN 3
                  WHEN 'Wed' THEN 4
                  WHEN 'Thu' THEN 5
                  WHEN 'Fri' THEN 6
                  WHEN 'Sat' THEN 7
                  ELSE NULL
                END AS dwno -- 每個星期的號碼
          FROM @tbl
        ) AS T
    ) AS T1
)
,CTE AS
(
  SELECT Yr,Mon,Dy,Dyno,RandomOrder,dwno,DateStart,DateEnd,DateStart AS DateLoop
  FROM CTE_Data
  UNION ALL
  SELECT Yr,Mon,Dy,Dyno,RandomOrder,dwno,DateStart,DateEnd,DATEADD(DD,1,DateLoop) -- 一次加一天
  FROM CTE
  WHERE DateLoop < DateEnd -- 月底為 CTE 結束日期
)
SELECT T5.Yr,T5.Mon,T5.Dy,T5.Dyno,
       CASE 
         WHEN T4.DateLoop IS NULL THEN 'Invalid Date'
         ELSE CONVERT(char(10),T4.DateLoop,120)
       END AS Date
FROM CTE_Data AS T5
  LEFT JOIN
    (
      SELECT T3.*
      FROM 
        (
          SELECT T2.* , 
                 ROW_NUMBER() OVER (PARTITION BY T2.DateStart,T2.Datedw ORDER BY T2.DateLoop) AS dwOrder
                 -- 以 開始日期和星期幾 為群組，列出全部資料的順序
          FROM
            (
              SELECT * , DATEPART(dw,DateLoop) AS Datedw -- 找出每一天的星期
              FROM CTE
            ) AS T2
        ) AS T3
      WHERE T3.Datedw = T3.Dwno -- 星期幾
              AND T3.dwOrder = T3.dyno -- 當月的第幾個禮拜
    ) AS T4 ON T5.RandomOrder = T4.RandomOrder

• 個人解法邏輯
1. CTE_Data 主要為整理資料，產生月初日期、月底日期和星期號碼。
2. CTE 引用 CTE_Data 資料，從每一筆資料的月初 日期，跑遞迴至月底日期
3. 根據 CTE 遞迴結果，產生每一天的星期，並利用日期和星期為群組，對 CTE 資料進行排序來判斷是當月第幾個禮拜的星期幾。
4. 跟 CTE_Data 進行資料合併，並把日期調整成規則要求格式。

參考資料：
• TSQL Beginner's Challenge 2 - Fight Your Fear for Date Calculations
• MSDN：SET LANGUAGE、sys.syslanguages、SET DATEFIRST、@@DATEFIRST
• 字母日期格式的問題