F.Patel F.Patel - 6 days ago 6
SQL Question

getting next 5 years for all projects in sql

I need next 5 years for all projects
As an example: Let's say project number is 1 and start year for that project is 2015 then I need next 5 year for that project. Just like that for another project number 2 if start year is 2018 then I need next 5 years from 2018.

Expected Output:

Project number Year

1----------------2015

1------------------2016

1------------------2017

1-----------------2018

1------------------2019

2------------------2018

2------------------2019

2------------------2020

2-------------------2021

2-----------------2022


Here is the code that I have written but it is not giving me anything that I expected.

WITH YEARLIST
AS (SELECT PROJECT_NUMBER
,START_YEAR AS YEAR
FROM TABLE_ABC

UNION ALL
SELECT
PROJECT_NUMBER
,Y.YEAR +1 AS YEAR
FROM YEARLIST Y
WHERE Y.YEAR >= Y.YEAR +5
)
SELECT
PROJECT_NUMBER
,YEAR
FROM YEARLIST

OPTION (MAXRECURSION 0)


Thanks in advance..

Answer

Sorry about the formatting, I'm new to the site.

CREATE TABLE #TMP1(PrId SMALLINT, StrtYr SMALLINT)

INSERT INTO #TMP1  (PrId,StrtYr)
VALUES
(1,2015),
(2,2018),
(3,2016),
(4,2014),
(5,2017),
(6,2015)



SELECT 
    * 
FROM #TMP1
        CROSS APPLY ( VALUES (StrtYr + 1)
                                ,(StrtYr + 2)
                                ,(StrtYr + 3)
                                ,(StrtYr + 4)
                                ,(StrtYr + 5)
                                ) FutureYear(futureyear)
Comments