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?

Binu,

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.
Sean

Read Other SQL Guru Questions