select statement with max function

Question

I am trying to write a statement which will show me clients who did not pickup more than 2 months i already have update procedure for it and it works as it must be

update Clients
set StatusID=4
from (SELECT P.ClientID, MAX(p.PickupDate) MaxPickupDate
      FROM Pickup P
      group by P.Clientid) P
     join Clients C on P.ClientID= C.ClientID
where   C.StatusID in (1, 2)and C.WIC=0
      AND P.MaxPickupDate<DATEADD(month,-2,GETDATE());

I have other select

select P.ClientID
   ,LastName+' '+FirstName as Name 
   ,Address
   ,max(p.PickupDate)
from Pickup P
join Clients C on P.ClientID= C.ClientID
where max(p.PickupDate)<DATEADD(month,-2,GETDATE())
group by p.clientid, lastname + ' ' + firstname,address

it supposed to show me only clients who did not do pickup for more that 2 months but it throwing me error

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

looks like it doesn't like this line where max(p.PickupDate)<DATEADD(month,-2,GETDATE())Any Ideas how to fix this problem?

Use `HAVING` instead of `WHERE`. http://stackoverflow.com/q/287474/139010 and http://stackoverflow.com/q/6319183/139010 — Matt Ball, Mar 17 '13 at 23:52

score 1 · Accepted Answer · answered Mar 17 '13 at 23:57

1

use HAVING instead of WHERE since the logic needs to be applied after the aggregation is done and not before:

select P.ClientID
   ,LastName+' '+FirstName as Name 
   ,Address
   ,max(p.PickupDate)
from Pickup P
join Clients C on P.ClientID= C.ClientID
group by p.clientid, lastname + ' ' + firstname,address
HAVING max(p.PickupDate)<DATEADD(month,-2,GETDATE())

answered Mar 17 '13 at 23:57

Mike Monteiro

1,427
1
14
21

i was trying this approach but it throwing me error `Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'group'.` – Andrey Mar 17 '13 at 23:59

select statement with max function

1 Answers1