clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2042731 fiddles created (16504 in the last week).

drop table if exists Orders; create table Orders (SellerID varchar(10) not NULL ,OrderID int not NULL ,OID int NULL); insert into Orders values ('seller1',123456,null), ('seller1',123457,null), ('seller1',123458,123460), ('seller1',123459,123460), ('seller2',234567,null), ('seller1',123455,null), ('seller2',234568,234570), ('seller2',234569,234570), ('seller1',123463,null), ('seller1',123466,null), ('seller1',123453,null), ('seller2',234572,null), ('seller1',123470,null);
13 rows affected
 hidden batch(es)


--select * from Orders;
 hidden batch(es)


/* select SellerID,OrderID from Orders union select SellerID,OID from Orders where OID is not NULL order by 1,2; */
 hidden batch(es)


with -- build a list of all OrderIDs allids (SellerID, OrderID) as (select SellerID, OrderID from Orders union select SellerID, OID from Orders where OID is not NULL), -- find the first 'missing' OrderID for each gap gapStart (SellerID, startOrderID) as (select a1.SellerID, a1.OrderID + 1 from allids a1 where not exists(select 1 from allids a2 where a2.OrderID = a1.OrderID + 1)), -- now find the end of each gap gaps (SellerID, startOrderID, endOrderID) as (select gs.SellerID, gs.startOrderID, min(OrderID) - 1 from gapStart gs join allids a on a.SellerID = gs.SellerID and a.OrderID > gs.startOrderID group by gs.SellerID, gs.startOrderID) -- display the gaps select g.SellerID, g.startOrderID + n.rn - 1 as OrderID from gaps g -- generate a sequence (1..N) where N = difference between gaps.startOrderID and gaps.endOrderID cross apply (select top(g.endOrderID - g.startOrderID + 1) row_number() over (order by (select NULL)) as rn from Orders) as n order by 1,2;
SellerID OrderID
seller1 123454
seller1 123461
seller1 123462
seller1 123464
seller1 123465
seller1 123467
seller1 123468
seller1 123469
seller2 234571
 hidden batch(es)