clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36278 in the last week).

CREATE TABLE #yourtable (grp int, Age int, Period int, Yr int) INSERT INTO #yourtable (grp, Age, Period, Yr) VALUES (1, 0 , NULL, NULL), (1, 1 , NULL, NULL), (1, 2 , NULL, NULL), (1, 3 , NULL, NULL), (1, 4 , NULL, NULL), (1, 5 , NULL, NULL), (1, 6 , NULL, NULL), (1, 7 , NULL, NULL), (1, 8 , NULL, NULL), (1, 9 , NULL, NULL), (1, 10, NULL, NULL), (1, 11, NULL, NULL), (1, 12, NULL, NULL), (1, 13, NULL, NULL), (1, 14, NULL, NULL), (1, 15, NULL, NULL), (1, 16, NULL, NULL), (1, 17, NULL, NULL), (1, 18, NULL, NULL), (1, 19, NULL, NULL), (1, 20, NULL, NULL), (1, 21, 2, 2021), (1, 22, NULL, NULL), (1, 23, NULL, NULL), (1, 24, NULL, NULL), (1, 25, NULL, NULL), (1, 26, null , null), (1, 27, NULL, NULL), (1, 28, NULL, NULL), (1, 29, NULL, NULL), (1, 30, NULL, NULL);
31 rows affected
 hidden batch(es)


with cte as ( select age ,max(period - age) over () + age as period ,max(yr - age) over() + age as yr from #yourtable ) select age ,case when period >0 then period end as period ,yr from cte
age period yr
0 2000
1 2001
2 2002
3 2003
4 2004
5 2005
6 2006
7 2007
8 2008
9 2009
10 2010
11 2011
12 2012
13 2013
14 2014
15 2015
16 2016
17 2017
18 2018
19 2019
20 1 2020
21 2 2021
22 3 2022
23 4 2023
24 5 2024
25 6 2025
26 7 2026
27 8 2027
28 9 2028
29 10 2029
30 11 2030
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)


; WITH PeriodInc AS (SELECT TOP 1 Period - Age AS PeriodInc FROM #yourtable WHERE Period IS NOT NULL), YrInc AS (SELECT TOP 1 Yr - Age AS YrInc FROM #yourtable WHERE Yr IS NOT NULL) SELECT Age, CASE WHEN (Age + PeriodInc) >= 0 THEN (Age + PeriodInc) ELSE NULL END AS Period, Age + YrInc AS Yr FROM #yourtable CROSS JOIN PeriodInc CROSS JOIN YrInc
Age Period Yr
0 2000
1 2001
2 2002
3 2003
4 2004
5 2005
6 2006
7 2007
8 2008
9 2009
10 2010
11 2011
12 2012
13 2013
14 2014
15 2015
16 2016
17 2017
18 2018
19 0 2019
20 1 2020
21 2 2021
22 3 2022
23 4 2023
24 5 2024
25 6 2025
26 7 2026
27 8 2027
28 9 2028
29 10 2029
30 11 2030
 hidden batch(es)