SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get difference between dates year by year


Get difference between dates year by year

Author
Message
lein
lein
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 153
Get difference between dates year by year
I need to get the days difference between Start date and End Date year by year for the past five years:
My date has always 5 years max interval:
Sample data
Start Date End Date
12/1/2013 1/10/2017
11/1/2013 11/10/2016
1/1/2013 1/31/2014
And here is my desired output (sample data for the past five years)
Start Date End Date Number of days per year
2013 2014 2015 2016 2017
12/1/2013 1/10/2017 30 365 365 366 10
11/1/2013 11/10/2016 60 365 365 315
1/1/2013 1/31/2014 364 31

For this year i have 2013-2017
for next year it will be 2014-2018 etc... Needs to be dynamic
Getting the days difference between two dates is simple here is what I have right now:
select start_date, end_date,
DATEDIFF(DAY, start_date, end_date) as difference_in_days
from table1

Thank you in advance Smile
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)

Group: General Forum Members
Points: 131911 Visits: 19201
Here's one way of doing it:

WITH Dates (StartDate, EndDate) AS (
SELECT '20131201', '20170110' UNION ALL
SELECT '20131101', '20161110' UNION ALL
SELECT '20130101', '20140131'
)
SELECT
StartDate
, EndDate
, CASE
WHEN StartDate >= '20131231' THEN 0
WHEN EndDate < '20130101' THEN 0
WHEN StartDate >= '20130101' AND EndDate < '20131231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,StartDate,'20131231')
WHEN StartDate < '20130101' AND EndDate <= '20131231' THEN DATEDIFF(day,'20130101',EndDate) + 1
WHEN StartDate < '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,'20130101','20131231') + 1
END AS Daysin2013
, CASE
WHEN StartDate >= '20141231' THEN 0
WHEN EndDate < '20140101' THEN 0
WHEN StartDate >= '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,StartDate,'20141231')
WHEN StartDate < '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,'20140101',EndDate) + 1
WHEN StartDate < '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,'20140101','20141231') + 1
END AS Daysin2014
, CASE
WHEN StartDate >= '20151231' THEN 0
WHEN EndDate < '20150101' THEN 0
WHEN StartDate >= '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,StartDate,'20151231')
WHEN StartDate < '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,'20150101',EndDate) + 1
WHEN StartDate < '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,'20150101','20151231') + 1
END AS Daysin2015
, CASE
WHEN StartDate >= '20161231' THEN 0
WHEN EndDate < '20160101' THEN 0
WHEN StartDate >= '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,StartDate,'20161231')
WHEN StartDate < '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,'20160101',EndDate) + 1
WHEN StartDate < '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,'20160101','20161231') + 1
END AS Daysin2016
, CASE
WHEN StartDate >= '20171231' THEN 0
WHEN EndDate < '20170101' THEN 0
WHEN StartDate >= '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,StartDate,'20171231')
WHEN StartDate < '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,'20170101',EndDate) + 1
WHEN StartDate < '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,'20170101','20171231') + 1
END AS Daysin2017
FROM Dates d;


John

ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84109 Visits: 9507
Edit: Sorry about the formatting. The site automatically "adjusts" (corrupts!) multiple spaces into fewer spaces.


SELECT start_date, end_date,
CASE WHEN start_date >= '20140101' OR end_date < '20130101' THEN 0
ELSE DATEDIFF(DAY, CASE WHEN start_date < '20130101' THEN '20130101' ELSE start_date END,
CASE WHEN end_date >= '20140101' THEN '20131231' ELSE end_date END) + 1 END AS [2013],
CASE WHEN start_date >= '20150101' OR end_date < '20140101' THEN 0
ELSE DATEDIFF(DAY, CASE WHEN start_date < '20140101' THEN '20140101' ELSE start_date END,
CASE WHEN end_date >= '20150101' THEN '20141231' ELSE end_date END) + 1 END AS [2014],
CASE WHEN start_date >= '20160101' OR end_date < '20150101' THEN 0
ELSE DATEDIFF(DAY, CASE WHEN start_date < '20150101' THEN '20150101' ELSE start_date END,
CASE WHEN end_date >= '20160101' THEN '20151231' ELSE end_date END) + 1 END AS [2015],
CASE WHEN start_date >= '20170101' OR end_date < '20160101' THEN 0
ELSE DATEDIFF(DAY, CASE WHEN start_date < '20160101' THEN '20160101' ELSE start_date END,
CASE WHEN end_date >= '20170101' THEN '20161231' ELSE end_date END) + 1 END AS [2016],
CASE WHEN start_date >= '20180101' OR end_date < '20170101' THEN 0
ELSE DATEDIFF(DAY, CASE WHEN start_date < '20170101' THEN '20170101' ELSE start_date END,
CASE WHEN end_date >= '20180101' THEN '20171231' ELSE end_date END) + 1 END AS [2017]
FROM ( VALUES
(CAST('12/1/2013' AS date), CAST('1/10/2017' AS date)),
('11/1/2013', '11/10/2016'),
('1/1/2013','1/31/2014') ) AS test_data(start_date, end_date)



SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
lein
lein
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 153
John Mitchell-245523 - Thursday, February 8, 2018 9:23 AM
Here's one way of doing it:

WITH Dates (StartDate, EndDate) AS (
SELECT '20131201', '20170110' UNION ALL
SELECT '20131101', '20161110' UNION ALL
SELECT '20130101', '20140131'
)
SELECT
StartDate
, EndDate
, CASE
WHEN StartDate >= '20131231' THEN 0
WHEN EndDate < '20130101' THEN 0
WHEN StartDate >= '20130101' AND EndDate < '20131231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,StartDate,'20131231')
WHEN StartDate < '20130101' AND EndDate <= '20131231' THEN DATEDIFF(day,'20130101',EndDate) + 1
WHEN StartDate < '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,'20130101','20131231') + 1
END AS Daysin2013
, CASE
WHEN StartDate >= '20141231' THEN 0
WHEN EndDate < '20140101' THEN 0
WHEN StartDate >= '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,StartDate,'20141231')
WHEN StartDate < '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,'20140101',EndDate) + 1
WHEN StartDate < '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,'20140101','20141231') + 1
END AS Daysin2014
, CASE
WHEN StartDate >= '20151231' THEN 0
WHEN EndDate < '20150101' THEN 0
WHEN StartDate >= '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,StartDate,'20151231')
WHEN StartDate < '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,'20150101',EndDate) + 1
WHEN StartDate < '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,'20150101','20151231') + 1
END AS Daysin2015
, CASE
WHEN StartDate >= '20161231' THEN 0
WHEN EndDate < '20160101' THEN 0
WHEN StartDate >= '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,StartDate,'20161231')
WHEN StartDate < '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,'20160101',EndDate) + 1
WHEN StartDate < '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,'20160101','20161231') + 1
END AS Daysin2016
, CASE
WHEN StartDate >= '20171231' THEN 0
WHEN EndDate < '20170101' THEN 0
WHEN StartDate >= '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,StartDate,EndDate)
WHEN StartDate >= '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,StartDate,'20171231')
WHEN StartDate < '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,'20170101',EndDate) + 1
WHEN StartDate < '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,'20170101','20171231') + 1
END AS Daysin2017
FROM Dates d;


John

This will work but i forgot to tell you that my years are dynamic. So basically im extracting all data for the past 5 years of the current date. Thank you for your response.

lein
lein
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 153
This is what i came up with John's suggestion. Will show the actual year in Report Builder.
If anyone has a better solution than this ginormous case statement let me know Smile Thank you in advance!

SELECT
StartDate
, EndDate
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0)) + 1
END AS DaysinLast5Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0)) + 1
END AS DaysinLast4Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0)) + 1
END AS DaysinLast3Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0)) + 1
END AS DaysinLast2Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)) + 1
END AS DaysinLastYear
FROM Table1
where StartDate >= (DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01')) and (EndDate >= StartDate or EndDate is null)
Bert-701015
Bert-701015
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1231
I'm not fond of the static years in this code. Perhaps it could be converted to a dynamic pivot
declare @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

drew.allen
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63765 Visits: 16956
I don't know that it's faster, but it's certainly simpler.

;
WITH Dates (ID, StartDate, EndDate) AS (
SELECT 1, '20131201', '20170110' UNION ALL
SELECT 2, '20131101', '20161110' UNION ALL
SELECT 3, '20130101', '20140131'
)
SELECT
ID
, MAX(StartDate) AS StartDate
, MAX(EndDate) AS EndDate
, MAX(CASE WHEN n = 5 THEN DaysInPeriod END) AS DaysInYear1
, MAX(CASE WHEN n = 4 THEN DaysInPeriod END) AS DaysInYear2
, MAX(CASE WHEN n = 3 THEN DaysInPeriod END) AS DaysInYear3
, MAX(CASE WHEN n = 2 THEN DaysInPeriod END) AS DaysInYear4
, MAX(CASE WHEN n = 1 THEN DaysInPeriod END) AS DaysInYear5
FROM Dates
CROSS APPLY
(
SELECT n, PeriodDate, DATEDIFF(DAY, LAG(PeriodDate, 1, StartDate) OVER(PARTITION BY ID ORDER BY PeriodDate), PeriodDate) AS DaysInPeriod

FROM
(
SELECT n, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) - n, -1) AS PeriodDate
FROM ( VALUES(1), (2), (3), (4), (5) ) t(n)
UNION
SELECT DATEDIFF(YEAR, EndDate, GETDATE()), EndDate
) v
WHERE PeriodDate > StartDate
AND PeriodDate <= EndDate
) v
GROUP BY ID
;


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search