[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Search a table for non-canceled data.

Posted on 2010-03-29
9
Medium Priority
?
625 Views
Last Modified: 2013-11-15
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.
0
Comment
Question by:alcsoft
7 Comments
 

Author Comment

by:alcsoft
ID: 28997633
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
0
 
LVL 27

Expert Comment

by:tliotta
ID: 29009109
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
0
 

Author Comment

by:alcsoft
ID: 29011053
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 14

Expert Comment

by:Jan Franek
ID: 30594109

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

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1500 total points
ID: 31329706
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
 from
(
select x.*
      ,substr(code,1,1) as trantype
      .substr(code,2,5) as Tranid
from yourtable as x
) as Y
group by clientid,tranid
having
     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,a.date
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1500 total points
ID: 31329818
>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"
0
 

Author Closing Comment

by:alcsoft
ID: 32776616
I had to change the code to meet my requirements  
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question