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