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 ,
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
@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.
18 People are following this question.
