February 8, 2018 at 8:26 am
| 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 🙂
February 8, 2018 at 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
February 8, 2018 at 10:11 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
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.
February 8, 2018 at 12:23 pm
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 🙂 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)
February 8, 2018 at 12:41 pm
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
February 8, 2018 at 12:44 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply