• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Joining a Table On a Valid Record

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

Open in new window

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.

TIA
0
Clif
Asked:
Clif
  • 22
  • 15
  • 5
  • +2
1 Solution
 
mrjoltcolaCommented:
I'd try a join to a subquery which has "SELECT TOP 1" and "order by Inv_Date desc"

Which version of SQL Server?
0
 
ClifAuthor Commented:
SQL Server 2008

The problem with your suggestion (if I understand it) is that there may be other valid invoice records after the Inv_Date in question (as well as before).  My example was just that of an invoice that been reversed effectively creating three invoices where it should normally be one.

What I want is some way of ignoring the reversed invoices (the incorrect one and the reversal of the incorrect one).  Also, in the above example, the cost center was the reason for the reversal, but there could be other reasons, such as the wrong Job_No.  In that case the records might look like this:
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       456789  1235        2011-03-27  I           12345.67  ABC

Open in new window

In any case, the first two records cancel each other out and should be ignored.
0
 
ClifAuthor Commented:
Here is an expanded example where there are three *true* invoices, but because of a reversal, there are five records.
Cust_Code  Job_No  Invoice_No  Inv_Date    Trans_Type    Amount  Cost_Ctr
ACME       987654  1023        2011-02-15  I            1642.89  ABC
ACME       987654  1196        2011-03-27  I           12345.67  ABC
ACME       987654  1234        2011-03-27  C           12345.67  ABC
ACME       456789  1235        2011-03-27  I           12345.67  ABC
ACME       987654  1389        2011-04-06  I           22345.67  ABC

Open in new window

Once again, the two invoice records that need to be ignored are #1196 and #1234
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
mrjoltcolaCommented:
Don't you mean 1023 should be ignored? It was the earliest. The last one for that job is 1234
0
 
mrjoltcolaCommented:
If I understand you right, and you can order by invoice_no, this should work.  You could create a view for this.

select a.* from invoice a join
  (select job_no, MAX(invoice_no) invoice_no from invoice  where trans_type = 'I' group by job_no) b
on a.invoice_no = b.invoice_no  

Open in new window


If the ordering is not based on invoice_no, but on date, then we can modify this...

0
 
ClifAuthor Commented:
No, it's not the earliest invoice that needs to be ignored.  Niether is the last invoice (either by invoice number or by date) is the only valid invoice.

In the last example I posted, there are three valid invoices (1023, 1235, and 1389).  Invoice# 1196 was entered wrong (for whatever reason).  Invoice #1234 reversed (corrected) #1196, and then #1235 is what #1196 should have been.
0
 
mrjoltcolaCommented:
Lets stick with a single invoice chain (I C I) to simplify the sample data.

In your original question you said "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 please correct me.

Also, in your last data sample, the cost centers don't change, and I assume, if Job_No identifies an invoice chain (I C I), then the data is wrong in that regard too, as the I C I chain I see overlaps dates and Job_Nos (987654 and 456789) . Which of these 3 records is the single chain?

Cust_Code  Job_No  Invoice_No  Inv_Date    Trans_Type    Amount  Cost_Ctr
ACME       987654  1023        2011-02-15  I            1642.89  ABC
ACME       987654  1196        2011-03-27  I           12345.67  ABC
ACME       987654  1234        2011-03-27  C           12345.67  ABC
ACME       456789  1235        2011-03-27  I           12345.67  ABC
ACME       987654  1389        2011-04-06  I           22345.67  ABC

Open in new window

0
 
ClifAuthor Commented:
You are correct, and I have verified with our account manager, that reversals (the I C I chain) will always appear on the same date.

The reason for the reversal might be, as in my last example, an incorrect Job No was typed in.  More likely, however not always, the wrong cost center was initially selected.  It is also possible, but *very* unlikely, that it could be a combination of incorrect Job No and incorrect cost center.

Since you brought up (accurately) the "I C I" chain, let me throw one more monkey-wrench...  Not every trans type 'C' indicates a reversal process.  "C" simply means "Credit" ("I" means "Charge").  Although I will have to verify for sure, it would seem to me that there could be a "C I C" chain as well, but we'll worry about that at a later time.
0
 
mrjoltcolaCommented:
So it seems if the Job_No is wrong, it would be impossible to identify the chain, or is there another field that would help?
0
 
ClifAuthor Commented:
I was wrong.  I just had a short tutorial with our acount manager and she informed me that the only change will be the cost center.
0
 
GhunaimaCommented:
Try This

 
select a.* from invoice a join
  (select job_no, MAX(case trans_type when 'C' then invoiceno else 0 end) invoiceno from invoice group by job_no) b
on a.job_no=b.job_no where a.invoice_no > b.invoice_no

Open in new window

0
 
ste5anSenior DeveloperCommented:
hi,

while a solution is possible for your sample data:
DECLARE @Sample TABLE 
  (  
    Cust_Code CHAR(4), 
    Job_No INT, 
    Invoice_No INT, 
    Inv_Date DATE,
    Trans_Type CHAR(1),
    Amount MONEY,
    Cost_Ctr CHAR(3)
  ) ;

INSERT INTO @Sample VALUES
  ('ACME', 987654, 1023, '2011-02-15','I', 1642.89, 'ABC'),
  ('ACME', 987654, 1196, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1234, '2011-03-27','C', 12345.67, 'ABC'),
  ('ACME', 456789, 1235, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1389, '2011-04-06','I', 22345.67, 'ABC') ;
  
SELECT *
FROM @Sample ;

WITH Data AS
  (
    SELECT *,
           COUNT(*) OVER (PARTITION BY Cost_Ctr, Job_No, Amount) AS Cnt
    FROM @Sample
  )
  SELECT *
  FROM Data 
  WHERE Cnt = 1;

Open in new window


A general solution is not possible, because you don't know/track which reversal belongs to which invoice. And you cannot match corresponding invoice/reversal row pairs by the given data. E.g. the same customer may buy always for the same amount. E.g.
DECLARE @Sample TABLE 
  (  
    Cust_Code CHAR(4), 
    Job_No INT, 
    Invoice_No INT, 
    Inv_Date DATE,
    Trans_Type CHAR(1),
    Amount MONEY,
    Cost_Ctr CHAR(3)
  ) ;

INSERT INTO @Sample VALUES
  ('ACME', 987654, 1023, '2011-02-15','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1196, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1234, '2011-03-27','C', 12345.67, 'ABC'),
  ('ACME', 456789, 1235, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1389, '2011-04-06','I', 22345.67, 'ABC') ;
  
SELECT *
FROM @Sample ;

WITH Data AS
  (
    SELECT *,
           COUNT(*) OVER (PARTITION BY Cost_Ctr, Job_No, Amount) AS Cnt
    FROM @Sample
  )
  SELECT *
  FROM Data 
  WHERE Cnt = 1;

Open in new window

In the above sample you cannot longer make a correct association.

 A slightly modification helps:
DECLARE @Sample TABLE 
  (  
    Cust_Code CHAR(4), 
    Job_No INT, 
    Invoice_No INT, 
    Inv_Date DATE,
    Trans_Type CHAR(1),
    Amount MONEY,
    Cost_Ctr CHAR(3),
    Reversal_To_Invoce INT
  ) ;

INSERT INTO @Sample VALUES
  ('ACME', 987654, 1023, '2011-02-15','I', 12345.67, 'ABC', NULL),
  ('ACME', 987654, 1196, '2011-03-27','I', 12345.67, 'ABC', NULL),
  ('ACME', 987654, 1234, '2011-03-27','C', 12345.67, 'ABC', 1196),
  ('ACME', 456789, 1235, '2011-03-27','I', 12345.67, 'ABC', NULL),
  ('ACME', 987654, 1389, '2011-04-06','I', 22345.67, 'ABC', NULL) ;
  
SELECT *
FROM @Sample ;

SELECT *
FROM   @Sample 
WHERE  NOT Invoice_No IN 
 (
   SELECT Reversal_To_Invoce 
   FROM   @Sample 
   WHERE  NOT Reversal_To_Invoce IS NULL
  )
AND    Reversal_To_Invoce IS NULL ;

Open in new window

0
 
ClifAuthor Commented:
I really don't care which reversal is attached to which invoice (well I do, but not in this particular case).  All I care about is that a customer is not showing more than is owed (or owes us more than they should).

Once again, as I stated in my last post, the account manager has confirmed that the only "wrong" data that the reversal will change is the cost center.  The customer number, job code and amount will all remain the same.

I will try all of your recommendations as soon as I can work them into the real query.
0
 
ste5anSenior DeveloperCommented:
hi,

sure? So we're talking about the sums only?

DECLARE @Sample TABLE
    (
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Sample
VALUES  ( 'ACME', 987654, 1023, '2011-02-15', 'I', 12345.67, 'ABC' ),
        ( 'ACME', 987654, 1196, '2011-03-27', 'I', 12345.67, 'ABC' ),
        ( 'ACME', 987654, 1234, '2011-03-27', 'C', 12345.67, 'ABC' ),
        ( 'ACME', 456789, 1235, '2011-03-27', 'I', 12345.67, 'ABC' ),
        ( 'ACME', 987654, 1389, '2011-04-06', 'I', 22345.67, 'ABC' ) ;
  
SELECT  *
FROM    @Sample ;

SELECT  Cust_Code ,
        Job_No ,
        SUM(CASE WHEN Trans_Type = 'I' THEN Amount
                 ELSE -Amount
            END) AS Amount ,
        Cost_Ctr
FROM    @Sample
GROUP BY Cust_Code ,
        Job_No ,
        Cost_Ctr ;

Open in new window

0
 
ClifAuthor Commented:
ste5an,

If I understand your query suggestion, not every 'C' is a reversal.  

Let's see if I can explain it again, because I think I got muddled somewhere.

A reversal is three transactions all to the same customer code, job number and amount.  The first entry may or may not be a different date and will be a different cost center from the other two.  The second and third entries will always have the same date and cost center.  The invoice number may or may not be continuous in any of the three entries (that is to say, they may not necessarily be '1000', '1001', '1002')
0
 
ste5anSenior DeveloperCommented:
hi,

just to get sure we're talking about the same: You have here a kind of T-account and need a cancellation of an entry?

What I don't understand: Why do we need three entries for a reversal? When I take a look at your original data

  ('ACME', 987654, 1023, '2011-02-15','I', 1642.89, 'ABC'),
  ('ACME', 987654, 1196, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1234, '2011-03-27','C', 12345.67, 'ABC'),
  ('ACME', 456789, 1235, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1389, '2011-04-06','I', 22345.67, 'ABC')

Open in new window


Then there is only on cancellation entry for a wrong invoice. So the third entry is the reversal to the second one. btw, why has your cancellation entry the same date?
0
 
ClifAuthor Commented:
The join I'm needing is not to create the reversal.  It's for a report that shows expenses and revenue.  The reversal, because of the way the accounting software does things, creates three records where there really is only one.

We don't need three entries, there are three entries...
1 - The original entry on a specific cost center.
2 - The realization that the original entry was posted to an incorrect cost center and a credit issued.
3 - The corrected entry to the proper cost center.

The first entry's date is some arbitrary date.  The fact that it was the same as the other two entries was purely coincidence.  The second and third entries will always have the same date, as the correction and reissue will always occur at the same time.

'C' does not refer specifically to a reversal.  It merely means that the invoice was a credit.
0
 
ClifAuthor Commented:
So far none appear to be valid solutions, at least as far as I can tell.

I think, in your examples, you have mixed up my data samples.  This data sample should be correct:
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

Open in new window

In all the examples you've shown so far, all the cost centers are the same.  This is not what it is in reality.  What the reversal does is adjust the cost center so that (in the example above), the first entry was the incorrect entry.  The second is the credit to the incorrect first.  The third is the cost center corrected entry.
0
 
ste5anSenior DeveloperCommented:
When not C every is an cancellation record, then your problem is NOT solvable as closed T-SQL expression. You can only use a cursor to loop over your records when and only when you can order your records, e.g. by Invoice_No and your system can guarantee that a reversal is using the C(invoice_no)->I(invoice_no+1) pattern:

DECLARE @Sample TABLE
    (
      Rec_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;
    
INSERT  INTO @Sample
VALUES  ( 1, 'ACME', 987654, 1000, '2011-03-27', 'I', 12345.67, 'ABC' ),
        ( 2, 'ACME', 987654, 1234, '2011-03-27', 'C', 12345.67, 'ABC' ),
        ( 3, 'ACME', 987654, 1235, '2011-03-27', 'I', 12345.67, 'XYZ' ) ; 

DECLARE @Result TABLE
    (
      Rec_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Result
        SELECT  *
        FROM    @Sample ;
        

SELECT  L.*
INTO    #Reversal
FROM    @Sample L
        LEFT JOIN @Sample R ON L.Invoice_No = R.Invoice_No - 1
WHERE   L.Trans_Type = 'C'
        AND R.Trans_Type = 'I' ;          
-- A*             
DELETE TOP ( 1 )
FROM    @Result
FROM    @Result R1
        INNER JOIN #Reversal R2 ON R1.Amount = R2.Amount
                                   AND R1.Cost_Ctr = R2.Cost_Ctr
                                   AND R1.Cust_Code = R2.Cust_Code
                                   AND R1.Job_No = R2.Job_No
WHERE   R1.Trans_Type = 'I' ;

DELETE  FROM @Result
FROM    @Result R1
        INNER JOIN #Reversal R2 ON R1.Invoice_No = R2.Invoice_No ; 
-- B*
SELECT  *
FROM    @Result ;

DROP TABLE #Reversal ;

Open in new window


This is an incomplete sample. You need to implement a loop between A* and B* with to logic shown above: Remove one matching I record from the result. Remove the reversal C record from the result. Remove the reversal record from the temporary table. Loop as long you have a record. btw, this can be done by using a cursor.
0
 
GhunaimaCommented:
Try this SQL

 
select a.* from invoice a join
  (select job_no, inv_date, MAX(case trans_type when 'C' then invoice_no else 0 end) invoice_no from invoice group by job_no, inv_date) b
on a.job_no=b.job_no and a.inv_date=b.inv_date where a.invoice_no > b.invoice_no

Open in new window

0
 
ClifAuthor Commented:
Ghunaima,
Almost thought you had it.  I added a test debit (I) record and it still worked.  Then I added a test credit (C) record, but it was not displayed.

Here is the sample data I used:
DECLARE @Invoice TABLE 
  (  
    Cust_Code CHAR(4), 
    Job_No INT, 
    Invoice_No INT, 
    Inv_Date DATE,
    Trans_Type CHAR(1),
    Amount MONEY,
    Cost_Ctr CHAR(3)
  ) ;

INSERT INTO @Invoice VALUES
  ('ACME', 987654, 1023, '2011-02-15','I', 1642.89, 'ABC'),
  ('ACME', 987654, 1196, '2011-03-27','I', 12345.67, 'DEF'),
  ('ACME', 987654, 1234, '2011-03-27','C', 12345.67, 'DEF'),
  ('ACME', 456789, 1235, '2011-03-27','I', 12345.67, 'ABC'),
  ('ACME', 987654, 1389, '2011-04-06','I', 22345.67, 'ABC'), 
  ('ACME', 987654, 1420, '2011-04-08','C', 1685.12, 'ABC') 
;

Open in new window

The results are:
Cust_Code    Job_No    Invoice_No    Inv_Date    Trans_Type    Amount    Cost_Ctr
ACME         456789    1235          2011-03-27  I             12345.67  ABC
ACME         987654    1023          2011-02-15  I             1642.89   ABC
ACME         987654    1389          2011-04-06  I             22345.67  ABC

Open in new window

The results should have been:
Cust_Code    Job_No    Invoice_No    Inv_Date    Trans_Type    Amount    Cost_Ctr
ACME         987654    1023          2011-02-15  I             1642.89   ABC
ACME         456789    1235          2011-03-27  I             12345.67  ABC
ACME         987654    1389          2011-04-06  I             22345.67  ABC
ACME         987654    1420          2011-04-08  C             1685.12   ABC

Open in new window

0
 
ste5anSenior DeveloperCommented:
hi,

Just to repeat myself: A closed T-SQL query is not possible as the information is not stored which three rows make a reversal.
0
 
GhunaimaCommented:
I thought that the only credit entries are reversals so need to be repressed Try this query for your purpose

select * from invoice 
where invoice_no not in (select invoice_no from (select a.invoice_no, b.Invoice_No cInvoice_no, a.Job_No, b.Job_No cJob_No, a.Trans_Type, b.Trans_Type cTrans_Type, a.Amount, b.Amount cAmount, a.Cost_Ctr, b.Cost_Ctr cCost_Ctr, a.cust_code, b.cust_code ccust_code from invoice a join invoice b on a.Job_No=b.Job_No and a.Inv_Date=b.Inv_Date and a.Amount=b.Amount where a.Trans_Type='I' and b.Trans_Type='C' and a.Invoice_No<b.Invoice_No) x)
  and invoice_no not in (select cinvoice_no from (select a.invoice_no, b.Invoice_No cInvoice_no, a.Job_No, b.Job_No cJob_No, a.Trans_Type, b.Trans_Type cTrans_Type, a.Amount, b.Amount cAmount, a.Cost_Ctr, b.Cost_Ctr cCost_Ctr, a.cust_code, b.cust_code ccust_code from invoice a join invoice b on a.Job_No=b.Job_No and a.Inv_Date=b.Inv_Date and a.Amount=b.Amount where a.Trans_Type='I' and b.Trans_Type='C' and a.Invoice_No<b.Invoice_No) x)

Open in new window

0
 
ste5anSenior DeveloperCommented:
Okay,

It seems that I was wrong about the single T-SQL solution, there is one, I'm not quite confident about it, but it seems to work:

DECLARE @Invoice TABLE
    (
      Row_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Invoice
VALUES  ( 1, 'ACME', 987654, 1023, '2011-02-15', 'I', 1642.89, 'ABC' ),
        ( 2, 'ACME', 987654, 1195, '2011-03-23', 'I', 12345.67, 'DEF' ),
        ( 3, 'ACME', 987654, 1196, '2011-03-24', 'I', 12345.67, 'DEF' ),
        ( 4, 'ACME', 987654, 1197, '2011-03-25', 'I', 12345.67, 'DEF' ),
        ( 5, 'ACME', 987654, 1234, '2011-03-27', 'C', 12345.67, 'DEF' ),
        ( 6, 'ACME', 456789, 1235, '2011-03-27', 'I', 12345.67, 'ABC' ),
        ( 7, 'ACME', 987654, 1389, '2011-04-06', 'I', 22345.67, 'ABC' ),
        ( 8, 'ACME', 987654, 1420, '2011-04-08', 'C', 1685.12, 'ABC' ),        
        ( 11, 'ACME', 1, 2343, '2011-03-23', 'I', 1, 'A01' ),
        ( 12, 'ACME', 1, 2344, '2011-03-24', 'I', 1, 'A01' ),
        ( 13, 'ACME', 1, 2345, '2011-03-25', 'I', 1, 'A01' ),
        ( 14, 'ACME', 1, 2346, '2011-03-27', 'C', 1, 'A01' ),
        ( 15, 'ACME', 2, 2347, '2011-03-27', 'I', 1, 'A02' ) ;

SELECT  *
FROM    @Invoice ;

WITH    Reversal
          AS ( SELECT   L.*
               FROM     @Invoice L
                        INNER JOIN @Invoice R ON L.Amount = R.Amount
                                                 AND L.Cost_Ctr <> R.Cost_Ctr
                                                 AND L.Cust_Code = R.Cust_Code
                                                 AND L.Invoice_No = R.Invoice_No - 1
                                                 AND L.Job_No <> R.Job_No
                                                 AND L.Trans_Type <> R.Trans_Type
                                                 AND L.Trans_Type = 'C'
             ),
        Candidate
          AS ( SELECT   I.* ,
                        R.Invoice_No AS Reversal_Invoice_No ,
                        ROW_NUMBER() OVER ( PARTITION BY R.Invoice_No ORDER BY I.Invoice_No ) AS Grp_Row_No
               FROM     @Invoice I
                        INNER JOIN Reversal R ON R.Amount = I.Amount
                                                 AND R.Cost_Ctr = I.Cost_Ctr
                                                 AND R.Cust_Code = I.Cust_Code
                                                 AND R.Job_No = I.Job_No
                                                 AND R.Trans_Type <> I.Trans_Type
             ),
        FirstCandidate
          AS ( SELECT   *
               FROM     Candidate
               WHERE    Grp_Row_No = 1
             )
    SELECT  *
    FROM    @Invoice
    WHERE   NOT Invoice_No IN ( SELECT  Invoice_No
                                FROM    Reversal )
            AND NOT Invoice_No IN ( SELECT  Invoice_No
                                    FROM    FirstCandidate ) ;

Open in new window


But: It can only remove one arbitrary record per reversal as we don't know which Invoice_No is affected. And the detection of what a reversal is may fail. As it is possible that two rows with the defined attributes exist which are independent transactions.

mfG
--> stefan <--
0
 
ClifAuthor Commented:
Don't you hate when the customer gives you one piece of information at a time?

So, here's the latest description of a reversal sequence...

Specific to a reversal (I C I Process):
1. All transactions, beginning to end, will occur on the same date.
2. All transactions in the ICI process will have the same customer code and job number.
3. Invoice numbers, while increasing though the ICI process, won't necessarily be sequential.
4. The ICI process is always used to change cost centers, so the first two transactions will always have the same cost center while the tird will always have a different cost center.
5. The invoice amount will remain the same through the ICI process.

Now for the monkeywrench...

6. The final transaction in the process may not result in a single transaction.  There may be more than one.  This doesn't happen very often, but does happen.
   a. The total invoice amount summed will always equal the ionvoice amount for the initial transaction.
   b. None of the cost centers for the final transactions will be the same as the initial cost center.

I understand that the final item (the monkeywrench) may make the whole thing impossible (if the rest hasn't already), so let's ignore it for the moment.
0
 
ste5anSenior DeveloperCommented:
hi,

when this is the final logic, then the system stinks, oops, sorry, has a bad code smell.

You  wrote earlier that not every C entry is part of a reversal. Is this still true?

When this is the case, there is another new possibility of getting the wrong entries. E.g.

DECLARE @Invoice TABLE
    (
      Row_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Invoice
VALUES  ( 0, 'ACME', 1, 0, '2011-03-27', 'I', 1, 'A01' ),
        ( 1, 'ACME', 1, 1, '2011-03-27', 'I', 1, 'A01' ),
        ( 2, 'ACME', 1, 2, '2011-03-27', 'I', 1, 'A01' ),
        ( 3, 'ACME', 1, 3, '2011-03-27', 'C', 1, 'A01' ),
        ( 4, 'ACME', 1, 4, '2011-03-27', 'I', 1, 'A02' ),
        ( 5, 'ACME', 1, 5, '2011-03-27', 'I', 1, 'A03' ),
        ( 6, 'ACME', 1, 6, '2011-03-27', 'I', 1, 'A04' ),
        ( 7, 'ACME', 1, 7, '2011-03-27', 'C', 1, 'A01' ),
        ( 8, 'ACME', 1, 8, '2011-03-27', 'I', 1, 'A04' ),
        ( 9, 'ACME', 1, 9, '2011-03-27', 'I', 1, 'A04' ) ;

WITH    Reversal
          AS ( SELECT   I1.Invoice_No AS Invoice_No_I1 ,
                        C.Invoice_No AS Invoice_No_C ,
                        I2.Invoice_No AS Invoice_No_I2
               FROM     @Invoice I1
                        INNER JOIN @Invoice C ON I1.Inv_Date = C.Inv_Date
                                                 AND I1.Cust_Code = C.Cust_Code
                                                 AND I1.Job_No = C.Job_No
                                                 AND I1.Invoice_No < C.Invoice_No
                                                 AND I1.Amount = C.Amount
                                                 AND I1.Cost_Ctr = C.Cost_Ctr
                        INNER JOIN @Invoice I2 ON C.Inv_Date = I2.Inv_Date
                                                  AND C.Cust_Code = I2.Cust_Code
                                                  AND C.Job_No = I2.Job_No
                                                  AND C.Invoice_No < I2.Invoice_No
                                                  AND C.Amount = I2.Amount
                                                  AND C.Cost_Ctr <> I2.Cost_Ctr
               WHERE    I1.Trans_Type = 'I'
                        AND C.Trans_Type = 'C'
                        AND I2.Trans_Type = 'I'
             )
    SELECT  *
    FROM    Reversal ;

Open in new window


This is really, really, hmm, bad. As you need to pick one association per Invoice_No_C and remove the corresponding Invoice_No_I1 row. Which has an effect on the subsequent Invoice_No_C groups. When you pick (0, 3, 4) for C=3 then you cannot pick (0, 7, ?) for C = 7 as the invoice_no = 0 is alread removed from the result. This really sounds again like a cursor/loop solution.
And to mention it again: You need to pick a randomly these possible rows. Not what I'd like to have in my account records.
0
 
ClifAuthor Commented:
Yes, it is still true thast a 'C' transaction is not specific to a reversal.  It is merely part of the reversal process.  
1. Initial invoice created (I)
2. Credit applied to invoive (C)
3. Corrected invoice created (I)

I will have to test your possible solution this afternoon.

0
 
GhunaimaCommented:
Did you try the second query I posted ??
0
 
ste5anSenior DeveloperCommented:
hi,

now after some thinking about my last example: my possible solution fails also in this case, for the same reason as posted in the last example. So I'm back again at the start: There is no closed T-SQL solution possible.

Imho you should verify with your account manager if the last example I've posted is possible or not. When it is possible then you should show him the consequences:

DECLARE @Invoice TABLE
    (
      Row_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Invoice
VALUES  ( 0, 'ACME', 1, 0, '2011-03-27', 'I', 1, 'A01' ),
        ( 1, 'ACME', 1, 1, '2011-03-27', 'I', 1, 'A01' ),
        ( 2, 'ACME', 1, 2, '2011-03-27', 'I', 1, 'A01' ),
        ( 3, 'ACME', 1, 3, '2011-03-27', 'C', 1, 'A01' ),
        ( 4, 'ACME', 1, 4, '2011-03-27', 'I', 1, 'A02' ),
        ( 5, 'ACME', 1, 5, '2011-03-27', 'I', 1, 'A03' ),
        ( 6, 'ACME', 1, 6, '2011-03-27', 'I', 1, 'A04' ),
        ( 7, 'ACME', 1, 7, '2011-03-27', 'C', 1, 'A01' ),
        ( 8, 'ACME', 1, 8, '2011-03-27', 'I', 1, 'A04' ),
        ( 9, 'ACME', 1, 9, '2011-03-27', 'I', 1, 'A04' ) ;

DECLARE @Result TABLE
    (
      Row_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Result
        SELECT  *
        FROM    @Invoice ;      

SELECT  I1.Invoice_No AS Invoice_No_I1 ,
        C.Invoice_No AS Invoice_No_C ,
        I2.Invoice_No AS Invoice_No_I2
INTO    #Reversal
FROM    @Invoice I1
        INNER JOIN @Invoice C ON I1.Inv_Date = C.Inv_Date
                                 AND I1.Cust_Code = C.Cust_Code
                                 AND I1.Job_No = C.Job_No
                                 AND I1.Invoice_No < C.Invoice_No
                                 AND I1.Amount = C.Amount
                                 AND I1.Cost_Ctr = C.Cost_Ctr
        INNER JOIN @Invoice I2 ON C.Inv_Date = I2.Inv_Date
                                  AND C.Cust_Code = I2.Cust_Code
                                  AND C.Job_No = I2.Job_No
                                  AND C.Invoice_No < I2.Invoice_No
                                  AND C.Amount = I2.Amount
                                  AND C.Cost_Ctr <> I2.Cost_Ctr
WHERE   I1.Trans_Type = 'I'
        AND C.Trans_Type = 'C'
        AND I2.Trans_Type = 'I' ;    

DELETE  FROM @Result
WHERE   Invoice_No IN ( SELECT  Invoice_No_C
                        FROM    #Reversal ) ;

DECLARE @Invoice_No_I1 INT ,
    @Invoice_No_C INT ;

WHILE EXISTS ( SELECT   1
               FROM     #Reversal ) 
    BEGIN
--*
        SELECT TOP 1
                @Invoice_No_I1 = Invoice_No_I1 ,
                @Invoice_No_C = Invoice_No_C
        FROM    #Reversal
        ORDER BY Invoice_No_I1 ASC ,
                Invoice_No_C ASC ;
--*
        DELETE  FROM @Result
        WHERE   Invoice_No = @Invoice_No_I1 ;
        
        DELETE  FROM #Reversal
        WHERE   Invoice_No_I1 = @Invoice_No_I1
                OR Invoice_No_C = @Invoice_No_C ;
    END ;

SELECT  *
FROM    @Result ;

DROP TABLE #Reversal ;

Open in new window


The selection of which rows you remove from the result is merely random. See the SELECT in marked (--*) area. You can use any ORDER BY combination of (ASC, ASC), (ASC,DESC), (DESC,ASC) or (DESC,DESC). You may also consider using to order by NEWID() instead of the actual columns. It still fulfills the requirements.

When your additional requirement should be correct: That the last transaction in the I-C-I chain can consist of multiple rows, then you have a further problem. How to identify if a C row has corresponding I rows and which of them are the correct ones, if any. While it's not important which records form the last I transactions, you need to extract the fact that the C row is part of a reversal. This problem is hard to solve as it is NP complete. The problem itself is known Knapsack problem:

 http://en.wikipedia.org/wiki/Knapsack_problem

So that in the end, you cannot guarantee correctness as you may have identified to much C rows as part of an reversal. Which leads me to my first answer: You need to store/track the information which rows form a reversal. btw, in Germany we have a think called GOB which is an abbreviation for (translated) Principles of Correct Accounting. These principles prescribe that you store this kind of information.

0
 
ClifAuthor Commented:
Sorry G, but I missed it.  I did try and you are still close, but an ICI sequence was accepted as a reversal, but it wasn't since ther copst center was the same for all three tranactions.

See my sample data:
  ('ACME', 987654, 1023, '2011-02-15','I', 1642.89, 'ABC'),
  ('ACME', 987654, 1196, '2011-03-27','I', 12345.67, 'DEF'),  --\
  ('ACME', 987654, 1234, '2011-03-27','C', 12345.67, 'DEF'),  ---- Reversal
  ('ACME', 456789, 1235, '2011-03-27','I', 12345.67, 'ABC'),  --/
  ('ACME', 987654, 1389, '2011-04-06','I', 22345.67, 'ABC'), 
  ('ACME', 987654, 1420, '2011-04-08','I', 1685.12, 'ABC'),   --\ 
  ('ACME', 987654, 1422, '2011-04-08','C', 1685.12, 'ABC'),   ---- Not a reversal as the cost center is the same for all three
  ('ACME', 987654, 1425, '2011-04-08','I', 1685.12, 'ABC'),   --/
  ('ACME', 987654, 1430, '2011-04-09','I', 1685.12, 'ABC') 

Open in new window

0
 
ClifAuthor Commented:
ste5an,
Which leads me to my first answer: You need to store/track the information which rows form a reversal.
I would like to, but the data is from a third party application.  I'm merely preparing reports based on the data it keeps in a database.
0
 
ste5anSenior DeveloperCommented:
hi,

Then you can only report a sum per cost center. This is a correct sum as long as you don't have these multiple transactions as last I transaction. In that case - due to the complications introduced by the Knapsack problem - you may identify to many C entries as reversals. In this case you cannot even guarantee a correct sum.

mfG
--> stefan <--
0
 
GhunaimaCommented:
Query I posted returned the result

Row_No      Cust_Code      Job_No      Invoice_No      Inv_Date      Trans_Type      Amount      Cost_Ctr
1      ACME      987654      1023      2011-02-15      I      1642.89      ABC
4      ACME      456789      1235      2011-03-27      I      12345.67      ABC
5      ACME      987654      1389      2011-04-06      I      22345.67      ABC
8      ACME      987654      1425      2011-04-08      I      1685.12      ABC
9      ACME      987654      1430      2011-04-09      I      1685.12      ABC

which seems correct. In your data Invoice_No field is unique and in ascending order so I used that for comparison of reversal. It does not matter is cost centers are changed.
0
 
anillucky31Commented:
Here is solution for your problem. Let me know if this works for you

I have attached the final code


I have splitted you problem INTO 2 portions

1. I have TO GET ALL the record whenever there IS credit ON same cost_ctr (AS ALL C are NOT reversesal, Reversal are ONLY WHEN cost_ctr changes)
we can GET those record WITH following query.


SELECT a.* FROM invoice  a
INNER JOIN  (
SELECT Cust_Code,  Job_No, dateAdd(d,datediff(d,0,inv_date),0) inv_date, amount, cost_ctr
FROM   invoice
GROUP BY Cust_Code, Job_No, dateAdd(d,datediff(d,0,inv_date),0), amount, cost_ctr
HAVING COUNT(*) > 2
) b ON  a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0 AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr


2. We have TO eliminate reversal cases. we can achive this WITH query written below. (this will filter cash credit AS well ON same cost_ctr, thats why i have written query above TO GET those required sepertly)




SELECT * FROM invoice WHERE invoice_No NOT IN (

SELECT a.Invoice_No FROM  invoice a
INNER JOIN invoice b
ON a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0 AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr AND a.Trans_Type = 'I' AND b.Trans_Type = 'C'

union

SELECT a.Invoice_No FROM  invoice a
INNER JOIN invoice b
ON a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0  AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr AND a.Trans_Type = 'C' AND b.Trans_Type = 'I'

)


Now you can combine you query TO GET result AS folllow.

SELECT * FROM invoice WHERE invoice_No NOT IN (

SELECT a.Invoice_No FROM  invoice a
INNER JOIN invoice b
ON a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0 AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr AND a.Trans_Type = 'I' AND b.Trans_Type = 'C'

union

SELECT a.Invoice_No FROM  invoice a
INNER JOIN invoice b
ON a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0  AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr AND a.Trans_Type = 'C' AND b.Trans_Type = 'I'

)


UNION

SELECT a.* FROM invoice  a
INNER JOIN  (
SELECT Cust_Code,  Job_No, dateAdd(d,datediff(d,0,inv_date),0) inv_date, amount, cost_ctr
FROM   invoice
GROUP BY Cust_Code, Job_No, dateAdd(d,datediff(d,0,inv_date),0), amount, cost_ctr
HAVING COUNT(*) > 2
) b ON  a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0 AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr






 
SELECT * FROM invoice WHERE invoice_No NOT IN (

SELECT a.Invoice_No FROM  invoice a
INNER JOIN invoice b 
ON a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0 AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr AND a.Trans_Type = 'I' AND b.Trans_Type = 'C'

union

SELECT a.Invoice_No FROM  invoice a
INNER JOIN invoice b 
ON a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0  AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr AND a.Trans_Type = 'C' AND b.Trans_Type = 'I'

)


UNION 

SELECT a.* FROM invoice  a
INNER JOIN  (
SELECT Cust_Code,  Job_No, dateAdd(d,datediff(d,0,inv_date),0) inv_date, amount, cost_ctr
FROM   invoice
GROUP BY Cust_Code, Job_No, dateAdd(d,datediff(d,0,inv_date),0), amount, cost_ctr
HAVING COUNT(*) > 2
) b ON  a.Cust_Code = b.Cust_Code AND a.Job_No = b.Job_No AND DATEDIFF(d,a.inv_date, b.inv_date) = 0 AND  a.Amount = b.Amount
AND a.Cost_Ctr = b.Cost_Ctr

Open in new window

0
 
ste5anSenior DeveloperCommented:
When I run anillucky31's solution against my data, which is valid due to Clif's description, it does not recognize the correct reversals...
DECLARE @Invoice TABLE
    (
      Row_No INT ,
      Cust_Code CHAR(4) ,
      Job_No INT ,
      Invoice_No INT ,
      Inv_Date DATE ,
      Trans_Type CHAR(1) ,
      Amount MONEY ,
      Cost_Ctr CHAR(3)
    ) ;

INSERT  INTO @Invoice
VALUES  ( 0, 'ACME', 1, 0, '2011-03-27', 'I', 1, 'A01' ),
        ( 1, 'ACME', 1, 1, '2011-03-27', 'I', 1, 'A01' ),
        ( 2, 'ACME', 1, 2, '2011-03-27', 'I', 1, 'A01' ),
        ( 3, 'ACME', 1, 3, '2011-03-27', 'C', 1, 'A01' ),
        ( 4, 'ACME', 1, 4, '2011-03-27', 'I', 1, 'A02' ),
        ( 5, 'ACME', 1, 5, '2011-03-27', 'I', 1, 'A03' ),
        ( 6, 'ACME', 1, 6, '2011-03-27', 'I', 1, 'A04' ),
        ( 7, 'ACME', 1, 7, '2011-03-27', 'C', 1, 'A01' ),
        ( 8, 'ACME', 1, 8, '2011-03-27', 'I', 1, 'A04' ),
        ( 9, 'ACME', 1, 9, '2011-03-27', 'I', 1, 'A04' ) ;
        
SELECT  a.*
FROM    @Invoice a
        INNER JOIN ( SELECT Cust_Code ,
                            Job_No ,
                            DATEADD(d, DATEDIFF(d, 0, inv_date), 0) inv_date ,
                            amount ,
                            cost_ctr
                     FROM   @Invoice
                     GROUP BY Cust_Code ,
                            Job_No ,
                            DATEADD(d, DATEDIFF(d, 0, inv_date), 0) ,
                            amount ,
                            cost_ctr
                     HAVING COUNT(*) > 2
                   ) b ON a.Cust_Code = b.Cust_Code
                          AND a.Job_No = b.Job_No
                          AND DATEDIFF(d, a.inv_date, b.inv_date) = 0
                          AND a.Amount = b.Amount
                          AND a.Cost_Ctr = b.Cost_Ctr ;

Open in new window

0
 
ClifAuthor Commented:
ste5an,

You are right, and I am puzzeled as to why this is.

It would seem that, from my sample data I posted (05/06/11 10:12 AM, ID: 35706985), if I switch the ICI cost centers from 'DEF', 'DEF', 'ABC', to 'ABC', 'ABC', 'DEF' the query fails to produce the correct results.

It seems odd that just changing the cost centers would cause it to fail.

I will request that EE Mods reopen the question.
0
 
anillucky31Commented:
Clif what should be out put for this input given by Ste5an ?

      
Row_No   Cust_Code  Job_No  Invoice_No      Inv_Date    Trans_Type Amount  Cost_Ctr
  0        ACME             1       0       2011-03-27       I             1       A01
  1       ACME             1       1       2011-03-27       I             1      A01
  2       ACME             1       2       2011-03-27       I             1      A01
  3       ACME             1       3       2011-03-27       C             1       A01
  4       ACME             1       4       2011-03-27       I             1       A02
  5       ACME             1       5       2011-03-27       I             1       A03
  6       ACME             1       6       2011-03-27       I             1       A04
  7       ACME             1       7       2011-03-27      C             1       A01  
  8       ACME             1       8       2011-03-27       I             1       A04  
  9       ACME             1       9       2011-03-27       I             1       A04  
0
 
ste5anSenior DeveloperCommented:
hi,

take a look at Clif's message ID:35697982. Especially point 3:

3. Invoice numbers, while increasing though the ICI process, won't necessarily be sequential.

So lets assume my record with Row_No 3 is a reversal. Then you can only pick a random prior row (Row_No 0-2) to the according rules. There is no possibility to get a better, exacter match. The reporting based of this data cannot use the invoice number as it varies. This shows my example in message ID:35704632 when you use an ORDER BY NEWID() in the marked statement. Conclusion 1: Only aggregated reports make sense.

When we also take a look at Clif's point 6, then the problem get's even worse than that as we're need to solve the Knapsack problem to determine if a C record is part of a reversal. While we don't get false negatives, cause all real reversals will be identified, there is the risk of false positives, thus identifying C records as reversals which are none. Conclusion 2: With point 6, the problem is not solvable.

Q.E.D.?

mfG
--> stefan <--
0
 
ste5anSenior DeveloperCommented:
hi Clif,

can you post the entire table creation script including all indices and - when they exist - foreign keys?

0
 
ste5anSenior DeveloperCommented:
hi Clif,

another thought: There exists often a relation between Cust_Code, Job_No, Invoice_No and Cost_Ctr either an unexpressed relation or an unidentified functional dependency.

P.S.: I'm not quite sure, that we have all the relevant information...
0
 
anillucky31Commented:
ste5an you are right. This problem becomes unsolvable if we have sample data like i posted in previous post. We wont be able to determine Credit and reversal accurately.

Solution given by me will not work on sample data as in previous post. If in actual practice data is not like that it will work.

 
0
 
ClifAuthor Commented:
Clif what should be out put for this input given by Ste5an ?
Give the inputs, it looks like invoice numbers 2, 3 and 4 would be the ICI reversal sequence with invoice #4 being the only row of the sequence returned.

Then you can only pick a random prior row...
I wouldn't say it would be a random row, per se,  It's the first prior row matching the Cust_Code, Job_No, Inv_Date, Amount, and Cost_Ctr with a Trans_Type = "I"

Conclusion 2: With point 6, the problem is not solvable
Then ignore point 6 for the time being.  It's occurance is very rare and we may be able to get around it some other way.

can you post the entire table creation script...
Sorry, but company policy prohibits this.

P.S.: I'm not quite sure, that we have all the relevant information...
Actually I believe you do.  There really is nothing more to it than I've given you.  The reversal, on a simple grid system, works fine and is easy to see and interpret with human intuition.  The problem with the reversal is when I need to use the calculations to feed a joiin in a query.  What happens is, through the join, a reversal sequence produces records for cost centers that the job is not actually part of.  It should because, aside from the ICI sequence that indicates a reversal, they appear as valid records.
0
 
ClifAuthor Commented:
So, let's take a look at what a human would do with the ICI sequence...

1. Sequence through the records until a Trans_Type 'C' is discovered.

2. Look back on all valid prior records for the first record which has the same Cust_Code, Job_No, Inv_Date, Amount, and Cost_Ctr and has a Trans_Type = "I".  This suggests a potential reversal.

3. Look forward from the record found in step 1 until a record with the same Cust_Code, Job_No, Inv_Date, Amount, and has a Trans_Type = "I" but has a different Cost_Center.  This indicates a true reversal.  

4. If a true reversal is found, the records from step 1 and step 2 are invalid and are not to be returned.  Record found in step 3 is returned.

This is the process as a human would do it.  All I need it for it to be automated.

Note:  I cannot modify the existing tables in any way, shape, or form.  However I can create tables, views and even UDFs if needed.
0
 
ste5anSenior DeveloperCommented:
hi Clif,

Assume that the row with Row_No=3 is a reversal.

I wouldn't say it would be a random row, per se,  It's the first prior row matching the Cust_Code, Job_No, Inv_Date, Amount, and Cost_Ctr with a Trans_Type = "I"

Taking the the "first" prior row is merely random, as you defined:

3. Invoice numbers, while increasing though the ICI process, won't necessarily be sequential.

So you don't know which of the rows (Row_No 0-2) is the real row. Picking one arbitrary number does not change the fact that you're acting on a pure guess. Thus the conclusion: you cannot identify the correct row, thus the differences between the three rows is no longer carrying valid information, thus you cannot longer use the Inoivce_No for reporting. To repeat it: You can define that the first record is the correct, but this does not change the mathematical set problem that these three rows are identical by your prior, original definitions. Because the necessary information is not stored, neither in data or meta-data.

When you can ignore your point 6 then you can use my solution from post ID:35704632.

And now for the other problem:
4. If a true reversal is found

You cannot identify true reversals speaking of C records. You can only identify potential reversal C records. Having a real T-account schema in mind, the possibility of such case is rare (~ <0.01), but they still exist and maybe a problem. You and your account manager must be aware of this fact.

The only exact solution according to your give information would be to store the relation of the participating rows in your I-C-I  reversal schema.

When you and your account manager find a different solution, which you're sure it is exact, then you can be sure, that this means that your original requirements and definitions are either not complete or not exact.
0
 
ClifAuthor Commented:
So you don't know which of the rows (Row_No 0-2) is the real row.
Not true.  Presuming that Row# 3 is a reversal (Trans_Type = 'C'), then the FIRST prior row matching the criteria IS the reversed invoice.  It always is.

You can only identify potential reversal C records.
That's what I said in step 2 of the human process above.  It's not until you identify a potential reversal (finding a 'C' and then looking back until you find a corresponding 'I') that you can start forward to find the proper 'I' which is the true result of the reversal.

When you can ignore your point 6 then you can use my solution from post ID:35704632
I have tested it just now (not sure how I missed it before) and it seems to work.  But I'm sure you'll understand if I don't award points right away.  ;-)

So, let's presume for the moment that it works so far.  Is there no way, once a potential reversal is discovered as in Step 2 of the process above, to look at all the records after the 'C' record summing the amounts until (and if) a combination 'I' is found?  After all, all that's needed is to find the result and ignore the reversed records.
0
 
ste5anSenior DeveloperCommented:
hi Clif,

Presuming that Row# 3 is a reversal (Trans_Type = 'C'), then the FIRST prior row matching the criteria IS the reversed invoice.  It always is.

Consider this sequential process:

1. Inovice 0 is correctly made (Row_No=0).
2. Invoice 1 is incorrectly made, but will be reverted later (Row_No=1).
3. Invoice 2 is correctly made (Row_No=2).
4. Now the reversal C record is inserted (Row_No=3).

This is possible according to your rules especially as you said they don't need to be sequential invoice numbers.
0
 
ClifAuthor Commented:
I don't think I was clear enough about the sequential stuff.  Let me clarify...

While the invoice numbers don't have to be sequential, they are contiguous for a particular Cust_Code, Job_No, Inv_Date, Amount.  In other words, there are a lot of transactions going on from different departments and, in the process of a reversal, some other jobs might have invoices written taking up the invoice numbers within the ICI sequence.  However there will never be any transactions in between an ICI sequence.

This is a point I hadn't thought about before now (the "duh" moment), and I just clarified with our account manager.  She says, as far as she has seen to date (but she hesitates to say "always") it is been three transactions in a row.  If Row #3 is a Trans_Type 'C', you need look no further back than Row #2 to see if there is a matching 'I' signaling a potential reversal.  If row #2 is a matching 'I', then you need not look farther than Row #4 to see if it matches for a true reversal.  (Of course point 6 modifies this a touch, but is still the same type of thing)
0
 
ClifAuthor Commented:
BTW, I tested the last suggestion with true data and it took 20 minutes to complete.  It also returned the same number of records (4770) that are in the original invoice table.
0
 
ClifAuthor Commented:
BTW2:
Ignore my previous post (35747295).  I missed something in converting it to use the real table.  It's fixed and now runs fast and gives me less records than the source table.

Now to figure out if the results are correct.
0
 
ClifAuthor Commented:
Not sure how I missed grading this (or how EE missed nagging me about an open question).

Thanks for your help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 22
  • 15
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now