CODEWITHSUNDEEP
sql-server
Silentbob
Silentbob

Reputation: 3065

SQL Server : select data between date range where there is missing data

Using SQL Server 2012, I have a script that inserts 4 rows of data into a table each day. The data looks like this:

Complete Data

Sometimes one or more of the rows is not inserted as the source data is incomplete. This looks like this - the second column is called commodityID:

Missing Data

There are times where more than one row could be missing.

I am trying to write a query that will show me what data is missing, so for the example above it would be for commodityID = 2. I have another table which is a calendar (lists all dates) table which I use with the query below to see if no data exists for a given date for all commodities. 

SELECT *
FROM [Calendar] 
WHERE (NOT EXISTS (SELECT ID, CommodityID, Price, DateEntered
                   FROM Spectron_DailyPricing
                   WHERE (CONVERT(date, DateEntered) = Calendar.date))) 
                     AND (date BETWEEN '2015-05-14' AND dateadd(day, -1,GETDATE()))
ORDER BY date asc

I want to be able to run a SQL query that will look through all of the data and show the date and the commodityID that is missing. So for the example above it would show the following.

example result

As my data spans a few years and there is the odd missing row there would be numerous results.

Upvotes: 0

Views: 256

Answers (1)

Sean Pearce
Sean Pearce

Reputation: 1170

WITH
    CTE_Numbers AS (
        SELECT * FROM (VALUES (1),(2),(3),(4)) AS a(CommodityID)),
    CTE_Calendar AS (
        SELECT *
        FROM [Calendar] c
        CROSS JOIN CTE_Numbers n
        WHERE c.date BETWEEN '2015-05-14' AND dateadd(day, -1,GETDATE()))
SELECT *
FROM CTE_Calendar c
LEFT JOIN Spectron_DailyPricing s
    ON c.CommodityID = s.CommodityID
    AND c.date = CONVERT(date, s.DateEntered)
WHERE 
    s.CommodityID IS NULL
ORDER BY c.date asc

Upvotes: 2

Related Questions