Home 2017 October SQL Puzzle: SQL Advance Query – Generate the report on Missing Year – Month data

SQL Puzzle: SQL Advance Query – Generate the report on Missing Year – Month data

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:

Expected Data:

Create a table with sample data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: SQL Advance Query - Find the average shared salary by each departmentSQL Puzzle: SQL Advance Query - To Swap the values in the Table
Comments: 3
  1. Dinesh.IS
    October 27, 2017 at 8:21 am

    ;
    WITH CTE
    As
    (Select Convert(DateTime,Convert(VarChar(6),YearMonthCol)+’01’) As DateAndTime From Dates
    )
    ,
    N
    As
    (Select Min(DateAndTime) As FromDate,Max(DateAndTime) As EndDate
    From CTE
    ),
    F
    As
    (Select FromDate,EndDate
    From N
    Union All
    Select DateAdd(M,1,FromDate),EndDate
    From F Where DateAdd(M,1,FromDate)<=EndDate
    )
    Select Left(Convert(VarChar,FromDate,111),7) As AllMonths From F

    ReplyCancel
  2. Dinesh.IS
    October 27, 2017 at 8:22 am

    ;
    WITH CTE
    As
    (Select Convert(DateTime,Convert(VarChar(6),YearMonthCol)+’01’) As DateAndTime From Dates
    ),
    N
    As
    (Select Min(DateAndTime) As FromDate,Max(DateAndTime) As EndDate
    From CTE
    Union All
    Select DateAdd(M,1,FromDate) As FromDate,EndDate
    From N Where DateAdd(M,1,FromDate)<=EndDate)
    Select Left(Convert(VarChar,FromDate,111),7) As AllMonths From N

    ReplyCancel
  3. Sarfaraz Mohammed
    December 23, 2019 at 12:19 pm

    DECLARE @Dates TABLE
    (YearMonthCol INT)

    INSERT @Dates
    VALUES
    (‘201501’)
    ,(‘201506’)
    ,(‘201601’)
    ,(‘201608’)
    ,(‘201702’)
    ,(‘201704′)

    DECLARE @MinDate VARCHAR(8),@MaxDate VARCHAR(8)
    SELECT @MinDate=CONCAT(MIN(d.YearMonthCol),’01’),@MaxDate=CONCAT(MAX(d.YearMonthCol),’01’) FROM @Dates d

    ;WITH CTE_Data
    AS
    (
    SELECT TOP (DATEDIFF(MONTH,@MinDate,@MaxDate)+1)
    CONVERT(VARCHAR(6),DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY a.object_id) – 1, @MinDate),112) AS AllMonths
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
    )
    SELECT AllMonths FROM CTE_Data cd
    ORDER BY 1 DESC

    ReplyCancel

Leave a Reply to Dinesh.IS Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

October 25, 2017 3 Comments SQL Puzzle, , , , , , , , , , , , ,