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)
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:
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:
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:
Hope this helps.