SQLTeam.com | Weblogs | Forums

SUM 4 X greater than actual w/ Join

#1

Hello.

The SQL statement below brings back all of the correct information however when I joined the LoanProperty table, the XP2_Transactions_Purchase and the XP2_Transaction_Balance_Transfer totals were exactly 4 times greater than the actual amount.

Where am I going wrong here? Thank you.

Select
ah.MemberNumber,
ah.AccountNumber,
ln.OpenDate,
Days_Since_Opened=DateDiff(dd,ln.OpenDate,GetDate()),
LoansPQ_LoanNumber = (Select Case When lp.ItemName='OXCUDLAPP' Then lp.LoanPropertyValue else 'None Listed' End From LoanProperty lp Where lp.MembershipKey=ah.MembershipKey AND lp.LoanNumber=ah.AccountNumber AND lp.ItemName='OXCUDLAPP' AND lp.CloseDate Is Null),
Product=Case When ln.LoanType IN ('5','8') Then 'Business Platinum'
When ln.LoanType IN ('34','55') Then 'Platinum Points'
When ln.LoanType IN ('69','89','103','104','108') Then 'Platinum'
When ln.LoanType IN ('97','99') Then 'VISA Signature'
Else 'other'
End,
XP2_Transactions_Purchase=SUM (Case When ah.TransactionType='DPA' Then ah.TransactionAmount else '0' End),
XP2_Transactions_Balance_Transfers=SUM(Case When ah.TransactionType='CHK' Then ah.TransactionAmount else '0' End)

From
AccountHistory ah
Join Loan ln
On ln.MembershipKey=ah.MembershipKey AND ln.LoanNumber=ah.AccountNumber
Join LoanProperty lp
On lp.MembershipKey=ah.MembershipKey AND lp.LoanNumber=ah.AccountNumber

Where
ah.EntryDate Between '12/01/2018' AND '03/31/2019'

Group By
ah.MemberNumber,
ah.AccountNumber,
ln.OpenDate,
Case When ln.LoanType IN ('5','8') Then 'Business Platinum'
When ln.LoanType IN ('34','55') Then 'Platinum Points'
When ln.LoanType IN ('69','89','103','104','108') Then 'Platinum'
When ln.LoanType IN ('97','99') Then 'VISA Signature'
Else 'other'
End,
ln.LoanNumber,
ah.MembershipKey

#2

Hard to say without seeing the data, but possibly you have multiple rows in the Loan table and/or LoanProperty table for each MembershipKey and/or AccountNumber in the AccountHistory table.

#3

Thanks for the response. I figured it out. I had to use another table that didn't have a bunch of duplicates.