SQL - creating a list of custom dates between two dates

Question

I am having trouble compiling a query than can do the following:

I have a table which has a startDate and endDate [tblPayments]
I have a column which stores a specific paymentDay [tblPayments]

Data

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

I am trying to generate a SELECT query which will split this specific table into separate lines based on the amount of months between these two dates, and set the paymentDay as the day for these queries

Example Output

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

I have found a query which will select the months between these dates but its adapting it to my table above, and multiple startDates and endDates I am struggling with

spliting the months

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

This query is limited to just one startDate and endDate, so I haven't expanded it to change the DAY of the date.

Eric · Accepted Answer · 2015-12-14 10:59:40Z

2

Use a date table and a simple inner join

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)

answered Dec 14, 2015 at 10:59

Eric

5,74319 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crezzer7 Over a year ago

thankyou, this works also, alls i did was swap the INSERT code with a select statement and this fetched the whole table values

connectedsoftware · Accepted Answer · 2015-12-14 11:02:56Z

1

I would create an in memory calendar table and then perform a simple query by joining to that:

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

edited Dec 14, 2015 at 11:02

answered Dec 14, 2015 at 10:56

connectedsoftware

7,0973 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL - creating a list of custom dates between two dates

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related