T-SQL Generate sequence of days after certain date

Question

I have a sequence of days as an array like the following:

DECLARE @days VARCHAR(MAX) = N'Monday, Wednesday, Friday'

And a n amount of occurrences like:

DECLARE @occurrences INT = 10

In this way I can generate a table using the following query and a custom table called Numbers which contains 1,000,000 numbers:

SELECT TOP @occurrences
    Item
FROM 
    dbo.SplitStrings_Numbers(@days, ',')
CROSS JOIN Numbers
ORDER BY n

The result is the following:

|----------------------|
| 1 | Monday           |
|----------------------|
| 2 | Wednesday        |
|----------------------|
| 3 | Friday           |
|----------------------|
| 4 | Monday           |
|----------------------|
| 5 | Wednesday        |
|----------------------|
| 6 | Friday           |
|----------------------|

Now what I need to complete this function is to start the sequence with the first day that occur based on a specified date. So, when I declare start from 1st of September

DECLARE @start DATETIME = '2015-09-01'

The result should look like the following:

|------------------------------------|
| 1 | Wednesday      | 2015-09-02    |
|------------------------------------|
| 2 | Friday         | 2015-09-04    |
|------------------------------------|
| 3 | Monday         | 2015-09-07    |
|------------------------------------|
| 4 | Wednesday      | 2015-09-09    |

What I cannot calculate is this pseudo-code: "Given an array of days and a starting date, get the first occurrence after that date".

Zohar Peled · Accepted Answer · 2015-08-08 14:02:32Z

1

This is not a complete answer but it will get you started:

;With DatesCte AS
(
SELECT TOP(@occurrences) DATEADD(DAY, number-1, @Start) As TheDate
FROM Numbers
), DatesWithWeekDay As
(
SELECT TheDate, DATENAME(WEEKDAY, TheDate) As WeekDayName
FROM DatesCTE
)
SELECT *
FROM DatesWithWeekDay

answered Aug 8, 2015 at 14:02

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

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

Collectives™ on Stack Overflow

T-SQL Generate sequence of days after certain date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related