Hello, I was wondering if there is a way to dynamically transform year values into 3 year cycle values. For instance, I would like the years 2010, 2011 and 2012 to be associated with 'cycle 1', 2013, 2014, 2015 to be associated with 'cycle 2' etc. I know how to do this using CASE but I was wondering if there was any function that could help me with this?
to get the juices flowing
declare @years table(_year int) insert into @years select distinct top 20 year(dateadd(yy,column_id,'2009-01-01')) from sys.columns where column_id between 1 and 100 order by 1 ;with src as ( select *, LAG(_year, 1,0) OVER (ORDER BY _year) AS Previous, LEAD(_year, 1,0) OVER (ORDER BY _year) AS Next from @years ) select * from src
SELECT *, (year - 2010) / 3 + 1 AS cycle# FROM ( VALUES(2010), (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018), (2019), (2020), (2021), (2022) ) AS years(year) ORDER BY year
nice! to demonstrate with sample data and progression of the logic
select * , (_year - 2010), (_year - 2010)/3, (_year - 2010) / 3 + 1 AS cycle# from @years
Thank you for your answer! This seems like a quick and easy way to do it
Thanks for your answer!