TSQL Challenges

TSQL Challenge #12 - Build sequential ranges of dates with propagation to missing values

The News

Here we go again with a new challenge, we hope that you will have some time to share with us working on the challenge even if it’s summer time. The TSQL Challenge teams are growing and we are very exited to have four new Sql Server MVPs in the persons of Mangal Pardeshi, Peter Larsson, Alejandro Messa and Adam Haines. I would like to wish them again a big welcome!

As usual we will be very happy to hear your comments and feedback on what we do with TSQL Challenges. You can post a comment or send an email to tc@beyondrelational.com.

The Context

You are working for an online gaming company and as usual when we talk about games we need to manage scores. Some features in your system had recently changed. Before when a user get connected you only shown him its last score, but now you have to present him a graph month by month of its best score in each one since its first game until the current date.

Here is the scores tables of an user for each month he played:

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

The Challenge

When a user connects for the first time after the deployment of the new system you will need to produce a table based on the original scores table with the following conditions:

• 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).

Here is the resulting table you need to produce:

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

Sample data

Use the following script to generate the sample data:

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)

Notes

1. Write a single query that produces the expected output.
2. The query can target any version of Sql Server
3. Send your entries to tc@beyondrelational.com
4. Do not include your solutions in the body of the email. Send them as an attachment in the email. Name it “firstname_lastname.sql”
5. Add ‘TSQL Challenge #12’ in the subject line of the email.
6. Last date to submit your entries: Monday 24 August 2009
7. Use this forum for any questions related to TSQL Challenge #12

About the Authors

Rui Carvalho is the director for TSQLChallenges. He is a senior developer on Sql Server and .Net mainly experienced in web applications. See complete profile. Mangal is an E&TC Engineer from Pune University. He started his career as an ERP Technical Consultant in 2007. Currently he works as a BI Developer.    His core areas of expertise are Data Warehousing and Business Intelligence. See complete profile