Full Question: Which payments in any month and year are more than twice the average for that month and year
(i.e. compare all payments in Oct 2004 with the average payment for Oct 2004? Order the results by the date of the payment. You will need to use the date functions.)
My attempt at sql
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
SET @EndDate = DATEADD(mm, 1, @StartDate)
SELECT checkNumber, amount AS avg_payment, paymentDate
WHERE paymentDate BETWEEN '2004-10-01' AND '2004-10-31'
AND amount > 2 * (SELECT AVG(amount)
GROUP BY paymentDate, checkNumber, amount)
This type of problem is easily handled using window functions:
SELECT p.checkNumber, p.amount, p.paymentDate FROM (SELECT p.*, AVG(p.amount) OVER (PARTITION BY YEAR(p.paymentDate), MONTH(p.PaymentDate)) as avg_ym FROM Payments p ) p WHERE p.paymentDate BETWEEN '2004-10-01' AND '2004-10-31' AND p.amount > 2 * avg_ym;
As mentioned in a comment, your query doesn't work first because there is no correlation clause and second because it will return an error because too many rows are returned.