I have a requirement whereby I start with three parameters:
@startyear INT (e.g. 2016),
@endyear INT (e.g. 2050),
@cycle VARCHAR(9) (e.g. 4-6-6-6-6).
2020 (2016 + 4 years, i.e. first number in @cycle)
2026 (2020 + 6 years, i.e. second number in @cycle)
2032 (2026 + 6 years, and so on..)
2038 (2032 + 6 years)
2044 (2038 + 6 years)
2048 (2044 + 4 years)
Edited because the original answer misunderstood the question:
DECLARE @startyear INT = 2016, @endyear INT = 2080, @cycle VARCHAR(9) = '4-6-6-6-6'; WITH CTE1 AS ( SELECT SUBSTRING(@cycle, A.B, 1) S, ROW_NUMBER() OVER (ORDER BY A.B) RN FROM ( VALUES (1), (3), (5), (7), (9)) A(B)) , E1(N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) A(B)) , E2(N) AS (SELECT 1 FROM E1 A CROSS JOIN E1) , CTE2 AS ( SELECT CAST(CTE1.S AS INT) S, (E2.N - 1) * 5 + RN RN FROM CTE1 CROSS JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2) E2(N) WHERE E2.N <= (SELECT (@endyear - @startyear) / SUM(CAST(S AS INT)) + 1 FROM CTE1)) SELECT @startyear + SUM(T2.S) Years FROM CTE2 T1 JOIN CTE2 T2 ON T1.RN >= T2.RN GROUP BY T1.RN HAVING @startyear + SUM(T2.S) < @endyear ORDER BY T1.RN;
This looks at the @cycle input, loops through it a number of times (based on the sum of the input in @cycle compared to @endyear - @startyear) then finds all the years in between based on it.
Notes: It requires input be single digits (separated by a single character delimiter) and it requires the total number of loops to be less than 100 (e.g. if you want to cycle through @cycle more than 100 times, this needs to change, but I doubt that would be the case).