两个表之间的 SQL 日期范围比较

Question

我有两个表。一个是带有日期范围的日历表。另一个表有日期。我想选择与日历表比较后没有数据的日期。例如：

    Declare @Calendar table
(
[Month] varchar(5),
CalDate varchar(10)
)
INSERT into @Calendar values ('May', '05/14/2017')
INSERT into @Calendar values ('May', '05/15/2017')
INSERT into @Calendar values ('May', '05/16/2017')
INSERT into @Calendar values ('May', '05/17/2017')
INSERT into @Calendar values ('May', '05/18/2017')
INSERT into @Calendar values ('May', '05/19/2017')
INSERT into @Calendar values ('May', '05/20/2017')
select * from @Calendar
Declare @ReportTable table
(
ReportID int,
ReportName varchar(10),
ReportData int,
ReportDate varchar(10)
)
INSERT into @ReportTable values ('3456', 'MyReport01', 4563, '05/17/2017')
INSERT into @ReportTable values ('3456', 'MyReport01', 6553, '05/18/2017')
INSERT into @ReportTable values ('3456', 'MyReport01', 432, '05/19/2017')
INSERT into @ReportTable values ('3456', 'MyReport01', 123, '05/20/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 9088, '05/14/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 4545, '05/15/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 144, '05/16/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 676, '05/17/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 4454, '05/18/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 2324, '05/19/2017')
INSERT into @ReportTable values ('7888', 'MyReport02', 68467, '05/20/2017')
select * from @ReportTable  

如果任何 ReportID 中缺少@ReportTable日期，我想返回 0。如您所见，对于 ReportID 3456，缺少日期。请指导如何执行此操作。

Answer 1

这里的关键是到达每个报表 - 日期组合有一行的位置。 你可以通过这样做来获得它：

SELECT ReportTypes.ReportID, ReportTypes.ReportName, c.CalDate
FROM @Calendar c
    CROSS JOIN (SELECT DISTINCT ReportID, ReportName 
                FROM @ReportTable) as ReportTypes
ORDER BY ReportTypes.ReportID, ReportTypes.ReportName, c.CalDate

结果：

ReportID    ReportName  CalDate
3456        MyReport01  05/14/2017
3456        MyReport01  05/15/2017
3456        MyReport01  05/16/2017
3456        MyReport01  05/17/2017
3456        MyReport01  05/18/2017
3456        MyReport01  05/19/2017
3456        MyReport01  05/20/2017
7888        MyReport02  05/14/2017
7888        MyReport02  05/15/2017
7888        MyReport02  05/16/2017
7888        MyReport02  05/17/2017
7888        MyReport02  05/18/2017
7888        MyReport02  05/19/2017
7888        MyReport02  05/20/2017

然后，您只需添加数据：

SELECT ReportTypes.ReportID, ReportTypes.ReportName, 
    ISNULL(r2.ReportData, 0) AS ReportData, C.CalDate
FROM @Calendar c
    INNER JOIN (SELECT DISTINCT ReportID, ReportName 
                FROM @ReportTable) as ReportTypes
         ON 1 = 1
    LEFT JOIN @ReportTable r2
        ON ReportTypes.ReportID = r2.ReportID 
            AND c.CalDate = r2.ReportDate
ORDER BY ReportTypes.ReportID, ReportTypes.ReportName, c.CalDate AS ReportDate

您必须使用 CalDate，因为 ReportDate 有时为空。

这将给出以下结果：

ReportID    ReportName  ReportData  ReportDate
3456        MyReport01  0           05/14/2017
3456        MyReport01  0           05/15/2017
3456        MyReport01  0           05/16/2017
3456        MyReport01  4563        05/17/2017
3456        MyReport01  6553        05/18/2017
3456        MyReport01  432         05/19/2017
3456        MyReport01  123         05/20/2017
7888        MyReport02  9088        05/14/2017
7888        MyReport02  4545        05/15/2017
7888        MyReport02  144         05/16/2017
7888        MyReport02  676         05/17/2017
7888        MyReport02  4454        05/18/2017
7888        MyReport02  2324        05/19/2017
7888        MyReport02  68467       05/20/2017