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:
<![CDATA[
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:
<![CDATA[
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:
<![CDATA[
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
