I need to resolve duplicate payments

Question

I have a table that has the following fields:

Acct_ID, Payment, Proc_date

I am putting together a report where there are 2 conditions to take a value:

1) it has to be <0
2) there is no offset value for the same date, i.e. no positive same value for the same date.

I thought to sum payment values up and only take values < 0 but this did not give me the right results. Here are examples:

Scenario 1:

Acct_ID          Payment          Proc_Date

123456           -200                 10/15/2015

123456            200                  10/15/2015

123456             -1.04                 10/15/2015

in that case, I need to take only -1.04 record. the other 2 cancel each other. the -200 is offset by the 200 since they are for the same day and same account.

Senario 2: 

Acct_ID          Payment          Proc_Date

123456           -200                 10/15/2015

123456            0.42                  10/15/2015

123456             -1.13                 10/15/2015

123456             0.71                 10/15/2015

in that case, I need to take values -200 and -1.13. the other 2 values are >0.

How can I accomplish that, I really need quick help since I have to show this report in 2 days.

thanks

Answer 1

Try the following:

SELECT Payments.Acct_Id, Sum(Payments.Payment) AS Total, Payments.Proc_Date
FROM Payments
GROUP BY Payments.Acct_Id, Payments.Proc_Date, Abs(Payments.Payment)
Having Sum(Payments.Payment) < 0

Matthias Kläy, Kläy Computing AG

Answer 2

but this will not work in scenario 2, since it will add up all values and I will get one record out which is -200 other 3 records value will be zero. I should get the following out of scenario 2:

-200 and -1.13 as 2 distinct rows.

I also wanted to add a third scenario:

Scenario 3:

Acct_ID          Payment          Proc_Date

123456           -200                 10/15/2015

123456            200                  10/15/2015

123456             -200                 10/15/2015

123456             200                10/15/2015

123456             -200                10/15/2015

there are 5 records 2 with -200 which will be offset with the other 2 with positive 200 and I should get out of this set, only one record of -200.

Answer 3

but this will not work in scenario 2, since it will add up all values and I will get one record out which is -200 other 3 records value will be zero. I should get the following out of scenario 2:

-200 and -1.13 as 2 distinct rows.

I also wanted to add a third scenario:

Scenario 3:

Acct_ID          Payment          Proc_Date

123456           -200                 10/15/2015

123456            200                  10/15/2015

123456             -200                 10/15/2015

123456             200                10/15/2015

123456             -200                10/15/2015

there are 5 records 2 with -200 which will be offset with the other 2 with positive 200 and I should get out of this set, only one record of -200.

Matthias' answer works for all of your scenarios.  Please see the following:

My test table uses a couple of more columns including Scenario and RecordID.  I also changed the dates for each scenario.  However, the results are as you require.  Here is my SQL which is very similar to what Matthias provided:

SELECT alsPayment.Acct_ID, Sum(alsPayment.Payment) AS SumPayment, alsPayment.Proc_Date, alsPayment.Scenario, First(alsPayment.RecordID) AS RecordID1
FROM alsPayment
GROUP BY alsPayment.Acct_ID, alsPayment.Proc_Date, alsPayment.Scenario, Abs([Payment])
HAVING (((Sum(alsPayment.Payment))<0))
ORDER BY alsPayment.Proc_Date, First(alsPayment.RecordID);

Answer 4

First of all, thank you so much for taking the time and putting the effort into this. I really appreciate it. This is a brilliant solution. I used it and it worked for the most part, however, it seems that it need a little tweak that I am not sure how to do it. Running through the ones that gave me trouble as per my scenario, I found that they were all resolve except the following:

Acct_ID          Payment          Proc_Date

123456           -5000                 10/27/2015

123456            -5000                 10/27/2015

the query is adding up both values and I am getting only one record with -10000.

what I need to see is 2 rows each of -5000 on the report.

Just to give you background, I am doing reconciliation between 2 tables, source and Target.

All the scenarios I listed above were from the source table, I need to match record for recod in the target. in this case, the target side is showing 2 rows with -5000 each. but the source is showing one row with -10000. this causes disparity in my results. Is there away to tweak this a little? I love this solution it will work perfect if this issue if resolved.

thank you again so so much.

Answer 5

This new requirement pretty much changes the game, and we need another approach.

First, we count the number of negative payments for which there is no corresponding positive payment:

SELECT Payments.Acct_Id, 
       Payments.Proc_Date, 
       -Abs([Payment]) AS Amount, 
       -Sum(Sgn([Payment])) AS PaymentCount
FROM Payments
GROUP BY Payments.Acct_Id, Payments.Proc_Date, Abs([Payment])
HAVING Sum(Sgn([Payment])) < 0;

We save this query under the name "PaymentCount".

Second, we need what is called a Tally table. This is a table, just call it "Tally", that has one field of type Long, call it "No". This field "No" is the primary key of the table.

Fill this tally table with the numbers 1, 2, 3, .... up to the maximum possible number of negative payments without corresponding positive payment. You find this number by checking the query "PaymentCount"; it is the maximum of the field "PaymentCount" (it does not matter if you fill in more numbers into the Tally table. For example, you can fill in the numbers 1, 2, 3, ..., 10 even if the maximum of PaymentCount is only 2 in your examples).

Third, get the desired result with the following query:

SELECT PaymentCount.Acct_Id, 
       PaymentCount.Proc_Date, 
       -[Amount] AS Payment
FROM Tally, PaymentCount
WHERE PaymentCount.PaymentCount >= Tally.No
ORDER BY PaymentCount.Acct_Id, PaymentCount.Proc_Date, -[Amount];

Matthias Kläy, Kläy Computing AG

Answer 6

Thank you so much Matthias, you are a life saver. this works just fine.