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

ID is the transaction ID
AMNT is the money transaction amount
Date is the date of transaction
ClientID is our clients ID
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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alcsoftAuthor Commented:
Just to ensure you are not confuse
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
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?

alcsoftAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jan FranekCommented:

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"

Open in new window

this probably does it

however, this type of processing is really best done in procedural / program code

the performance will probably be v.poor due to the bad design of the code... two columns should have be used #
for a transaction type and a transaction identifier...

Select *
  from yourtable as a
Inner Join (
Select tranid,clientid
      ,sum(case when trantype = 1 then amnt else 0 end) as amnt
      ,sum(case when trantype = 1 then amnt else 0 end) as canc
select x.*
      ,substr(code,1,1) as trantype
      .substr(code,2,5) as Tranid
from yourtable as x
) as Y
group by clientid,tranid
     sum(case when trantype = 1 then amnt else 0 end)
      - sum(case when trantype = 1 then amnt else 0 end)
   <> 0
) as z
on a.clientid=z.clientid
and substr(a.code,2,5)=z.tranid
order by a.clientid,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
>jan franck

as with most accounting systems i am assuming that you can't limit this to just single rows with the identifier and must allow for multiple occurrences of the same "transaction id"
alcsoftAuthor Commented:
I had to change the code to meet my requirements  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.