I'd like to calculate how many years in the past a particular date is based on Today's date, not December 31st. Unfortunately DateDiff with the YEAR parameter changes the period on December 31st, not the date I'm evaluating against. That makes this report I'm trying to run inaccurate unless we run it near December 31st.
The dates will go with service call records that have information I want to Count, Sum and Average based on call dates between 0 to 1 year ago, 1 to 2 years ago etc. in a later query. I was hoping to figure out the period of each date in a temp table first to make the larger query a little easier to read.
Hopefully the query below will better explain my intent.
DECLARE @t TABLE (dates DATE, [Expected Period] INT)
DECLARE @Today DATE = '2016-06-30'
INSERT INTO @t
VALUES('2016-12-31', 0)
,('2016-06-30', 1)
,('2016-01-01', 1)
,('2015-12-31', 1)
,('2015-06-30', 2)
,('2015-01-01', 2)
,('2014-12-31', 2)
,('2014-06-30', 3)
,('2014-01-01', 3)
,('2013-12-31', 3)
,('2013-06-30', 4)
,('2013-01-01', 4)
SELECT dates
,[Expected Period]
,DATEDIFF(YEAR, dates, @Today) AS 'Date Diff Attempt'
,CASE
WHEN dates BETWEEN DATEADD(YEAR,-1,@Today) AND DATEADD(YEAR, 0,@Today) THEN 1
WHEN dates BETWEEN DATEADD(YEAR,-2,@Today) AND DATEADD(YEAR,-1,@Today) THEN 2
WHEN dates BETWEEN DATEADD(YEAR,-3,@Today) AND DATEADD(YEAR,-2,@Today) THEN 3
END AS 'Bad Attempt'
FROM @t