Eliminating Duplicates

1

Hello. I am stumped as to why I am receiving duplicates on this TSQL query

Select Distinct
ln.MemberNumber,
ln.LoanNumber,
ac.Balance,
Total_Change= Case When ah.EntryDate BEtween '03/01/2019' AND '03/31/2019' Then SUM(ah.PrincipalAmount)
Else '0'
End,
ac.LoanKey

From AllCalculationLoanSnapshot ac
Join Loan ln
On ln.MembershipKey=ac.MembershipKey AND ln.LoanKey=ac.LoanKey
Join AccountHistory ah
on ah.MembershipKey=ln.MembershipKey AND ah.AccountNumber=ln.LoanNumber

Where
ln.LoanType='5'

Group By
ln.MemberNumber,
ln.LoanNumber,
ac.Balance,
ah.EntryDate,
ac.LoanKey

I am getting this

MemberNumber LoanNumber Balance Total_Change LoanKey
XX46XX0 129 3591.62 -314.49 89019
XX46XX0 129 3591.62 0.00 89019
XX46XX0 129 3591.62 62.04 89019

Why is the Total Change not summing? Thanks.

2

Then EntryDate is different. You don't want to include EntryDate in your GROUP BY.

Also, it's best to do the SUM like this:
...
Total_Change = SUM(Case When ah.EntryDate BEtween '03/01/2019' AND '03/31/2019' Then ah.PrincipalAmount Else 0 End),
...

3

Inner sum should be removed, as sum within a sum is not allowed

4

Yeah, removed. Not supposed to be an inner SUM. The point was to move the CASE to within the SUM, not next to it.

5

Thanks to all. That did the trick! Appreciate it.