CODEWITHSUNDEEP
sqlsql-serverdate
BaronG
BaronG

Reputation: 80

Return all months between this month and specified month

I have a table which includes the end dates for many projects, I want to return the dates of all the months between today (current month) and that end date.

The day of the month it returns is irrelevant, so it can simply be the end date minus one month at a time until this month.

Example table:

Project End Date
Proj_A 2023-03-20
Proj_B 2023-01-20

Desired Output:

Project End Date Preceding Dates
Proj_A 2023-03-20 2022-12-20
Proj_A 2023-03-20 2023-01-20
Proj_A 2023-03-20 2023-02-20
Proj_A 2023-03-20 2023-03-20
Proj_B 2023-01-20 2022-12-20
Proj_B 2023-01-20 2023-01-20

Upvotes: 0

Views: 74

Answers (1)

gotqn
gotqn

Reputation: 43636

One simple example:

DECLARE @DataSource TABLE
(
    [Project] VARCHAR(128)
   ,[End Date] DATE
);

INSERT INTO @DataSource ([Project], [End Date])
VALUES ('Proj_A', '2023-03-20')
      ,('Proj_B', '2023-01-20');

WITH DataSource ([month]) AS 
(
    SELECT [month]
    FROM
    (
        VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
    ) DS ([month])
)
SELECT DS.[Project]
      ,DS.[End Date]
      ,DATEADD(MONTH, -M.[month], [End Date]) AS [Preceding Dates]
FROM @DataSource DS
CROSS APPLY DataSource M
WHERE SYSDATETIME() < DATEADD(MONTH, -M.[month], [End Date]);

enter image description here

Of course, you need to test and tune depending on your real data.

Upvotes: 1

Related Questions