user3552829 user3552829 - 7 months ago 14
SQL Question

Algorithm in SQL While Loop

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).


Upon selecting those, SQL Server should populate a table with years between @startyear and @endyear each time adding up the number of years specified in the the @cycle format. I realise this might sound confusing so here's an example. If the @cycle is 4-6-6-6-6, @startyear = 2016 and @endyear = 2050, then the result should be:

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)


Any help would be greatly appreciated! Thank you.

ZLK ZLK
Answer

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).