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 mytable( Age INT NOT NULL PRIMARY KEY ,Period INTEGER ,Year INTEGER ); INSERT INTO mytable(Age,Period,Year) VALUES ('0',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('1',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('2',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('3',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('4',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('5',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('6',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('7',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('8',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('9',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('10',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('11',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('12',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('13',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('14',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('15',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('16',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('17',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('18',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('19',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('20',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('21',46,2065); INSERT INTO mytable(Age,Period,Year) VALUES ('22',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('23',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('24',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('25',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('26',51,2070); INSERT INTO mytable(Age,Period,Year) VALUES ('27',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('28',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('29',NULL,NULL); INSERT INTO mytable(Age,Period,Year) VALUES ('30',NULL,NULL);
31 rows affected
 hidden batch(es)


select t.age, coalesce(t.period, n.period - n.diff, p.period - p.diff) period, coalesce(t.year, n.year - n.diff, p.year - p.diff) year from mytable t outer apply ( select top (1) t1.*, t1.age - t.age diff from mytable t1 where t1.age > t.age and t1.period is not null and t1.year is not null order by t1.age ) n outer apply ( select top (1) t1.*, t1.age - t.age diff from mytable t1 where t1.age < t.age and t1.period is not null and t1.year is not null order by t1.age desc ) p order by t.age
age period year
0 25 2044
1 26 2045
2 27 2046
3 28 2047
4 29 2048
5 30 2049
6 31 2050
7 32 2051
8 33 2052
9 34 2053
10 35 2054
11 36 2055
12 37 2056
13 38 2057
14 39 2058
15 40 2059
16 41 2060
17 42 2061
18 43 2062
19 44 2063
20 45 2064
21 46 2065
22 47 2066
23 48 2067
24 49 2068
25 50 2069
26 51 2070
27 52 2071
28 53 2072
29 54 2073
30 55 2074
 hidden batch(es)


select age, coalesce( period, max(period) over(partition by grp2) - max(age) over(partition by grp2) + age, max(period) over(partition by grp1) - min(age) over(partition by grp1) + age ) period, coalesce( year, max(year) over(partition by grp2) - max(age) over(partition by grp2) + age, max(year) over(partition by grp1) - min(age) over(partition by grp1) + age ) year from ( select t.*, count(period) over(order by age) grp1, count(period) over(order by age desc) grp2 from mytable t ) t order by age
age period year
0 25 2044
1 26 2045
2 27 2046
3 28 2047
4 29 2048
5 30 2049
6 31 2050
7 32 2051
8 33 2052
9 34 2053
10 35 2054
11 36 2055
12 37 2056
13 38 2057
14 39 2058
15 40 2059
16 41 2060
17 42 2061
18 43 2062
19 44 2063
20 45 2064
21 46 2065
22 47 2066
23 48 2067
24 49 2068
25 50 2069
26 51 2070
27 52 2071
28 53 2072
29 54 2073
30 55 2074
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)