question

sergiord2017 avatar image
sergiord2017 asked

transform one record into several records with date

Hello , I have records with an starting date and an ending date. Both can be on the same year or in different years. I have to convert that record in so many records as years between the ending date and the starting date. For example: START DATE END DATE 05/03/2013 05/01/2015 I want to transform it into: START DATE END DATE 05/03/2013 31/12/2013 01/01/2014 31/12/2014 01/01/2015 05/01/2015 Does anyone knows how to transform this record into these? Thanks in advance ,
sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
See if the following helps declare @table table ( StartDate date, EndDate date ) insert @table select '20130305', '20150105' select *, CASE WHEN CalculatedYear = StartDate --KEEP THE START DATE AS IT IS THEN StartDate ELSE DATENAME(YEAR, CalculatedYear) + '0101' --INITIALIZE WITH START OF YEAR END CalculatedStartDate, CASE WHEN DATEPART(YEAR, CalculatedYear) = DATEPART(YEAR, EndDate) THEN EndDate --KEEP THE END DATE ELSE DATENAME(YEAR, CalculatedYear) + '1231' --LAST DAY OF YEAR END CalculatedEndDate from ( select *, DATEADD(YEAR, NumberOfYear, StartDate) CalculatedYear from @table cross apply ( -- select TOP(DATEDIFF(YEAR, StartDate, EndDate) + 1) ROW_NUMBER() over(order by (select null)) - 1 NumberOfYear from sys.columns c cross join sys.columns c1 )YD )a
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much, it works perfect!!!!!
0 Likes 0 ·
@sergiord2017 Since it does, please tick the "Accept" icon located in the same area with the "current number of votes" group (top left of the answer), so that someone else with the similar question in the future has a better chance of finding existing solution. Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.