SQL - 在两个日期之间创建自定义日期列表

Question

我在编译一条查询时遇到了一些问题，需要实现以下功能：

1. 我有一个表格，其中包含一个startDate和一个endDate [tblPayments]
2. 我有一个列存储特定的paymentDay [tblPayments]

数据

paymentID        startDate           endDate          paymentDay
    1            2016-01-01         2016-12-31            25
    2            2015-06-01         2016-06-30            16

我正试图生成一个SELECT查询，它将根据这两个日期之间的月数将此特定表拆分为单独的行，并将paymentDay设置为这些查询的日期。 示例输出：

paymentID       expectedDate
    1            2016-01-25   
    1            2016-02-25   
    1            2016-03-25   
    1            2016-04-25   
    1            2016-05-25   
    1            2016-06-25   
    1            2016-07-25   
    1            2016-08-25   
    1            2016-09-25   
    1            2016-10-25   
    1            2016-11-25   
    1            2016-12-25   
    2            2015-06-16   
    2            2015-07-16  
    2            2015-08-16  
    2            2015-09-16  
    2            2015-10-16  
    2            2015-11-16  
    2            2015-12-16  
    2            2016-01-16  
    2            2016-02-16  
    2            2016-03-16  
    2            2016-04-16  
    2            2016-05-16  

我找到了一个查询语句，可以选择这些日期之间的月份，但是我在适应我的上述表格、多个开始日期和结束日期方面遇到了困难。

拆分月份

declare @start DATE = '2015-01-01'
declare @end DATE = '2015-12-31'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(MM,1,date)
    from months
    where DATEADD(MM,1,date)<=@end
)
select Datename(MM,date) from months

这个查询仅限于一个开始日期和结束日期，因此我没有将其扩展以更改日期的天数（DAY）。

- Crezzer7

Answer 1

使用日期表和简单的内连接。

DECLARE @tblPayments table (paymentID int identity(1,1), startDate date, endDate date, paymentDay int)

INSERT @tblPayments VALUES 
  ('2016-01-01', '2016-12-31', 25), 
  ('2015-06-01', '2016-06-30', 16)



;WITH dates AS -- Build date within the range of startDate and endDate
(
    SELECT MIN(startDate) AS Value, MAX(endDate) AS MaxDate FROM @tblPayments
    UNION ALL 
    SELECT DATEADD(DAY, 1, Value), MaxDate 
    FROM dates WHERE DATEADD(DAY, 1, Value) <= MaxDate
)
SELECT pay.paymentID, dates.Value AS expectedDate 
FROM 
    @tblPayments pay 
    INNER JOIN dates ON 
        dates.Value BETWEEN pay.startDate AND pay.endDate 
        AND DAY(dates.Value) = paymentDay
OPTION (maxrecursion 0)

- Eric

---

谢谢，这个也可以，我只是用一个SELECT语句替换了INSERT代码，这样就获取了整个表的值。 - Crezzer7

Answer 2

我会创建一个内存日历表，然后通过连接该表执行简单查询：

-- Create a table with all the dates between the min and max dates in the 
-- data table
DECLARE @Calendar TABLE
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = MIN(startdate), @EndDate = MAX(enddate) FROM YourDataTable

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO @Calendar (CalendarDate)
    SELECT @StartDate
    SET @StartDate = DATEADD(dd, 1, @StartDate)
END

-- Join to return only dates between the start and end date that match the Payment Day
SELECT D.PaymentId, C.CalendarDate FROM YourDataTable D
INNER JOIN @Calendar C ON C.CalendarDate BETWEEN D.StartDate AND D.EndDate
AND DATEPART(day, C.CalendarDate) = D.PaymentDay

- connectedsoftware