SQL Server fill in Empty Rows

Question

Good Afternoon,

I had an interesting question that I wanted to put the the stack overflow community. We have a data set in our data warehouse (SQL Server) with unique identifier and several months for that unique identifier. For example if one looked at it in Dec 2018 and saw these rows below:

Row ID  BeginDate  EndDate       Unique ID   Amount
178484  2018-01-01 2018-01-31    GroupID1    387.22
176555  2018-03-01 2018-03-31    GroupID1    751.07
170120  2018-04-01 2018-04-30    GroupID1    567.48
172037  2018-09-01 2018-09-30    GroupID1    587.51
179024  2018-10-01 2018-10-31    GroupID1    63.42
182061  2018-11-01 2018-11-30    GroupID1    728.04

What we would love is somehow to identify missing rows (months) that are missing. For example for the above, we would insert the following rows

Feb, May, Jun, Jul, Aug, and Dec The Final month that would be entered could be the date that the query is run.

It is important to note that obviously this is not the only row grouping in our database. Furthermore, we would to avoid cursors. We have tried doing this with a cursor and a temp table that holds all the valid values. But was hoping that there is a faster way to approach this.

Any help would really be appreciated on this.

All the best, George Eivaz

Since you are looking for missing months, can't you use a join? — Ilyes, Dec 17 '18 at 20:38
Just values from 1 to 12 as a table and left join them with your table on Month(YourDateColumn) where your datecolumn IS NULL — Ilyes, Dec 17 '18 at 20:41
I will give this a try. This might be simplest question in the history or stackoverflow — George Eivaz, Dec 17 '18 at 20:47

score 1 · Answer 1 · answered Dec 17 '18 at 20:50

Simply

SELECT M MonthNumber,
       DATENAME(Month, DATEADD(Month, M, -1)) MonthName
FROM T RIGHT JOIN 
     (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) TT(M)
ON MONTH(T.BeginDate) = TT.M
WHERE T.BeginDate IS NULL;

Returns:

+-------------+-----------+
| MonthNumber | MonthName |
+-------------+-----------+
|           2 | February  |
|           5 | May       |
|           6 | June      |
|           7 | July      |
|           8 | August    |
|          12 | December  |
+-------------+-----------+

Demo

score 0 · Answer 2 · answered Dec 31 '18 at 08:18

Please try following script which use NOT EXISTS to see if it satisfies your requirement.

 ----drop table test 
    create table test (
    [Row ID] int ,
    BeginDate  date, 
    EndDate   date,
    [Unique ID]   varchar(15),
    Amount decimal(10,2)
    )
    insert into test values 
    (178484,'2018-01-01','2018-01-31','GroupID1',387.22),
    (176555,'2018-03-01','2018-03-31','GroupID1',751.07),
    (170120,'2018-04-01','2018-04-30','GroupID1',567.48),
    (172037,'2018-09-01','2018-09-30','GroupID1',587.51),
    (179024,'2018-10-01','2018-10-31','GroupID1',63.42),
    (182061,'2018-11-01','2018-11-30','GroupID1',728.04)

    select M as MonthNumber ,DATENAME(Month, DATEADD(Month, M, -1)) as  MonthName
    from (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) TT(M)
    where not exists (select * from test where TT.M=MONTH(BeginDate))
    /*
    MonthNumber MonthName
    ----------- ------------------------------
    2           February
    5           May
    6           June
    7           July
    8           August
    12          December
    */

SQL Server fill in Empty Rows

2 Answers2