Query giving consecutive dates for following weeks

Question

Is it possible to create a table in SQL, in which 1 column gives the consecutive Sundays. The other column has the upcoming 7 sundays corresponding to each sunday on column1. Expected output below:

Any help is extremely appreciated.

Answer 1

Try the following:

select date_Sundays,
       date_add(date_Sundays,
                interval 7*row_number() over (partition by date_sundays order by date_sundays) day)
       as nextSundays 
from tbl

See a demo.

Answer 2

You can use a function to generate the list of Sundays and then join to itself to get the future 7 Sundays. When calling the second function in the JOIN, make sure the end date is far enough in the future to encompass the future 7 weeks.

--Function to generate a list of Sundays using a number table.
CREATE FUNCTION fun_GetSundaysList 
(   
    --Need to know the date range for generating these dates.
    @StartDate date
    , @EndDate date
)
RETURNS TABLE 
AS
RETURN 
(
    --Using a numbers table to generate a list of dates.
    --Concept borrowed from this post:  https://stackoverflow.com/a/17529962/2452207
    SELECT DATEADD(DAY,number+1,@StartDate) [Date]
    FROM master..spt_values
    WHERE type = 'P'
        AND DATEADD(DAY,number+1,@StartDate) < @EndDate
        AND DATEPART(WEEKDAY, DATEADD(DAY,number+1,@StartDate)) = 1  --Narrow list to only Sundays.
)
GO

--Select the list of Sundays and JOIN to the same list.
SELECT
    s.[Date] as main_sunday
    , s1.[Date] as future7_sundays
FROM fun_GetSundaysList ('2022-10-1', '2023-1-1') as s
    JOIN fun_GetSundaysList ('2022-10-1', '2023-3-1') as s1
        ON s1.[Date] > s.[Date]
        AND s1.[Date] < DATEADD(week,8,s.[Date])
ORDER BY s.[Date], s1.[Date]

Sample of list Generated:

Edit: Looking again, I don't like using the master..spt_values for generating the list of dates. The reasoning is that it is an undocumented table, and it only gives you up to 2048 values to use. It is fast. Getting 2048 values from spt_values takes 2ms where generating 10k values using the below joins takes 187ms on my server. Either way, you need to generate a sequence of numbers to help create the dates so here's another way to build the numbers list in the function:

ALTER FUNCTION fun_GetSundaysList 
(   
    --Need to know the date range for generating these dates.
    @StartDate date = '10/11/2022'
    , @EndDate date = '1/1/2023'
)
RETURNS TABLE 
AS
RETURN 
(
    WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
    , y as (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as number
        FROM x ones, x tens, x hundreds, x thousands
        --ORDER BY 1
    )
        
    --Using a numbers table.
    SELECT DATEADD(DAY,number+1,@StartDate) [Date]
    FROM y
    WHERE DATEADD(DAY,number+1,@StartDate) < @EndDate
        AND DATEPART(WEEKDAY, DATEADD(DAY,number+1,@StartDate)) = 1
)

Answer 3

If you want the whole shebang, something like this:

-- Create Sundays table
CREATE TABLE Sundays (
    Date_sundays DATE
);

-- Insert a bunch of Sundays
DECLARE @StartDate DATE
DECLARE @EndDate DATE 
SET @StartDate = CAST('2022-03-06' AS DATE)
SET @EndDate = CAST('2023-03-05' AS DATE)

WHILE @StartDate <= @EndDate
    BEGIN
        INSERT INTO Sundays (Date_sundays) 
            SELECT @StartDate
        SET @StartDate = DATEADD(@StartDate, INTERVAL 7 DAY)
    END
;

-- self JOIN to get next Sundays
SELECT
    Sundays.Date_sundays,
    Sundays2.Date_sundays AS Date_sundays_next
FROM
    Sundays 
    JOIN Sundays Sundays2
        ON Sundays2.Date_sundays BETWEEN
            DATEADD(Sundays.Date_sundays, INTERVAL 7 DAY)
            AND DATEADD(Sundays.Date_sundays, INTERVAL 49 DAY)
ORDER BY
    Sundays.Date_sundays,
    Sundays2.Date_sundays
;