~楓花雪岳~: [Challenge] 補齊日期範圍內缺少的值

星期五, 3月 23, 2012

[Challenge] 補齊日期範圍內缺少的值

Beyond Relational TSQL Challenge 12

資料來源

YearMonth   Score
----------- -----------
200903      100
200803      95
200802      99
200801      100
200711      100

產生結果

YearMonth   Score
----------- -----------
200908      100
200907      100
200906      100
200905      100
200904      100
200903      100
200902      95
200901      95
200812      95
200811      95
200810      95
200809      95
200808      95
200807      95
200806      95
200805      95
200804      95
200803      95
200802      99
200801      100
200712      100
200711      100

規則

Create a new couple year/month for each missing month between two valid months of the original table
For each new couple created, you should recopy the score of the last month he played.
Continue the list until the current month (included).

個人解法

DECLARE @Scores TABLE
(        
  YearMonth INT,        
  Score  INT
)             
INSERT @Scores VALUES(200903, 100)        
INSERT @Scores VALUES(200803, 95)        
INSERT @Scores VALUES(200802, 99)        
INSERT @Scores VALUES(200801 ,100)        
INSERT @Scores VALUES(200711, 100)

; 
WITH CTE AS
(
  SELECT 
    CASE 
      WHEN ISDATE(T.Date) = 1 THEN CAST(T.Date AS datetime)
      ELSE NULL
    END AS DateType ,
    Score ,
    ROW_NUMBER() OVER (ORDER BY T.Date DESC) AS SeqNO
  FROM
    (
       SELECT 
         CAST(YearMonth AS char(6)) + '01' AS Date , 
         Score
       FROM @Scores 
    ) AS T
)
,
CTE_Loop AS 
(
  SELECT MIN(DateType) AS Date 
  FROM CTE
  UNION ALL 
  SELECT DATEADD(mm,1,Date)
  FROM CTE_Loop
  WHERE DATEADD(mm,1,Date) < CAST('20090901' AS DATETIME)
)
SELECT CAST(CONVERT(char(6),L.Date,112) AS int) AS YearMonth, T.Score
FROM CTE_Loop AS L 
  JOIN
    (
      SELECT T1.DateType AS DateStart , ISNULL(T2.DateType , CAST('20090901' AS datetime)) AS DateEnd , T1.Score
      FROM CTE AS T1 
        LEFT JOIN CTE AS T2 ON T1.SeqNO = T2.SeqNO + 1
    ) AS T ON L.Date >= T.DateStart AND L.Date < T.DateEnd
ORDER BY L.Date DESC

個人解法邏輯

把資料來源的數值日期轉成日期形態並利用 ROW_NUMBER() 產生序號（CTE）。
引用 CTE 來跑遞迴產生所需的日期 Table（CTE_Loop）。
利用步驟一產生的序號來產生兩個日期間的起始時間和結束時間，把起始時間和結束時間當成篩選條件來 JOIN CTE_Loop 日期 Table。

~楓花雪岳~

星期五, 3月 23, 2012

[Challenge] 補齊日期範圍內缺少的值

沒有留言:

張貼留言