This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to generate the report on missing Year – Month data.
Input Data:
1 2 3 4 5 6 7 8 |
YearMonthCol ------------ 201501 201506 201601 201608 201702 201704 |
Expected Data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
AllMonths --------- 2017-04 2017-03 2017-02 2017-01 2016-12 2016-11 2016-10 2016-09 2016-08 2016-07 2016-06 2016-05 2016-04 2016-03 2016-02 2016-01 2015-12 2015-11 2015-10 2015-09 2015-08 2015-07 2015-06 2015-05 2015-04 2015-03 2015-02 2015-01 |
Create a table with sample data:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Dates (YearMonthCol INT) GO INSERT Dates VALUES ('201501') ,('201506') ,('201601') ,('201608') ,('201702') ,('201704') GO |
Solution:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
;WITH CTE AS ( SELECT CAST ( LEFT(YearMonthCol,4) + '/' + RIGHT(YearMonthCol,2) + '/' + '01' AS VARCHAR(10)) YearMonthCol ,ROW_NUMBER() OVER ( ORDER BY YearMonthCol ) ranker FROM Dates ) ,CTE1 AS ( SELECT c1.YearMonthCol, CAST ( ' ,ISNULL(c2.YearMonthCol,c1.YearMonthCol) YearMonthEnds FROM CTE c1 LEFT JOIN CTE c2 ON c1.ranker = c2.ranker - 1 ) ,CTE2 AS ( SELECT *, DATEDIFF ( M ,YearMonthCol, YearMonthEnds ) diff , CAST ( REPLICATE( CAST ( ranker AS VARCHAR(10)) + ',' , CASE WHEN DATEDIFF ( M ,YearMonthCol, YearMonthEnds ) > 1 THEN DATEDIFF ( M ,YearMonthCol, YearMonthEnds ) ELSE 1 END ) AS XML ) xmlcol FROM CTE1 ) SELECT CONVERT(Varchar(7),DATEADD(m,RowNumber,YearMonthCol),121) as AllMonths 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 RowNumber FROM s.xmlcol.nodes('x') as project(D) ) t ORDER BY AllMonths DESC |
Please try the different solution for this puzzle and share it via comment...