RE: Get difference between dates year by year

SSC Eights!

Points: 974

February 8, 2018 at 12:23 pm

#1979090

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)