Calculating Yearly Period of Dates Based on Today

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

See the last column in output of the following query:

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',
		datediff(yy,dateadd(dd,1,dates),c)
FROM @t
cross apply ( values( dateadd(yy,datediff(yy,0,@today)+1,0)) ) p(c)
1 Like

Here's one way: use a tally table (in this case, I've used an inline-cte-tally-table, but naturally it could be a physical table as well) to generate year start and end dates based on the value of @today, and then join to the main table:

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT t.*, dates.*
FROM (
    SELECT DATEADD(YEAR, -(t.number - 1), @Today) AS Year_End_Date, 
        DATEADD(DAY, 1, DATEADD(YEAR, -t.number, @Today)) AS Year_Start_Date,
        t.number AS Year_Diff
    FROM cteTally100 t
) AS dates
INNER JOIN @t t ON t.dates BETWEEN dates.Year_Start_Date AND dates.Year_End_Date
1 Like

Thank you both very much! This really helped me look at the problem differently. I also appreciated how both solutions used different methods neither of which I'm familiar with normally doing. Thanks again with taking the time.