Question 113226 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query giving consecutive dates for following weeks

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:

enter image description here

Any help is extremely appreciated.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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:

    enter image description here

    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
)
    Login or Signup to reply.
  3. 0

    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
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search