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。
沒有留言:
張貼留言