Dynamic transformation of year values

1

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?

2

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
1 Like
3 
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
2 Likes
4

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
1 Like
5

Thank you for your answer! This seems like a quick and easy way to do it :slight_smile:

6

Thanks for your answer!