clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
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. 648473 fiddles created (15197 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, by SellerID allids (SellerID, OrderID) as (select SellerID, OrderID from Orders union select SellerID, OID from Orders where OID is not NULL), -- find the max(OrderID), by SellerID; to be used to limit a) cte recursion and b) final result maxIDs (SellerID, maxOrderID) as (select SellerID, max(OrderID) from allids group by SellerID), -- use a recursive cte to fill in gaps of missing OrderID's recurse (SellerID, gapID) as (-- find first 'missing' OrderID in a gap select a.SellerID, a.OrderID + 1 from allids a where not exists(select 1 from allids b where b.SellerID = a.SellerID and b.OrderID = a.OrderID + 1) union all -- find successive 'missing' OrderIDs select a.SellerID, a.gapID + 1 from recurse a where not exists(select 1 from allids b where b.SellerID = a.SellerID and b.OrderID = a.gapID + 1) and a.gapID < (select maxOrderID from maxIDs m where m.SellerID = a.SellerID) ) select r.SellerID, r.gapID from recurse r where r.gapID < (select maxOrderID from maxIDs m where m.SellerID = r.SellerID) order by 1,2 -- if gaps could be expected to span more than 1000 'missing' OrderIDs, -- update the MaxRecursion option accordingly: option (MaxRecursion 1000) ;
SellerID gapID
seller1 123454
seller1 123461
seller1 123462
seller1 123464
seller1 123465
seller1 123467
seller1 123468
seller1 123469
seller2 234571
 hidden batch(es)