troubleshooting Question

Search a table for non-canceled data.

Avatar of alcsoft
alcsoft asked on
DatabasesDB2
7 Comments2 Solutions649 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros