Return a distinct amount only if duplicate

[EasyAim]

We have a table of payments associated with an "agent".

tblPayment:

AgentID PaymentID Amount AdjustmentID
30101 10001 $40.00 701
30101 10002 $50.00 902
30101 10003 $50.00 902
30101 10004 $50.00 902
30101 10005 $60.00 844

There should be only one payment for each "AdjustmentID" value, however we've discovered multiple payments applied to the same "AdjustmentID" (see Payments 1002, 1003, and 1004).

I need to run a special report to show how these duplicates have screwed up our data.

EXAMPLE #1:

I can do this one easily enough...

AgentID= 30101
SUM(Amount)= $250
SUM(duplicates)= $150
CORRECTED_VALUE = $250 - $150 = $100 (wrong)

Using:

SELECT SUM(a.Amount) FROM tblPayment a WHERE EXISTS
(SELECT b.AdjustmentID, b.Amount, count(*)
FROM tblPayment b
WHERE a.AdjustmentID = b.AdjustmentID AND
a.Amount = b.Amount AND b.AgentID = 30101
GROUP BY b.AdjustmentID, b.Amount HAVING count(*) > 1 )

However, when you subtract the duplicate sum from the total sum, you get $100 which is not the correct value. This is not what I need because removing all of the duplicates also removes the one correct payment (payment_id= 10002).

What I want is:

METHOD #1:

AgentID= 30101
Sum(Amount) = $250
Sum(duplicates) = $100 (with correct first payment not included)
CORRECT = $250 - $100 = $150

or

METHOD #2:

AgentID= 30101
Sum(Amount) = $250
Sum(duplicates) = $150
Sum(distinct_duplicate) = $50.00 (replacing the correct first payment)
CORRECT = $250 - $150 + $50 = $150

QUESTION: Can anyone figure out how to leave one payment in the summation (for method #1)
or create a whole different SQL statement to return the payment amount of each distinct AdjustmentID ONLY if duplicate payments were made on that AdjustmentID (for method #2).

 

[tlbroadbent]

This is just one possible solution. I approached this in four steps.

1) Find the duplicates.
2) Elminate the first payment from the duplicate sum.
3) Summarize all payment per AgentID.
4) Join the results of the previous steps in a final query.

I use three sub queries within the main query. I've color-code each sub query.

Select
q1.AgentID, q1.TotAmt, q2.SumDups,
(q1.TotAmt - q2.SumDups) As AdjAmt
From
--This sub query summarizes all payments by AgentID
(Select
t.AgentID,
Sum(t.Amount) As TotAmt
From tblPayment As t
Group By AgentID) As q1

Inner Join

--This sub query summarizes payments
--with duplicate AdjustmentIDs by AgentID
(Select
AgentID,
Sum(Amount) As SumDups,
Count(*) As RecCnt
From tblPayment a
Where PaymentID>
--This sub query helps eliminate the 1st AdjustmentID
--of the duplicated records
(Select min(PaymentID) From tblPayment
Where AgentID=a.AgentID
And AdjustmentID=a.AdjustmentID)
Group By AgentID
Having count(*)>1) As q2

On q1.AgentID=q2.AgentID Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[EasyAim]

Thanks Terry, this worked great. You didn't even have any typos in your SQL statement.

NEW PROBLEM:

My main statement got so convoluted I'm writing the intermediate results to a temp table.

How do I write ALL of the unique AgentID's into the temp table, but writing "0" (zero) for those agents that don't have any duplicates.

EXAMPLE:

30101 $100
30205 0
40224 0
...

The following only writes those Agents that HAVE a duplicate. I need all agents written.

SELECT q2.AgentID, q2.SumDups INTO #TempTable FROM
(SELECT t.AgentID FROM tblPayment t
GROUP BY AgentID
) as q1
INNER JOIN
(SELECT AgentID, SUM(Amount) as SumDups, Count(*) as RecCnt FROM tblPayment a WHERE
Payment_ID >
(SELECT MIN(Payment_ID) FROM tblPayment
WHERE AgentID = a.AgentID AND AdjustmentID = a.AdjustmentID
)
GROUP BY To_Account_ID
HAVING Count(*) > 1
) as q2
ON q1.To_Account_ID = q2.To_Account_ID

 

[tlbroadbent]

Want to add another sub query? Add or modify the code in green.

SELECT q0.AgentID, q2.SumDups INTO #TempTable FROM
(Select distinct AgentID From tblPayment) As q0
LEFT JOIN
((SELECT t.AgentID FROM tblPayment t
GROUP BY AgentID
) as q1
INNER JOIN
(SELECT AgentID, SUM(Amount) as SumDups, Count(*) as RecCnt FROM tblPayment a WHERE
Payment_ID >
(SELECT MIN(Payment_ID) FROM tblPayment
WHERE AgentID = a.AgentID AND AdjustmentID = a.AdjustmentID
)
GROUP BY To_Account_ID
HAVING Count(*) > 1
) as q2
ON On q1.AgentID=q2.AgentID)
On q0.AgentID=q1.AgentID Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.