When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
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?

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


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