To read the article online, visit

The SQL Guru Answers your Questions...

Today's question comes from Binu D.:

I have an acounts table where i have debit(s) and credit(s) against each transaction. For each tranasaction number, i will have more than one entry. A (-) amount is a debit and a (+) a credit. In any case the sum of a transaction should be zero. A sample transaction is listed below.

Tr.No    Srno        Amt
2020      1        500.00
2020      2       -500.00
2021      1        750.00
2021      2        -500.00
2021      3        -250.00
2022      1        -2500.00
2022      2          500.00
2022      3          500.00
2022      4         1500.00
2024      1        -2500.00
2024      2         2500.00
2024      3         1000.00
2024      4        -1000.00

Ok. Now to my problem...the transaction numbers are supposed to be in serial order. In my sample , I am missing transaction no. 2023. Can you give me a SQL solution to find out the missing transaction No?


Yuck. This is always a tricky problem. I thought of a couple of different ways to solve this off the top of my head, there may be others. I suspect Solution #1 would be faster. (all SQL syntax is SQL Server T-SQL, since you didn't mention which database you're using)

Solution #1
1) Create a table that contains sequential Transaction numbers for the range you want to search. (You could also make it Transaction, Sequence if you wanted to search for missing sequence #'s in a transaction).
2) LEFT Join from the table containing the sequential numbers to your transaction table:

SELECT * FROM AllTransNums a LEFT JOIN MyTransaction t on a.TrNum = t.TrNum WHERE t.TRNum IS NULL

The rows returned are your missing transaction numbers. Note that the above query will only work with the ANSI join syntax shown.

Incedentally, here is a quick way to populate the table in step 1:

DECLARE @current int, @end int SELECT @current = 1, @end = 1000 WHILE @current <= @end BEGIN INSERT foo VALUES (@current) SELECT @current = @current + 1 END

Solution #2
Use a cursor to step through the table row-by-row. Save the value of the last row examined, and if the current row isn't one greater, you have a missing number. Such as:

DECLARE @LastTran int, @CurrTran int DECLARE foo CURSOR FOR SELECT DISTINCT TRNum FROM MyTransaction ORDER BY TRNum OPEN foo FETCH NEXT FROM foo INTO @Currtran SELECT @LastTran = @CurrTran - 1 WHILE @@FETCH_STATUS = 0 BEGIN IF @CurrTran <> @LastTran + 1 SELECT 'Found Missing: ' + convert(varchar, @LastTran + 1) SELECT @LastTran = @CurrTran FETCH NEXT FROM foo INTO @Currtran END CLOSE Foo DEALLOCATE FOO

Hope this helps.

Article Information
Article Title:
Article Author: Scott Mitchell
Article URL:

Copyright 2018 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers