how to create column based on other column in sql?

id  year
1   2017
1   2018
1   2019
2   2018
2   2019
3   2017
3   2019
8   2017   
4   2018
4   2019

I need to create column based on id and year column:

1. if a id present in 2017 and 2018 (subsequent year) then mark ‘P’ against 2017.
2. if a id present in 2018 and 2019 then mark ‘P’ then mark ‘P’ against 2017.
3. if a id present in 2017 but not in subsequent year then mark ‘N’ against 2017
4. If there is no data of subsequent year then mark ‘N’ in the previous year (2019)

output :

id  year  mark
1   2017  P
1   2018  P
1   2019  N
2   2018  P
2   2019  N
3   2017  N
3   2019  N
8   2017  P
4   2018  P
4   2019  N

Answer

You can try Lead() function. but please check output for Id = 8. Ideally it should be ‘N’

SELECT *
    ,CASE WHEN LEAD(Year) OVER (PARTITION BY ID ORDER BY YEAR) - YEAR = 1 THEN 'P' ELSE 'N' END
FROM #Table