T-SQL Query | [ Complete the sequence Puzzle ]

The puzzle is simple. Here the participants were given month-wise score values and were asked to complete the sequence by creating entries for missing month. Please check out the sample input and expected output for details.

Sample Input

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

Expected output

 yearmonth score 200711 100 200712 100 200801 100 200802 99 200803 95 200804 95 200805 95 200806 95 200807 95 200808 95 200809 95 200810 95 200811 95 200812 95 200901 95 200902 95 200903 100 200904 100 200905 100 200906 100 200907 100 200908 100 200909 100 200910 100 200911 100 200912 100 201001 100 201002 100 201003 100 201004 100 201005 100 201006 100 201007 100 201008 100 201009 100 201010 100 201011 100 201012 100 201101 100 201102 100 201103 100 201104 100 201105 100 201106 100 201107 100 201108 100 201109 100 201110 100 201111 100 201112 100 201201 100 201202 100 201203 100 201204 100 201205 100 201206 100 201207 100 201208 100 201209 100 201210 100 201211 100 201212 100 201301 100 201302 100 201303 100 201304 100 201305 100 201306 100 201307 100 201308 100 201309 100 201310 100 201311 100 201312 100 201401 100 201402 100 201403 100 201404 100 201405 100 201406 100 201407 100 201408 100 201409 100 201410 100 201411 100 201412 100 201501 100 201502 100 201503 100 201504 100

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with 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) ```

UPDATE – 20-Apr-2015 – Solution 1

 ``` -- ;WITH CTE AS ( SELECT CAST ( LEFT(YearMonth,4) + '/' + RIGHT(YearMonth,2) + '/' + '01' AS VARCHAR(10)) YearMonth , Score ,ROW_NUMBER() OVER ( ORDER BY YearMonth ) ranker FROM SCORES ) ,CTE1 AS ( SELECT c1.YearMonth, CAST ( '' + CAST(c1.ranker AS VARCHAR(10)) + '' AS XML ) ranker,c1.ranker rnk ,c1.Score,ISNULL(c2.YearMonth,c1.YearMonth) YearMonthEnds FROM CTE c1 LEFT JOIN CTE c2 ON c1.ranker = c2.ranker - 1 ) ,CTE2 AS ( SELECT *, DATEDIFF ( M ,YearMonth, YearMonthEnds ) diff , CAST ( REPLICATE( CAST ( ranker AS VARCHAR(10)) + ',' , CASE WHEN DATEDIFF ( M ,YearMonth, YearMonthEnds ) > 1 THEN DATEDIFF ( M ,YearMonth, YearMonthEnds ) ELSE 1 END ) AS XML ) xmlcol FROM CTE1 ) SELECT CONVERT(Varchar(7),DATEADD(m,RowNum,YearMonth),121) as AllocationMonth,Score FROM CTE2 s CROSS APPLY ( SELECT project.D.value('.','VARCHAR(50)') as SplitData,ROW_NUMBER() OVER(Partition by Project.D.value('.', 'varchar(50)') ORDER BY (SELECT NULL)) -1 as RowNum FROM s.xmlcol.nodes('x') as project(D) ) p ORDER BY AllocationMonth DESC -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com