Answered by:
I need to resolve duplicate payments
Question

I have a table that has the following fields:
Acct_ID, Payment, Proc_dateI 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
Sunday, October 18, 2015 6:30 AM
Answers

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
Sunday, October 18, 2015 7:29 PM
All replies

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
 Proposed as answer by RunningManHD Sunday, October 18, 2015 2:34 PM
Sunday, October 18, 2015 8:01 AM 
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.
Sunday, October 18, 2015 1:03 PM 
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);
Sunday, October 18, 2015 2:31 PM 
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.
Sunday, October 18, 2015 3:58 PM 
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
Sunday, October 18, 2015 7:29 PM 
Thank you so much Matthias, you are a life saver. this works just fine.Sunday, October 18, 2015 7:54 PM