Transact SQL - Filling gaps in a sequence

Question

Hi,
I have a table with a sequence on one column, which has a gap (in this case it is order number 9 that is missing):

How would I write a tsql script that will detect the gap and fill it as below?

Answer 1

create table test2 (Order_ID varchar(30), Order_Number int)  
  
insert into test2 (Order_ID, Order_Number)values  
('ORDER64276',1),  
('ORDER64288',2),  
('ORDER64296',3),  
('ORDER64312',4),  
('ORDER64542',5),  
('ORDER64552',6),  
('ORDER64560',7),  
('ORDER64564',8),  
('ORDER64718',10),  
('ORDER64984',11)  
  
 --===== Create number table on-the-fly  
;WITH Num1 (n) AS (  
SELECT 1 as n  
UNION ALL SELECT n+1 as n  
FROM Num1 Where n <101),  
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),  
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)  
  
,mycte as (  
select * from Nums    
left join test2 t on Nums.n=t.Order_Number  
where Nums.n<=(select max(Order_Number)   
from test2 )  
)  
  
select d.Order_ID, d.Order_Number   
from mycte m  
outer apply (select top 1 Order_ID, Order_Number from mycte t   
where t.n<=m.n and (t.Order_ID is not null and t.Order_Number is not null)   
Order by t.n  desc   
) d(Order_ID, Order_Number)  
  
   
drop table test2  

Answer 2

Hi @Mirithu Kinyanjui
Please also check this query using JingyangLi's sample data:

create table #test2 (Order_ID varchar(30), Order_Number int)  
insert into #test2 (Order_ID, Order_Number)values  
 ('ORDER64276',1),  
 ('ORDER64288',2),  
 ('ORDER64296',3),  
 ('ORDER64312',4),  
 ('ORDER64542',5),  
 ('ORDER64552',6),  
 ('ORDER64560',7),  
 ('ORDER64564',8),  
 ('ORDER64718',10),  
 ('ORDER64984',11)  
  
DECLARE @MAX_OrderNumber INT   
SET @MAX_OrderNumber = 11  
  
;WITH Num AS   
(  
 SELECT 1 AS OrderNumber  
 UNION ALL   
 SELECT OrderNumber+1  
 FROM Num   
 WHERE OrderNumber < @MAX_OrderNumber  
),GAP AS  
(  
 SELECT O.Order_ID,O.Order_Number,N.OrderNumber AS Order_Num  
       ,SUM(CASE WHEN O.Order_Number IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY N.OrderNumber) AS GAP  
 FROM Num N LEFT JOIN #test2 O ON N.OrderNumber=O.Order_Number  
)  
SELECT FIRST_VALUE(Order_ID) OVER (PARTITION BY GAP ORDER BY Order_Num) AS Order_ID  
      ,FIRST_VALUE(Order_Number) OVER (PARTITION BY GAP ORDER BY Order_Num) AS Order_Number  
FROM GAP  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.