Query to display missing records in a time period

[JohnCM]

All,

I have a database that pulls down information from a website. There should be one record per day per customer. Due to some reason a customer may not have a record for a day or multiple days while the other customers have a record.

I need to create a query to show me these "Holes" where a customer should have a record but does not. I want to display any customers who are missing a record and what day(s) is missing. The customer list is dynamic with customers leaving and starting at different times. I have a separate table to indicate which customers are currently active.

Ideas?

I feel that I can do an unmatched item query if I had a table that had each day in the period. Without making a table with just dates in it how can I accomplish this in SQL or query design?

Also, would this approach work to show the customer and the day missing or is a loop of some kind needed to evaluate each customer for missing records?

If this is not the right approach to this please suggest another way.

Thanks,

John Martinez

 

[bs0d]

It should work. Post your query.

SELECT tblDailyReadings.entityKey, tblDailyReadings.ReadingDate, tblDailyReadings.myValue
FROM tblReadingDate LEFT JOIN tblDailyReadings ON tblReadingDate.ReadingDate = tblDailyReadings.ReadingDate
WHERE (((tblDailyReadings.entityKey)=175653));

 

[bs0d]

Here's my approach on filling missing dates via loop method -- but it seems to add a 3-4 second lag due to the looping:

Dim ws As Worksheet
Set ws = Worksheets("Data")

'Get Last Row
Dim FinalRow As Long
FinalRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Start from bottom, loop to first row
For i = FinalRow To 3 Step -1
    curcell = ws.Cells(i, 1).Value
    prevcell = ws.Cells(i - 1, 1).Value
    
    'Nested loop to add missing dates within date range:
    Do Until curcell - 1 = prevcell Or curcell = prevcell
        ws.Rows(i).Insert xlShiftDown
        
        'Add missing date to cell of new row:
        curcell = ws.Cells(i + 1, 1) - 1
        ws.Cells(i, 1).Value = curcell
        ws.Cells(i, 12).Value = "Date Missing"
    Loop
Next i

 

[SydneyGeek]

Try:

SELECT tblReadingDate.ReadingDate, tblDailyReadings.entityKey, tblDailyReadings.myValue
FROM tblReadingDate LEFT JOIN tblDailyReadings ON tblReadingDate.ReadingDate = tblDailyReadings.ReadingDate
WHERE (((tblDailyReadings.entityKey)=175653));

Note 2 differences above.
1. The date comes from the main date table, not the data table with the gaps
2. I've moved the data to be the first field. No special reason, just a preference, but it makes it easier to see the date order

Denis

 

[xenou]

What's entity key supposed to do here? That's looks suspicious. The where clause might be effectively filtering out the missing dates and defeating the purpose of the left join because (by definition) there's no entity key equaling 175653 for any missing dates.

 

[xenou]

Here's another take.

ReadingDate has to come from the Left Side of the join.

We can't use a Where criteria on the query in such a way that it is applied to the results of the left join, because that puts you back where you started - no matches on the missing dates because they don't have records with that key.

So the solution is to apply the where to the tblDailyReadings table FIRST, then do the left join stuff:

SELECT 
	b.entityKey, 
	a.ReadingDate, 
	b.myValue
FROM 
	tblReadingDate a
	LEFT JOIN 
	(
		select entityKey, ReadingDate, myValue 
		from tblDailyReadings 
		where tblDailyReadings.entityKey = 175653
	) b 
	ON a.ReadingDate = b.ReadingDate
WHERE
	a.ReadingDate >= (select min(ReadingDate) from tblDailyReadings where entityKey = 175653)
	and
	a.ReadingDate <= (select max(ReadingDate) from tblDailyReadings where entityKey = 175653)

------------------------------------------------------------------------------------------------------------


In practice, it may work better to create the subquery as a separate actual query:

QUERY 20:

SELECT 
	tblDailyReadings.ReadingDate, 
	tblDailyReadings.entityKey, 
	tblDailyReadings.myValue
FROM 
	tblDailyReadings
WHERE 
	tblDailyReadings.entityKey=175653;

QUERY 21:

SELECT 
	b.entityKey, 
	a.ReadingDate, 
	b.myValue
FROM 
	tblReadingDate a
	LEFT JOIN 
	(
		SELECT ReadingDate, entityKey, myValue FROM Query20 
	) b 
	ON a.ReadingDate = b.ReadingDate
WHERE
	a.ReadingDate >= (select min(ReadingDate) from Query20)
	and
	a.ReadingDate <= (select max(ReadingDate) from Query20)

 

[bs0d]

Here's another take.

ReadingDate has to come from the Left Side of the join.

We can't use a Where criteria on the query in such a way that it is applied to the results of the left join, because that puts you back where you started - no matches on the missing dates because they don't have records with that key.

So the solution is to apply the where to the tblDailyReadings table FIRST, then do the left join stuff:

In practice, it may work better to create the subquery as a separate actual query:

I think you've figured it out. I tested your first piece of code and it worked. It returns exactly what I'm after. Thanks for the help. Better yet, it seems to work very fast- so this also appears to be the most optimal solution vs. going back and looping through the data in VBA.

The subquery (query 20) in your example, that's basically what I have used up to this point, which would return the data with missing dates. So I think the magic happens (so to speak) in query 21 of your example.