DynamicDateRangeGenerator

/* 
Tired of having queries coded like: 

    Case when SomeDateColumn like '%2013%' then 2013
        when SomeDateColumn like '%2014%' then 2014
        when SomeDateColumn like '%2015%' then 2015
        when SomeDateColumn like '%2016%' then 2016

We are tracking the products target date in a window that's usually 
current year - 3 to current year + 3

Every Year someone has to modify the query to adjust the range and naturally we forget.
*/WITH DynamicDateRangeGenerator(BaseYearMinus, BaseYearPlus)
AS
(
SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) BaseYearMinus, DATEFROMPARTS(YEAR(GETDATE()), 1, 1) BaseYearPlus
UNION ALL
SELECT DATEADD(YEAR, -1, BaseYearMinus) BaseYearMinus, DATEADD(YEAR, 1, BaseYearPlus) BaseYearPlus FROM DynamicDateRangeGenerator
WHERE (DATEDIFF(YEAR, BaseYearMinus, BaseYearPlus)/2) < (3 /* <=== Number of years to generate on either side of current year*/)
)
SELECT UnPvt.RangeYear
FROM
(
SELECT YEAR(BaseYearMinus) YearMinus, YEAR(BaseYearPlus) YearPlus FROM DynamicDateRangeGenerator
) Tbl
UNPIVOT (RangeYear FOR ReleaseYear IN (YearMinus, YearPlus)) UnPvt
GROUP BY RangeYear
ORDER BY RangeYear;