Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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 ( '<x>' + CAST(c1.ranker AS VARCHAR(10)) + '</x>' 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