CODEWITHSUNDEEP
sql-serversql-insertdata-warehousewindow-functionssql-data-warehouse
George Eivaz
George Eivaz

Reputation: 145

SQL Server fill in Empty Rows

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

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

Upvotes: 0

Views: 305

Answers (2)

Rachel_Wang
Rachel_Wang

Reputation: 57

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
    */

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 1

Related Questions