b.le b.le - 4 days ago 8
SQL Question

Correlated Sub-Query; payments in any month and year are more than twice the average for that month and year

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
FROM Payments
WHERE paymentDate BETWEEN '2004-10-01' AND '2004-10-31'
AND amount > 2 * (SELECT AVG(amount)
FROM Payments
GROUP BY paymentDate, checkNumber, amount)


Database here : http://richardtwatson.com/dm6e/images/general/ClassicModels.png

What am i doing wrong?

Answer

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.

Comments