/*************************************************************************************************

** File: "jesse_mclain_tsqlchallenge_12.sql"

** Desc: solution for TSQL Challenge #12

** http://beyondrelational.com/blogs/tc/archive/2009/08/09/tsql-challenge-12-build-sequential-ranges-of-dates-with-propagation-to-missing-values.aspx

**

**

** Auth: Jesse McLain

** Email: mailto:jesse@jessemclain.com

** Web: http://www.jessemclain.com

** Blog: http://www.jessesql.blogspot.com

**

** Date: 09/02/2009

**

**************************************************************************************************/

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 BaseScores AS (

SELECT

YearMonth,

Score,

ParsedDate = CONVERT(datetime,

SUBSTRING(CONVERT(char(6), YearMonth), 5, 2) + '/1/' + LEFT(YearMonth, 4))

FROM @Scores

/* this recursive CTE picks the earliest score date and builds a table of each month between then

and the current date */

), AllMonths AS (

SELECT

ParsedDate,

Score

FROM BaseScores

WHERE ParsedDate = (SELECT MIN(ParsedDate) FROM BaseScores)

UNION ALL SELECT

ParsedDate = DATEADD(m, 1, AllMonths.ParsedDate),

Score = ISNULL((SELECT Score FROM BaseScores

WHERE BaseScores.ParsedDate = DATEADD(m, 1, AllMonths.ParsedDate)), AllMonths.Score)

FROM AllMonths

WHERE DATEADD(m, 1, AllMonths.ParsedDate) <= GETDATE()

)

SELECT

YearMonth = CONVERT(int, LEFT(REPLACE(CONVERT(VARCHAR, ParsedDate, 102), '.', ''), 6)),

Score

FROM AllMonths

ORDER BY 1 DESC