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 P.ProductName, ProductDesc, P.TargetReleaseDate FROM Product P INNER JOIN ( SELECT CAST(UnPvt.RangeYear AS VARCHAR(4)) AS RangeYear FROM ( SELECT YEAR(BaseYearMinus) YearMinus, YEAR(BaseYearPlus) YearPlus FROM PlusMinusThree ) Tbl UNPIVOT (RangeYear FOR ReleaseYear IN (YearMinus, YearPlus)) UnPvt GROUP BY RangeYear ) R ON P.TargetReleaseDate like '%'+R.RangeYear+'%'
2012-11-21 (first published: 2012-10-02)
1,537 reads