Update based on MAX() from join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update based on MAX() from join

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-08-13 : 12:39:52

How can I get this to work? Client is the parent table to meterdata which has multiple records based on customer_no.

I want to set the Client.Spindle_Utility to 1 if the MAX(MeterData.TimeStamp) column is < 14 days old, otherwise I want to set it to zero.

UPDATE Client SET
	Spindle_Utility = CASE WHEN MAX(MeterData.TimeStamp) is null OR DATEDIFF(d,MAX(MeterData.TimeStamp),GETDATE()) <= 14 THEN 1 ELSE 0 END
FROM
	Client
	LEFT JOIN meterdata ON meterdata.Customer_No = Client.Customer_No

TIA!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 13:05:52

[code]
UPDATE c
SET Spindle_Utility =
CASE WHEN x.[TimeStamp] is NULL OR DATEDIFF(d,MAX(x.[TimeStamp]),GETDATE()) <= 14 THEN 1
ELSE 0
END
FROM Client c
LEFT JOIN (
SELECT Customer_No, max([TimeStamp]) [TimeStamp]
FROM meterdata
GROUP BY
Customer_No
) x
On c.Customer_No = x.Customer_No[/code]

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2009-08-13 : 13:20:58

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources