sql-server - 选择两个日期之间缺失的月份(同一年)

Question

我试图获取同一年没有交易的所有月份(如果不同的年份是不可能的)

这是我的查询，用于获取 2 个日期之间的交易，但不知道如何只选择数据库中缺少交易的月份

SELECT * 
FROM Installment 
WHERE OrderId = 1 
      AND InstallmentDate 
            BETWEEN cast('8/02/2014' as date) 
                AND cast('12/25/2014' as date)

InstallmentId   OrderId CustomerKey InstallmentAmount   InstallmentDate
    18             1      INS-1          3000           2014-09-03
    92             1      INS-1          3000           2014-10-13
   137             1      INS-1          3000           2014-11-05

在这种情况下，第 12 个月和第 8 个月的记录丢失了，我如何使用 SQL Server 查询获取此记录？

更新

select yymm.yy, yymm.mm
from (select distinct year(InstallmentDate) as yy, month(InstallmentDate) as mm
  from Installment
  where InstallmentDate BETWEEN '2014-09-02' and '2015-01-15'
 ) yymm left join
 Installment i
 on i.OrderId = 1 and
    year(i.InstallmentDate) = yymm.yy and
    month(i.InstallmentDate) = yymm.mm
where i.OrderId is not null;

Gordon 的查询返回表中 2 个日期之间的所有年份和月份，只需将 i.OrderId is null 更改为 i.OrderId is not null 这里是在他的查询之外

 yy      mm
2014     9
2014     10
2014     11

预期输出(如果可能)

 yy      mm
2014     12
2015     1

Answer 1

使用以下递归 CTE:

DECLARE @start DATE = '2014-09-02'
DECLARE @end DATE = '2015-01-15'
;WITH IntervalDates (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(MONTH, 1, date)
    FROM IntervalDates
    WHERE DATEADD(MONTH, 1, date)<=@end
)
SELECT YEAR(date) AS Year, MONTH(date) AS Month
FROM IntervalDates

您可以获得感兴趣的两个日期之间所有年/月的列表:

Year    Month
==============
2014    9
2014    10
2014    11
2014    12
2015    1

在上述 CTE 上使用 EXCEPT:

;WITH IntervalDates (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(MONTH, 1, date)
    FROM IntervalDates
    WHERE DATEADD(MONTH, 1, date)<=@end
)
SELECT YEAR(date) AS Year, MONTH(date) AS Month
FROM IntervalDates

EXCEPT

SELECT DISTINCT YEAR(InstallmentDate) AS yy, MONTH(InstallmentDate) AS mm
FROM Installment
WHERE  OrderId = 1 AND InstallmentDate BETWEEN @start AND @end 

产生所需的结果集:

Year    Month
=============
2014    12
2015    1