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
Oracle LazyDBA home page