3 added 2332 characters in body
source | link

Here's a dbfiddle for the recursive cte


Not particularly fond of recursive queries when I can't be 100% sure of how deep the recursion needs to go, so wanted a to look at how to do this without recursion.

The gist of this idea is to find the start/end points of the gaps, and use a sequence generator to fill in the missing OrderIDs.

NOTE: I've discarded my initial sequence generator and replaced with @Daniel's sequence generator.

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 |  gapID
 -------- | ------
 seller1  | 123454
 seller1  | 123461
 seller1  | 123462
 seller1  | 123464
 seller1  | 123465
 seller1  | 123467
 seller1  | 123468
 seller1  | 123469
 seller2  | 234571

Here's a dbfiddle for the gap/sequence idea.

Here's a dbfiddle

Here's a dbfiddle for the recursive cte


Not particularly fond of recursive queries when I can't be 100% sure of how deep the recursion needs to go, so wanted a to look at how to do this without recursion.

The gist of this idea is to find the start/end points of the gaps, and use a sequence generator to fill in the missing OrderIDs.

NOTE: I've discarded my initial sequence generator and replaced with @Daniel's sequence generator.

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 |  gapID
 -------- | ------
 seller1  | 123454
 seller1  | 123461
 seller1  | 123462
 seller1  | 123464
 seller1  | 123465
 seller1  | 123467
 seller1  | 123468
 seller1  | 123469
 seller2  | 234571

Here's a dbfiddle for the gap/sequence idea.

2 added 2446 characters in body
source | link

NOTE: OP mentions having a query that finds the gaps for a single column, so I'm assuming OP could use the (above) cte to replace references to Order ... in the mean time ...


One not-necessarily-the-cleanest gap-finding query that eliminates the need for the @SellerID variable:

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

Here's a dbfiddle

NOTE: OP mentions having a query that finds the gaps for a single column, so I'm assuming OP could use the (above) cte to replace references to Order

OP mentions having a query that finds the gaps for a single column, so I'm assuming OP could use the (above) cte to replace references to Order ... in the mean time ...


One not-necessarily-the-cleanest gap-finding query that eliminates the need for the @SellerID variable:

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

Here's a dbfiddle

1
source | link

One idea would be to use a CTE to extract a combined list of IDs, then replace Orders with the CTE name, eg:

with

allids as
(select SellerID,OrderID from Orders
 union
 select SellerID,OID     from Orders where OID is not NULL)

select ...
from   allids s1
left
join   allids s2
...

NOTE: OP mentions having a query that finds the gaps for a single column, so I'm assuming OP could use the (above) cte to replace references to Order