I'm not fond of the static years in this code. Perhaps it could be converted to a dynamic pivotdeclare @t table (
startdate date,
enddate date
)
declare @n table (
Nbr tinyint
)
;
insert @n values(0),(1),(2),(3),(4)
;
insert @t values
('12/1/2013', '1/10/2017'),
('11/1/2013', '11/10/2016'),
('1/1/2013', '1/31/2014')
;
select StartDate, Enddate, [2013] as [2013], [2014] as [2014], [2015] as [2015], [2016] as [2016], [2017] as [2017]
from (
select
StartDate,
EndDate,
DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate))) AS [Year],
DATEDIFF(
DAY,
case DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate)))
when datepart(year,startdate) then t.startdate
else CAST(DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate))) AS CHAR(4)) + '-01-01'
end,
case DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate)))
when datepart(year,enddate) then t.enddate
else CAST(DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate))) AS CHAR(4)) + '-12-31'
end
) AS DayCnt
from @t t
inner join @n n on n.Nbr < datepart(year,t.enddate) - datepart(year,t.startdate) + 1
) p
pivot (
min(DayCnt)
for [Year] in ([2013], [2014], [2015], [2016], [2017])
) as pvt
order by startdate, enddate