CODEWITHSUNDEEP
sqlsql-server
Arulkumar
Arulkumar

Reputation: 13237

Get the list of year values based on the gap and year value in the table

Scenario: I have a table with Year and Gap columns. What I need the output as, starting from the given year value it incremented up to the value in the gap column.

i.e., If the YearVal is 2001, and Gap is 3, I need the output as

Result
--------
2001
2002
2003

What I have tried:

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);
INSERT INTO @ResultYears (Gap, YearVal) VALUES (3, 2001);

;WITH FinalResult AS (
     SELECT YearVal AS [YR] FROM @ResultYears
     UNION ALL
     SELECT [YR] + 1 FROM FinalResult 
     WHERE [YR] + 1 <= (SELECT YearVal + (Gap -1) FROM @ResultYears)
)

SELECT * FROM FinalResult;

db<>fiddle demo with one entry in the table.

Using the query above, I can achieve the expected result. But if the table have more than one entry, the query is not working.

i.e., If I have the entries in the table as below:

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);

INSERT INTO @ResultYears (Gap, YearVal) VALUES
(3, 2001), (4, 2008), (1, 2014), (2, 2018);

How can I modify the query to achieve my expected result?

db<>fiddle demo with more than one entry in the table.

Upvotes: 4

Views: 82

Answers (3)

Serg
Serg

Reputation: 22811

If for a reason, you prefer recursive CTE (which is definetly slower)

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);

INSERT INTO @ResultYears (Gap, YearVal) VALUES (3, 2001), (4, 2008), (1, 2014), (2, 2018);

;WITH FinalResult AS (
     SELECT YearVal, Gap, YearVal [YR] FROM @ResultYears
     UNION ALL
     SELECT YearVal, Gap, [YR] + 1 
     FROM FinalResult 
     WHERE [YR] + 1 <= YearVal + (Gap -1)
)
SELECT * FROM FinalResult
ORDER BY [YR];

You need to keep original row parameters in the recursive part. this way recursion runs as desired.

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Initially Create one user defined table type function which return the Gap years

CREATE FUNCTION [dbo].[ufn_GetYears]
 (
 @i_Gap INT,@Year INT
 )
RETURNS @Temp TABLE
(
    Years INT
)
AS
BEGIN
    ;WITH CTE
    AS
    (
    SELECT 1 AS Seq,DATEFROMPARTS ( @Year,01,01) AS Years
    UNION ALL
    SELECT seq +1,DATEADD(YEAR,1,Years)
    FROM Cte 
    WHERE Seq < @i_Gap
    )
    INSERT INTO @Temp
    SELECT DATEPART(YEAR,Years )
    FROM CTE
RETURN
END

Sample Data

DECLARE @ResultYears TABLE 
    (Gap INT,
     YearVal INT
     );

INSERT INTO @ResultYears (Gap, YearVal) VALUES
(3, 2001), (4, 2008), (1, 2014), (2, 2018);

Sql Query to get the expected result using CROSS APPLY

SELECT R.Gap,dt.Years
FROM @ResultYears R
CROSS APPLY [dbo].[ufn_GetYears](R.Gap,R.YearVal) AS dt

Result

Gap Years
---------
3   2001
3   2002
3   2003
4   2008
4   2009
4   2010
4   2011
1   2014
2   2018
2   2019

Upvotes: 1

Thom A
Thom A

Reputation: 95827

Is this what you're after?

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);

INSERT INTO @ResultYears (Gap, YearVal) VALUES
(3, 2001), (4, 2008), (1, 2014), (2, 2018);

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
    FROM N N1, N N2), --100 is more than enough
Years AS(
    SELECT RY.YearVal + T.I AS [Year],
           RY.Gap,
           RY.YearVal
    FROM @ResultYears RY
         JOIN Tally T ON RY.Gap > T.I)
SELECT *
FROM Years Y
ORDER BY Y.YearVal;

Personally I prefer a tally table over a rCTE; they are far quicker, especially with large datasets, or where the rCTE would have to do a high volume of recursion.

Demo on db<>fiddle

Upvotes: 3

Related Questions