When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right
Print this page.
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.

Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article