Thank you that worked perfectly and now of course, the requirement changes a little. I have been asked where this Expected_Close = 8) is in the where clause.
Could someone help- They want that field to just incremental example when the report runs at the end of the month on the last day to insert 9 and then the next month 10
is that possible? Thanks everyone
SELECT TOP (100) PERCENT tblObject_1.Name AS Agent, COUNT(dbo.tblLoan.Object_ID) AS Count, SUM(dbo.tblProcessingFile.LoanAmount_MIP_FF) AS Volume
FROM dbo.tblLoan INNER JOIN
dbo.tblObject ON dbo.tblLoan.Object_ID = dbo.tblObject.Object_ID INNER JOIN
dbo.tblProcessingFile ON dbo.tblLoan.Object_ID = dbo.tblProcessingFile.Loan_ID INNER JOIN
dbo.tblObject AS tblObject_1 ON dbo.tblLoan.ContactOwnerID = tblObject_1.Object_ID INNER JOIN
dbo.tblDomain ON tblObject_1.Domain_ID = dbo.tblDomain.Object_ID INNER JOIN
dbo.tblObject AS tblObject_2 ON dbo.tblLoan.Source_ID = tblObject_2.Object_ID
WHERE (dbo.tblDomain.DomainCategoryTypeID = 2) AND (dbo.tblProcessingFile.Funded IS NOT NULL) AND (dbo.tblLoan.Expected_Close_Year = 2012) AND
(dbo.tblLoan.Expected_Close = 9)
GROUP BY tblObject_1.Name
ORDER BY Count DESC, Agent