CODEWITHSUNDEEP
sql-servert-sql
Chuck Bernardes
Chuck Bernardes

Reputation: 25

How to find missing dates by ID

I need help modifying a script to find the missing RevenuePeriod by ID within a given time frame. I have found a similar script that finds the missing date for a given ID but I don't know how to modify the script where it will give the missing dates per ID. 

create table #WorksheetHistory (WorksheetID [int] IDENTITY(1,1) ,ID varchar(6), RevenuePeriod datetime)
insert into #WorksheetHistory (ID,RevenuePeriod) 

SELECT '000001','2015-06-01 00:00:00.00' Union All
SELECT '000001','2015-07-01 00:00:00.00' Union All
SELECT '000001','2015-11-01 00:00:00.00' Union All
SELECT '000001','2015-12-01 00:00:00.00' Union All
SELECT '000002','2015-06-01 00:00:00.00' Union All
SELECT '000002','2015-12-01 00:00:00.00'

DECLARE @EndDate datetime
DECLARE @StartDate datetime

SET @StartDate  = '2015-06-01 00:00:00.00'
SET @EndDate    = '2015-12-01 00:00:00.00'

;WITH Dates as
(
SELECT @StartDate AS dt
    UNION ALL
SELECT DATEADD(month, 1, dt) as dt
    FROM Dates
    WHERE dt < (select dateadd(month,-1,@EndDate)  enddate)
)

select Month(dt) as dtMonth, Year(dt) dtYear
from Dates d left outer join #WorksheetHistory w
on dateadd(month, datediff(month,0,d.dt),0)  = dateadd(month, datediff(month,0,w.RevenuePeriod),0) Where RevenuePeriod is null

Drop Table #WorksheetHistory

The current output returns the following output. I realize in the script it doesnt have an ID returned, but even if I do, it would return null value as the script indicates return null dates. I don't know how to put the associated ID in it too.

dt
2015-08-01 00:00:00.000
2015-09-01 00:00:00.000
2015-10-01 00:00:00.000

My desired result would be return the Missing ID's with the respective missing dates.

ID      dt
000001  2015-08-01 00:00:00.00
000001  2015-09-01 00:00:00.00
000001  2015-10-01 00:00:00.00
000002  2015-07-01 00:00:00.00
000002  2015-08-01 00:00:00.00
000002  2015-09-01 00:00:00.00
000002  2015-10-01 00:00:00.00
000002  2015-11-01 00:00:00.00

Upvotes: 0

Views: 88

Answers (1)

S. Rojak
S. Rojak

Reputation: 454

Use EXCEPT:

WITH Dates as
(
    SELECT @StartDate AS dt
    UNION ALL
    SELECT DATEADD(month, 1, dt) as dt
    FROM Dates
    WHERE dt < DATEADD(m, -1, @EndDate)
)
-- all the possible combinations
SELECT w.ID, d.dt
FROM Dates d
CROSS JOIN (
    SELECT ID
        FROM #WorksheetHistory
        GROUP BY ID
) w
EXCEPT
-- the combinations you actually have
SELECT w.ID, d.dt
FROM Dates d
JOIN #WorksheetHistory w
    ON d.dt = w.RevenuePeriod;

Upvotes: 1

Related Questions