SQL Query aggregate may not appear in WHERE clause
Use the HAVING
as hinted by the error message, which requires a GROUP BY
:
SELECT
SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount,
M1.BillingID, M2.Name,
M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold,
DATEDIFF(d, MIN(BillingDate),GETDATE()) as DaysLate
FROM
Invoices M1
LEFT JOIN
ClientAccounts M2 ON M1.BillingID = M2.ID
WHERE
InvoiceTotal <> AmountApplied
AND
M2.DelinquentDaysThreshold > DATEDIFF(d, MIN(BillingDate),GETDATE())
GROUP BY
M1.BillingID, M2.Name,
M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold,
DATEDIFF(d, MIN(BillingDate),GETDATE())
HAVING
(SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold
Use "Having" for use with aggregate functions.
Heres a link: http://msdn.microsoft.com/en-us/library/ms180199.aspx