Fill in gaps in year sequence in SQL Server

Question

I have a table with the columns Age, Period and Year. The column Age always starts with 0 and doesn't have a fixed maximum value (I used 'Age' 0 to 30 in this example but the range could also be 0 to 100 etc.), the values Period and Year only appear in certain rows at certain ages.

However at what Age the values for Period and Year appear, changes and the solution should therefore be dynamic. What is the best way to fill in the NULL values with correct Period and Year?

I am using SQL Server.

Age Period  Year
-----------------
0   NULL    NULL
1   NULL    NULL
2   NULL    NULL
3   NULL    NULL
4   NULL    NULL
5   NULL    NULL
6   NULL    NULL
7   NULL    NULL
8   NULL    NULL
9   NULL    NULL
10  NULL    NULL
11  NULL    NULL
12  NULL    NULL
13  NULL    NULL
14  NULL    NULL
15  NULL    NULL
16  NULL    NULL
17  NULL    NULL
18  NULL    NULL
19  NULL    NULL
20  NULL    NULL
21  46      2065
22  NULL    NULL
23  NULL    NULL
24  NULL    NULL
25  NULL    NULL
26  51      2070
27  NULL    NULL
28  NULL    NULL
29  NULL    NULL
30  NULL    NULL

The result should look like this, the numbers for Period and Year should be increased and/or decrease from the last known values for Period and Year.

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

Here is an UPDATE to my question as I didn't specify my requirement detailed enough: The solution should be able to handle different combinations of Age, Period and Year. My start point will always be a known Age, Period and Year combination. However, the combination Age = 21, Period = 46 and Year = 2065 (or 26|51|2070 as the second combination) in my example is not static. The value at Age = 21 could be anything e.g. Period = 2 and Year = 2021. Whatever the combination (Age, Period, Year) is, the solution should fill in the gaps and finish the sequence counting up and down from the known values for Period and Year. If a Period value sequence becomes negative the solutions should return NULL values, if possible.

Answer 1

Seem you have always the same increment for age and year so

select age, isnull(period,age +25) Period,  isnull(year,age+44) year  
from yourtable 

or the standard function coalesce (as suggested by Gordon Linoff)

select age, coalesce(period,age +25) Period,  coalesce(year,age+44) year  
from yourtable 

Answer 2

Tabel creation code

create table yourtable ( AGE int ,  Period  int, Year int )

insert into  yourtable
Select  0    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  1    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  2    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  3    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  4    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  5    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  6    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  7    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  8    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  9    AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  10   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  11   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  12   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  13   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  14   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  15   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  16   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  17   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  18   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  19   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  20   AS AGE , null  As Period   ,  null  As Year       UNION all 
Select  21   AS AGE ,46 As Period   ,2065    As Year       UNION all 
Select  22   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  23   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  24   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  25   AS AGE ,   51  As Period   ,2070    As Year       UNION all 
Select  26   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  27   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  28   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  29   AS AGE ,  null As Period   ,  null  As Year       UNION all 
Select  30   AS AGE ,  null As Period   ,  null  As Year      

**Steps **

• We need to get one row with non null value for Period and year.
• Using age get first value for both the column .
• Now just add respective age column value and fill full table .

Code to fix the serial

;with tmp as 
  (select top 1 * from yourtable  where  Period is not null and  year is not null)
 update yourtable
  set Period =  (tmp.Period - tmp.age) + yourtable.age
  , year =  (tmp.year - tmp.age) + yourtable.age
  from yourtable , tmp

OR

Declare @age int  ,@Year int  ,@Period int 

select @age = age , @Year = year - (age +1)  ,@Period  = Period- (AGE +1)
from yourtable where  Period is not null and  year is not null

update  yourtable
set Period =@Period + age
,Year =@year + age
 from yourtable 

Answer 3

You finally want three sequences with different start values. Then you simply need to calculate an offset and add it to age:

with cte as
 (
   select age
     ,max(period - age) over () + age as period -- adjusted period
     ,max(yr - age)     over () + age as yr     -- adjusted yr
   from #yourtable
 )
select age
  -- If a Period value sequence becomes negative the solutions should return NULL
  ,case when period >0 then period end as period
  ,yr
from cte

See fiddle

Answer 4

-- hope you can manage the syntax error. but some logic like given below should work in this case where we can make period an origin to calculate other missing values. good luck!

declare @knownperiod int;
declare @knownperiodage int;
declare @agetop int;
declare @agebottom int;

@knownperiod = select top 1 period from table1 where period is not null
@knownperiodage = select top 1 age from table1 where period is not null



while(@knownperiodage >= 0)
begin 
@knownperiod = @knownperiod -1 ;
@knownperiodage = @knownperiodage -1;
update table1 set period = @knownperiod, year = YEAR(GetDate())+@knownperiod-1  where age = @knownperiodage
end

-- now for bottom age 
@knownperiod = select top 1 period from table1 where period is null or year is null
@knownperiodage = select top 1 age from table1 where period is null or year is null

while(@knownperiodage <= (Select max(age) from table1))
begin 
@knownperiod = @knownperiod +1 ;
@knownperiodage = @knownperiodage +1;
update table1 set period = @knownperiod, year = YEAR(GetDate())+@knownperiod-1  where age = @knownperiodage
end