Create multiple rows with date info (Month, Year, etc.) based on date columns-CodePudding

I don't know if that is possible to accomplish, but I want to create timeline of rows for each unique AuthId based on 2 columns (AuthStartDate and AuthEndDate).

declare @authorization table
(AuthId INT, AuthStartDate DATE, AuthEndDate DATE);

INSERT INTO @authorization
VALUES
(123, '2021-12-19', '2022-03-17')

SELECT * FROM @authorization

What I want to get:

AuthId	AuthStartDate	AuthEndDate	MonthStartDate	MonthEndDate	Month	Year
123	2021-12-19	2022-03-17	2021-12-19	2021-12-31	December	2021
123	2021-12-19	2022-03-17	2022-01-01	2022-01-31	January	2022
123	2021-12-19	2022-03-17	2022-02-01	2022-02-28	February	2022
123	2021-12-19	2022-03-17	2022-03-01	2022-03-17	March	2022

I would share my code, but I have no idea even how to start.

CodePudding user response：

You can try to use recursive to make a result for each month from a date range before self-join, because you need to get original data from your expected result.

then use DATEADD with arithmetic to get the first day of months, there is a trick we need to use CASE WHEN or IIF compare.

DATENAME function help us get the month of name easier.
EOMONTH function help us get the end day of months.

the query look like below

;WITH CTE AS (
  SELECT AuthId,AuthStartDate,DATEADD(month,1,AuthEndDate) AuthEndDate
  FROM @authorization
  UNION ALL
  SELECT AuthId,DATEADD(month,1,AuthStartDate),AuthEndDate
  FROM CTE 
  WHERE DATEADD(month,1,AuthStartDate) <= AuthEndDate
)
SELECT a1.*,
       IIF(DATEADD(m, DATEDIFF(m, 0, c.AuthStartDate), 0) < a1.AuthStartDate,
          a1.AuthStartDate,DATEADD(m, DATEDIFF(m, 0, c.AuthStartDate), 0)) MonthStartDate,
       EOMONTH(c.AuthStartDate) MonthEndDate,
       DATENAME(month,c.AuthStartDate) Month,
       year(c.AuthStartDate) year
FROM CTE c
INNER JOIN @authorization a1
ON c.AuthId = a1.AuthId

sqlfiddle

CodePudding user response：

Use a recursive CTE to generate the records month by month and some date functions like EOMONTH and DATEADD to manipulate the dates.

declare @authorization table
(AuthId INT, AuthStartDate DATE, AuthEndDate DATE);

INSERT INTO @authorization
VALUES
(123, '2021-12-19', '2022-03-17'),
(987, '2022-02-01', '2022-04-05');

;WITH date_gen AS 
(
   SELECT AuthId, AuthStartDate, AuthEndDate
        , AuthStartDate AS MonthStartDate
     FROM @authorization
   
   UNION ALL
   
   SELECT AuthId, AuthStartDate, AuthEndDate
        , Next.MonthStartDate
     FROM date_gen
    CROSS
    APPLY (SELECT DATEADD(DAY, 1, EOMONTH(date_gen.MonthStartDate)) AS MonthStartDate) AS Next
    WHERE Next.MonthStartDate < AuthEndDate

)
SELECT *
     , EOMONTH(MonthStartDate) AS MonthEndDate
     , DATENAME(MONTH, MonthStartDate) AS MonthName
     , YEAR(MonthStartDate) AS Year
  FROM date_gen
 ORDER BY AuthId, MonthStartDate

Working demo on dbfiddle