CODEWITHSUNDEEP
sqlsql-server
Yara1994
Yara1994

Reputation: 391

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

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.

Upvotes: 0

Views: 577

Answers (2)

MarcinJ
MarcinJ

Reputation: 3639

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 GenerateMonths AS 
(
   SELECT AuthId, AuthStartDate, AuthEndDate
        , AuthStartDate AS MonthStartDate
     FROM @authorization
   
   UNION ALL
   
   SELECT AuthId, AuthStartDate, AuthEndDate
        , Next.MonthStartDate
     FROM GenerateMonths
    CROSS
    APPLY (SELECT DATEADD(DAY, 1, EOMONTH(GenerateMonths.MonthStartDate)) AS MonthStartDate) AS Next
    WHERE Next.MonthStartDate < AuthEndDate

)
SELECT *
     , IIF(AuthEndDate < EOMONTH(MonthStartDate), AuthEndDate, EOMONTH(MonthStartDate)) AS MonthEndDate
     , DATENAME(MONTH, MonthStartDate) AS MonthName
     , YEAR(MonthStartDate) AS Year
  FROM GenerateMonths
 ORDER BY AuthId, MonthStartDate

Working demo on dbfiddle

Upvotes: 2

D-Shih
D-Shih

Reputation: 46229

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 looks 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,
       IIF(EOMONTH(c.AuthStartDate) < a1.AuthEndDate,
          EOMONTH(c.AuthStartDate), a1.AuthEndDate) MonthEndDate,
       DATENAME(month,c.AuthStartDate) Month,
       year(c.AuthStartDate) year
FROM CTE c
INNER JOIN @authorization a1
ON c.AuthId = a1.AuthId

sqlfiddle

Upvotes: 1

Related Questions