I'm having a heck of a time with a join. The problem is that the joined table is an invoice table, and occassionally there will be reversals. The reversals show up as transactions and cause multiple records to show up when there should only be one.
I'm hoping that I don't have to post the whole query, but can get away with just showing an example of what I'm talking about.
Here is an example from the invoice table that's causing today's headache...
Cust_Code Job_No Invoice_No Inv_Date Trans_Type Amount Cost_Ctr
ACME 987654 1000 2011-03-27 I 12345.67 ABC
ACME 987654 1234 2011-03-27 C 12345.67 ABC
ACME 987654 1235 2011-03-27 I 12345.67 XYZ
The problem is that (in this particular instance) there was a posting to the wrong cost center. The first record (Invoice# 1000) was posted as an Invoice. Then it was later discovered that it was posted to the wrong cost center. So a credit was posted (Invoice# 1234) and the proper invoice to the correct cost center was posted right after (Invoice# 1235). The result of this is that there are three records showing up in the query where there should only be one.
When there is a reversal, it will always occur on the same day and will always be in the Trans_Type sequence of I C I where the last transaction is the valid one.
So my question is this...
How do I join to the invoice table making sure that, when there is a reversal, only the last record is recgnized?
Good luck and, as always, should you or any of your IM Force be caught or killed, the Secretary will disavow any knowledge of your action.