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