I'm trying to come up with a way to audit a sales table to determine if there are missing records on any days by client. Most of the sample queries I looked at don't work once you try to find gaps in the data by client. Below are 3 sample tables and when I run the query at the end, I get back
but Client 200 didn't send any data on 2015-07-02 so that should be on the list, but because client 300 did, it doesn't show up as an issue.
The other problem I have is since I'm looking for clients with missing data and there's no record for that day, how do I return the client ID's for the gaps that are missing? I can return the missing days, but I want the associated client so I know who to get a hold of.
Essentially I'd love to return something this and show each client for each day (commas are just for show)
DECLARE @Calendar TABLE(CalendarDate date primary key)
INSERT INTO @Calendar
DECLARE @Sales TABLE(ID int identity(1,1) primary key, ClientID integer, SaleDate date)
INSERT INTO @Sales
/(The file frequency on the @ClientInfo table is so I can hopefully add a GETDATE() - Frequency to handle clients who send files monthly vs daily)/
DECLARE @ClientInfo TABLE(ID int identity(100,100) primary key, Name varchar(50), FileFrequency int NOT NULL DEFAULT 0)
INSERT INTO @ClientInfo
from @Calendar C LEFT OUTER JOIN
(SELECT ClientID, SaleDate, COUNT(*) SALECOUNT FROM @Sales
GROUP BY ClientID, SaleDate
) S on C.CalendarDate = S.SaleDate
WHERE S.SALECOUNT IS NULL