Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Get difference between dates year by year RE: Get difference between dates year by year
February 8, 2018 at 10:46 am
John Mitchell-245523 - Thursday, February 8, 2018 9:23 AMHere'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.