Reputation: 3065
Using SQL Server 2012, I have a script that inserts 4 rows of data into a table each day. The data looks like this:
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
:
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.
As my data spans a few years and there is the odd missing row there would be numerous results.
Upvotes: 0
Views: 256
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