sql server displaying missing dates

Question

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

Answer 1

I would recommend you use a table valued function to get you all days in between 2 selected dates as a table (Try it out in this fiddle):

CREATE FUNCTION dbo.GetAllDaysInBetween(@FirstDay DATETIME, @LastDay DATETIME)
RETURNS @retDays TABLE 
(
    DayInBetween DATETIME
)
AS 
BEGIN
    DECLARE @currentDay DATETIME
    SELECT @currentDay = @FirstDay

    WHILE @currentDay <= @LastDay
    BEGIN

        INSERT @retDays (DayInBetween)
            SELECT @currentDay

        SELECT @currentDay = DATEADD(DAY, 1, @currentDay)
    END 

    RETURN
END

(I include a simple table setup for easy copypaste-tests)

CREATE TABLE SiteVisit (ID INT PRIMARY KEY IDENTITY(1,1), visitDate DATETIME, visitSite NVARCHAR(512))

INSERT INTO SiteVisit (visitDate, visitSite)
    SELECT '2014-03-11', 'site1'
    UNION
    SELECT '2014-03-12', 'site1'
    UNION
    SELECT '2014-03-15', 'site1'
    UNION
    SELECT '2014-03-18', 'site1'
    UNION
    SELECT '2014-03-18', 'site2'

now you can simply check what days no visit occured when you know the "boundary days" such as this:

SELECT
        DayInBetween AS missingDate,
        'site1' AS visitSite
    FROM dbo.GetAllDaysInBetween('2014-03-11', '2014-03-18') AS AllDaysInBetween
    WHERE NOT EXISTS 
        (SELECT ID FROM SiteVisit WHERE visitDate = AllDaysInBetween.DayInBetween AND visitSite = 'site1')

Or if you like to know all days where any site was not visited you could use this query:

SELECT
        DayInBetween AS missingDate,
        Sites.visitSite
    FROM dbo.GetAllDaysInBetween('2014-03-11', '2014-03-18') AS AllDaysInBetween
    CROSS JOIN (SELECT DISTINCT visitSite FROM SiteVisit) AS Sites
    WHERE NOT EXISTS
        (SELECT ID FROM SiteVisit WHERE visitDate = AllDaysInBetween.DayInBetween AND visitSite = Sites.visitSite)
    ORDER BY visitSite

Just on a side note: it seems you have some duplication in your table (not normalized) siteName should really go into a separate table and only be referenced from SiteVisit