Can we assume then this:
1) Every negative amount has a corresponding positive amount
2) you will only ever get positive amounts in the output
3) A negative row will always negate a positive row that falls before it
(either using objectid or date)
If so this should work:
select *
from test b
where b.amount > 0
and b.objectid not in (
select a.objectid
from test a, test b
where a.amount > 0
and b.amount < 0
and (a.OBJECTID < b.OBJECTID
and a.ITEM_CODE = b.ITEM_CODE
and (a.AMOUNT + b.AMOUNT) = 0))
If you want to use date instead of objectid then change this line:
and (a.OBJECTID < b.OBJECTID
To this:
and (a.ITEM_DATE < b.ITEM_DATE
Pat Cummings
-----Original Message-----
From: Panagopoulos Chris
[mailto:oracledba-ezmlmshield-x30197836.[Email address protected]
Sent: Friday, April 28, 2006 1:07 PM
To: LazyDBA Discussion
Subject: RE: Eliminating offsetting entries
Hi Pat
Unfortunately the numbers are not sequential. The rows could be:
1 10 4/15/06 +10.00
2 20 4/15/06 +30.00
3 10 4/17/06 -10.00
4 30 4/17/06 +25.00
5 10 4/18/06 +10.00
6 25 4/19/06 +20.00
7 10 4/20/06 +10.00
For code 10 I only want entries 5 and 6 since 1 and 3 cancel. I am
trying to do it with a PL/SQL block where I separate the pluses and
minuses into two separate tables and then walk the plus table and
eliminate a corresponding negative entry, but it is cumbersome.
Thanks
Chris
-----Original Message-----
From: Cummings Patrick J Contr AU/SCIE
[mailto:oracledba-ezmlmshield-x38547548.[Email address protected]
Sent: Friday, April 28, 2006 12:44 PM
To: LazyDBA Discussion
Subject: RE: Eliminating offsetting entries
Need a little more information here:
1) By offsetting entries are you only talking about when they occur
(using Object Id) sequentially next to each other?
2) Is it possible for row 2 and 3 to offset each other? If not how do
you know where to start?
If you are just using consecutive numbers then this should work:
select a.*
from test a, test b
where (a.OBJECTID + 1 = b.OBJECTID
and a.ITEM_CODE = b.ITEM_CODE
and a.ITEM_DATE = b.ITEM_DATE
and a.AMOUNT = b.AMOUNT)
or (a.OBJECTID - 1 = b.OBJECTID
and a.ITEM_CODE = b.ITEM_CODE
and a.ITEM_DATE = b.ITEM_DATE
and a.AMOUNT = b.AMOUNT)
If you are truly just looking for things to cancel out then try this:
select a.*
from test a, test b
where (a.OBJECTID + 1 = b.OBJECTID
and a.ITEM_CODE = b.ITEM_CODE
and a.ITEM_DATE = b.ITEM_DATE
and (a.AMOUNT + b.AMOUNT) != 0)
or (a.OBJECTID - 1 = b.OBJECTID
and a.ITEM_CODE = b.ITEM_CODE
and a.ITEM_DATE = b.ITEM_DATE
and (a.AMOUNT + b.AMOUNT) != 0)
NOTE: you may want to multiply the amount columns by 100 and truncate
(i.e. trunc(a.AMOUNT*100) ) to avoid float math problems.
Pat Cummings
-----Original Message-----
From: Panagopoulos Chris
[mailto:oracledba-ezmlmshield-x81064041.[Email address protected]
Sent: Friday, April 28, 2006 10:52 AM
To: LazyDBA Discussion
Subject: Eliminating offsetting entries
I have the following table:
Object Id
Item code
Item date
Amount
Does anybody have a script that will eliminate offsetting amounts for
the same item code and date and just produce duplicates?
For example:
1 10 04/28/06 +10.00
2 10 04/28/06 -10.00
3 10 04/28/06 +10.00
4 10 04/28/06 +10.00
Only want to see the last two entries.
Thanks for your help.
Chris
This message (including any attachments) is confidential and intended
solely for the use of the individual or entity to whom it is addressed,
and is protected by law. If you are not the intended recipient, please
delete the message (including any attachments) and notify the originator
that you received the message in error. Any disclosure, copying, or
distribution of this message, or the taking of any action based on it,
is strictly prohibited. Any views expressed in this message are those
of the individual sender, except where the sender specifies and with
authority, states them to be the views of Vanguard Health Systems.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
This message (including any attachments) is confidential and intended
solely for the use of the individual or entity to whom it is addressed,
and is protected by law. If you are not the intended recipient, please
delete the message (including any attachments) and notify the originator
that you received the message in error. Any disclosure, copying, or
distribution of this message, or the taking of any action based on it,
is strictly prohibited. Any views expressed in this message are those
of the individual sender, except where the sender specifies and with
authority, states them to be the views of Vanguard Health Systems.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page