Sigh. I am once again running into issues with duplicates.
This is what I am getting when I run the query below. I want to get the count/sum by Product. Where am I going wrong?
Select
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,
Checks_Number= Case When ah.TransactionType='LDNC' Then Count(ah.AccountSequenceNumber) Else '0' End,
Checks_Dollar= Case When ah.TransactionType='LDNC' Then SUM(ah.TransactionAmount) Else '0' End,
Widget_Number= Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then Count(ah.AccountSequenceNumber) Else '0' End,
Widget_Dollar= Case When ah.TransactionType='CHK' AND ah.FocusTellerId='6009' Then SUM(ah.TransactionAmount) Else '0' End,
Phone_In_Person_Number= Case When ah.TransactionType='CHK' AND ah.FocusTellerId <>'6009' Then Count(ah.AccountSequenceNumber) Else '0' End,
Phone_In_Person_Dollar= Case When ah.TransactionType='CHK' AND ah.FocusTellerID <>'6009' Then SUM(ah.TransactionAmount) Else '0' End
From
Loan ln
Join AccountHistory ah
On ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber
Where
ln.LoanType In ('5','8','34','55','69','89','97','99','103','104','108')
AND ah.TransactionType In ('CHK','LDNC')
AND ah.EntryDate Between '05/01/2019' AND '05/31/2019'
Group By
ah.TransactionType,
ah.FocusTellerId,
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