Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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); > GO > > <pre> 13 rows affected > </pre> <!-- --> > --select * from Orders; > GO > > <pre> > ✓ > </pre> <!-- --> > /* > select SellerID,OrderID from Orders > union > select SellerID,OID from Orders where OID is not NULL > order by 1,2; > */ > GO > > <pre> > ✓ > </pre> <!-- --> > 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; > GO > > <pre> > SellerID | OrderID > :------- | :------ > seller1 | 123454 > seller1 | 123461 > seller1 | 123462 > seller1 | 123464 > seller1 | 123465 > seller1 | 123467 > seller1 | 123468 > seller1 | 123469 > seller2 | 234571 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=11b3889c783d1c1a563d61b377e10e70)*
back to fiddle