Derive multiple rows for month based on Start and end time

Question

Derive multiple rows for month based on Start and end time

Prusty, Parthasarathi (Cognizant) 41

I have a table where I have the start time and end time as given in below example.

DECLARE @TableTT AS TABLE
    (
  Id INT ,
  StartDate datetime ,
  EndDate datetime
)

INSERT  INTO @TableTT
    ( Id, StartDate, EndDate)
VALUES  ( 1, N'2023-01-19 15:23:09.960', '2023-05-14 15:23:09.960'),
    ( 2, N'2021-03-19 15:23:09.960', '2021-08-14 15:23:09.960')

User's image

I'll have only 3 columns ID, year and Month. I have to split the rows based on the Months. For exmple. I need output as below. Year would be derived from Startdate and Month will be calculated by subtracting startdate from enddate and that many rows for month should appear.

User's image

Accepted answer

0 additional answers

Your answer

Answer 1

Jingyang Li 5,896 Volunteer Moderator

DECLARE @TableTT AS TABLE
    (
  Id INT ,
  StartDate datetime ,
  EndDate datetime
)

INSERT  INTO @TableTT
    ( Id, StartDate, EndDate)
VALUES  ( 1, N'2023-01-19 15:23:09.960', '2023-05-14 15:23:09.960'),
    ( 2, N'2021-03-19 15:23:09.960', '2021-08-14 15:23:09.960')


	select Id,YEAR(StartDate),MONTH(StartDate)+n-1 
	from @TableTT
	cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
	where Eomonth(dateadd(month,n-1,StartDate))<=Eomonth(EndDate)

Prusty, Parthasarathi (Cognizant) 41

I found another way of doing it.

CREATE Table #Test_Table
    (
  Id INT ,
  StartDate datetime ,
  EndDate datetime
)

INSERT  INTO #Test_Table
    ( Id, StartDate, EndDate)
VALUES  ( 1, N'2023-01-19 15:23:09.960', '2023-05-14 15:23:09.960'),
    ( 2, N'2021-03-19 15:23:09.960', '2021-08-14 15:23:09.960')

  with nums as (
      select 0 as n
      union all
      select n + 1 as n
      from nums
      where n < 11
     )

	 select Id, StartDate, EndDate,
       year(dateadd(month, n.n, StartDate)) as yr,
       month(dateadd(month, n.n, StartDate)) as mon
from #Test_Table t join
     nums n
     on dateadd(month, n.n - 1, StartDate) <= EndDate
	 order by ID,yr,mon;

Jingyang Li 5,896 Volunteer Moderator

select Id,YEAR(StartDate),MONTH(StartDate)+n-1 
	from @TableTT
	--cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
	--SQL Server 2022
	cross apply (SELECT value as n FROM GENERATE_SERIES(1, 10)) d
	where Eomonth(dateadd(month,n-1,StartDate))<=Eomonth(EndDate)

Share via

Derive multiple rows for month based on Start and end time

0 additional answers

Your answer