alcsoft
asked on
Search a table for non-canceled data.
Hi Experts
I have this table in DB2.
ID Code AMNT clientID Date
1 1A 1000 100 12/12/2009
2 1B 2000 101 12/12/2009
3 2A 300 100 8/16/2009
4 1C 100 105 09/20/2009
5 1A 300 100 08/15/2009
6 2C 500 103 06/16/2009
7 1C 500 103 05/12/2009
Where
ID is the transaction ID
AMNT is the money transaction amount
Date is the date of transaction
ClientID is our clients ID
Code:
1%, if we have 1 followed by anything that’s mean payment
2% if we have 2 followed by anything that’s cancelation of a payment
So if we have 2A transaction followed the 1A (With the same amount for the same client) then the first will cancel the second.
I want to see the following:
ID Code AMNT clientID Date
1 1A 1000 100 12/12/2009
2 1B 2000 101 12/12/2009
4 1C 100 105 09/20/2009
As you can see the transaction with ID 3 eats the transaction with 5 and 6 eats 7.
I want to do this in optimum way because the table is so big.
I have this table in DB2.
ID Code AMNT clientID Date
1 1A 1000 100 12/12/2009
2 1B 2000 101 12/12/2009
3 2A 300 100 8/16/2009
4 1C 100 105 09/20/2009
5 1A 300 100 08/15/2009
6 2C 500 103 06/16/2009
7 1C 500 103 05/12/2009
Where
ID is the transaction ID
AMNT is the money transaction amount
Date is the date of transaction
ClientID is our clients ID
Code:
1%, if we have 1 followed by anything that’s mean payment
2% if we have 2 followed by anything that’s cancelation of a payment
So if we have 2A transaction followed the 1A (With the same amount for the same client) then the first will cancel the second.
I want to see the following:
ID Code AMNT clientID Date
1 1A 1000 100 12/12/2009
2 1B 2000 101 12/12/2009
4 1C 100 105 09/20/2009
As you can see the transaction with ID 3 eats the transaction with 5 and 6 eats 7.
I want to do this in optimum way because the table is so big.
Okay, so "followed by" definitely refers to the 'Date' column, and transaction ID is merely a unique identifier that has nothing to do with sequencing. The positioning of rows in your sample table should not be taken as any indication that one row "follows" another -- the rows need to be ordered by 'Date' to know which transactions "follow" other transactions. Also, cancellations only affect equal payment transactions.
Can you verify if I understand correctly so far?
Tom
Can you verify if I understand correctly so far?
Tom
ASKER
Perfect.
You are right about the date.
The cancelation is based on three criteria’s.
- The client ID should be the same
- 2A will cancel 1A, 2B will cancel 1B, 2C will cancel 1C, and so on, but 2A will not cancel 1B for example.
- The payment amount should be the same.
If you can give me a query that shows the last payment without cancelation (i.e. the last 1X that isn’t followed by 2X, which match the above criteria’s ) that’s will be awesome
You are right about the date.
The cancelation is based on three criteria’s.
- The client ID should be the same
- 2A will cancel 1A, 2B will cancel 1B, 2C will cancel 1C, and so on, but 2A will not cancel 1B for example.
- The payment amount should be the same.
If you can give me a query that shows the last payment without cancelation (i.e. the last 1X that isn’t followed by 2X, which match the above criteria’s ) that’s will be awesome
select *
from YourTable t1
where not exists ( select 1
from YourTable t2
where t2.AMNT = t1.AMNT
and t2.Date >= t1.Date
and t2.clientID = t1.clientID
and t2.Code = "2" + right( t1.Code, 1 ) )
and left( t1.Code, 1 ) = "1"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to change the code to meet my requirements
ASKER
if we have another record in the table with folloing information
8 1C 400 100 04/12/2009
then the result will be
ID Code AMNT clientID Date
1 1A 1000 100 12/12/2009
2 1B 2000 101 12/12/2009
4 1C 100 105 09/20/2009
8 1C 400 100 04/12/2009