SQL 查找丢失的日期范围 [英] SQL Find missing date ranges

问题描述

我有一张表，可以保存一年中的所有天/月

I have a table that holds all the days/months of the year

例如

Day      Month
1         9
2         9
3         9
4         9
5         9
6         9
7         9
...       ...

我有一个表格显示了来自不同数据集的日期范围

I have a table which shows the date ranges from different datasets

例如

 DataSet    DateRange
webshop 2013-09-20
webshop 2013-09-21
webshop 2013-09-22
webshop 2013-09-23
webshop 2013-09-24
webshop 2013-09-25
webshop 2013-09-26
webshop 2013-09-27
webshop 2013-09-28
webshop 2013-09-29
webshop 2013-09-30

我将如何比较这两个表以显示该特定月份的 DataSet 中缺少哪些天

How would I compare the two tables to show which days are missing from the DataSet for that particular month

E.G.对于我上面的示例，其中数据集是 webshop，它缺少日期范围 01/09/2013 - 19/09/2013

E.G. for my example above where the dataset is webshop it is missing the date range 01/09/2013 - 19/09/2013

感谢您的帮助！

推荐答案

您可以使用 CTE 并将查询编写为:

You can use CTE and write a query as:

declare @StartDate DATE, @EndDate DATE
set @StartDate = '2013-09-01';
set @EndDate = '2013-09-30';

  WITH DateRange(Date) AS
     (
         SELECT
             @StartDate Date
         UNION ALL
         SELECT
             DATEADD(day, 1, Date) Date
         FROM
             DateRange
         WHERE
             Date < @EndDate
     )

     SELECT 'webshop',Date 
     FROM DateRange
     EXCEPT 
     SELECT DataSet,DateRange
     FROM ImportedDateRange
     WHERE DataSet='webshop'
     --You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
     OPTION (MaxRecursion 10000);

这篇关于SQL 查找丢失的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！