Tsql identifies the missing maximum and minimum ID values from the data table.

Sample

Declare @ a table (account INT) insert into @ A values (1) insert into @ A values (2) insert into @ A values (3) insert into @ A values (4) insert into @ A values (9) insert into @ A values (10) insert into @ A values (11) insert into @ A values (20) -- missing minimum ID: Select case when not exists (select * From @ A where account = 1) then 1 else min (account) + 1 end from @ A as a where not exists (select * From @ A as B where B. account =. account + 1) -- the largest missing ID: Select case when not exists (select * From @ A where account = 1) then 1 else max (account) -1 end from @ A as a where not exists (select * From @ A as B where B. account =. account-1)

 

Returned results: Missing minimum: 5 Missing maximum: 19
Idea: Compare the original table data value with the value + 1/-1 to find the missing adjacent value. Then, the condition "if the minimum value of 1 does not exist, 1 is assigned to the value 1, the minimum value (+ 1) and maximum value (-1) of the table with missing values are not assigned weekly)