Hello.
I am running a query with the code below. The intent of this query is to get a count by LoanType, LoanTypeINCDE, and LoanTypeDescription. However, it is returning several totals for each.
How do I just get a count for each of the loan types? Thanks.
Select
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Purchase_Count_2019 = (Select Sum(Case When ah.TransactionType='DPA' Then 1 Else 0 End) From AccountHistory ah Where ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber AND ah.EntryDate Between '11/01/2019' AND '11/30/2019')
From
Loan ln
Join LoanTypeDefinitions ltd
On ltd.LoanType=ln.LoanType
Where
ln.LoanType In ('5','8','34','55','69','89','97','99','103','104','105','108')
Hi i think this will help
Purchase_Count_2019 = (Select Sum(Case When ah.TransactionType='DPA' Then 1 Else 0 End) From AccountHistory ah Where ah.MemberNumber=ln.MemberNumber AND ah.AccountNumber=ln.LoanNumber AND ah.EntryDate Between '11/01/2019' AND '11/30/2019')
Make this instead of inline sub query
A separate table
Join
And sum
Select
ln.LoanType,
ltd.LoanTypeINCDE,
ltd.LoanTypeDescription,
Sum(case .......) As purchase_count_2019
From
( Select TransactionType ,
MemberNumber , AccountNumber from AccountHistory where
Where EntryDate Between '11/01/2019' AND '11/30/2019') ah
Join Loan in on
Thank you for responding, I appreciate the feedback.
Since the AccountHistory table is so large, I wanted to run the subquery. I did it the normal way, by joining the tables and it took about 5 minutes to run.
But I got my result. Thanks again.
hi
thats one way to do it
when you have a large SQL Query
the part where it takes a long time
usually put it into Temp tables # tables with indexes
and then use it in Joins
