CODEWITHSUNDEEP
sqlsql-server-2008selectmax
Andrey
Andrey

Reputation: 1759

select statement with max function

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?

Upvotes: 0

Views: 763

Answers (1)

Mike Monteiro
Mike Monteiro

Reputation: 1457

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())

Upvotes: 1

Related Questions