sql server 显示缺失日期

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

有两个网站，每个网站都需要每个月每天的访问条目，因此考虑到今天是 2014 年 3 月 11 日，我们预计有 22 个条目，但只有 14 个条目，因此缺少 8 个条目，有什么办法吗在 sql 中我们可以提取丢失的日期条目

针对网站的截至当月当前日期

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

这是我不成功的尝试，我认为在逻辑和语法上都是错误的

select 
    siteName, visitDate  
from  
    SiteVisit not in (SELECT siteName, visitDate
                      FROM SiteVisit 
                      WHERE Day(visitDate) != Day(CURRENT_TIMESTAMP) 
                        AND Month(visitDate) = Month(CURRENT_TIMESTAMP))

注:以上数据和列是实际表格的简化版本

Answer 1

我建议您使用表值函数来获取两个选定日期之间的所有日期作为表格 (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

(我包括一个简单的表格设置，以便于复制粘贴测试)

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'

现在，当您知道“边界日”时，您可以简单地检查哪些天没有访问，如下所示:

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')

或者，如果您想了解没有访问任何网站的所有日期，您可以使用此查询:

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

顺便说一句:您的表中似乎有一些重复(未标准化)siteName 应该真正进入一个单独的表，并且只能从 SiteVisit 引用