Find missing sequence number in a database

Question

Let suppose my database data structure is like that

ShopId   Transaction

001       1.000    
001       2.000
001       3.000
002       1.000
002       2.000
002       3.000

Now it is might possible that some transaction are missing let suppose

002       4.000
002       6.000

In above table 5.000 transaction of table is missing.

I want to write a query that find the missing sequence number like above in my database.

So my query will return result

   shopid     transaction
   002       5.000

DavidEG · Accepted Answer · 2011-11-26 13:26:15Z

To get the number of missing transactions per ShopId:

  SELECT ShopId
       , count(*) 
           - max([Transaction])
           + min([Transaction])
           - 1 as MissingTranCount
    FROM yourtable
GROUP BY ShopId

To get the missing transactions:

1.Generate table #numbers

2.Generate #shops:

  SELECT ShopId as id
    into #shops
    FROM yourtable
GROUP BY ShopId
  HAVING count(*) 
           - max([Transaction])
           + min([Transaction])
           - 1 > 0

3.Get the missing transactions:

  SELECT id
       , number
    FROM #numbers
   cross join #shops shops
   WHERE exists (SELECT 1
                   FROM yourtable
                  WHERE ShopId = id
                    and number < [Transaction]
                )
     and exists (SELECT 1
                   FROM yourtable
                  WHERE ShopId = id
                    and number > [Transaction]
                )
     and not exists (SELECT 1
                       FROM yourtable
                      WHERE ShopId = id
                        and number = [Transaction]
                )

Collectives™ on Stack Overflow

Find missing sequence number in a database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related