SiteVisitID siteName visitDate
------------------------------------------------------
1 site1 01/03/2014
2 Site2 01/03/2014
3 site1 02/03/2014
4 site1 03/03/2014
5 site2 03/03/2014
6 site1 04/03/2014
7 site2 04/03/2014
8 site2 05/03/2014
9 site1 06/03/2014
10 site2 06/03/2014
11 site1 08/03/2014
12 site2 08/03/2014
13 site1 09/03/2014
14 site2 10/03/2014
There are two sites and each need to have a visit entry for everyday of the month, so considering that today is 11/03/2014 we are expecting 22 entries but there are only 14 entries so missing 8, is there any way in sql we could pull out missing date entries
Up to the current day of the month against sites
siteName missingDate
-----------------------
site2 02/03/2014
site1 05/03/2014
site1 07/03/2014
site2 07/03/2014
site2 09/03/2014
site1 10/03/2014
site1 11/03/2014
site2 11/03/2014
Here is my unsuccessful attempt I believe is wrong both logically and syntactically
select
siteName, visitDate
from
SiteVisit not in (SELECT siteName, visitDate
FROM SiteVisit
WHERE Day(visitDate) != Day(CURRENT_TIMESTAMP)
AND Month(visitDate) = Month(CURRENT_TIMESTAMP))
Note: the above data and columns are simplified version of the actual table
See Question&Answers more detail:os