asked
update statement with a datediff on max date
I'm trying to update field1 in tablename1 to 1 when the date in tablename2 field2 is less than or equal to 24 months from today. However, I have multiple records per id and so I want the maximum date for the group of ids to use in the rule comparing to todays date. The below is what I have done which does not work. For example if there are 3 records for id = 1, and there are 3 different dates for the records, I want to use the most recent date in my datediff function to see if the most recent date is within the last 24 months. Any ideas? update tablename1 set field1 = 0 update tablename1 set field1 = 1 from tablename1 a, tablename2 b where
a.id =
b.id and datediff(month, max(b.field2), getdate()) <= 24
One more question - your solution works well if I run from within SSMS. I can also run from within the SMSS query with an exec statement but if I execute it from within a stored procedure, I get the following error: 'No column name was specified for column 1 of 'b'' What do I need to do to run this from within a stored procedure?
Please check your proc, there must be some issue there. It is working fine for me. CREATE PROCEDURE usp_TestProc AS BEGIN ;WITH CTE(Id,Datedifference) AS ( SELECT id, DateDiff(mm,max(Date),getdate()) as datedifference FROM [dbo].[TableName2] Group by id ) Update A SET Field=1 FROM TableName1 A INNEr JOIN CTE B ON
A.id=
B.id WHERE B.Datedifference <=12 END GO
Hi Anne, You can try using this script: ;WITH CTE(Id,Datedifference) AS ( SELECT id, DateDiff(mm,max(Date),getdate()) as datedifference FROM [dbo].[TableName2] Group by id ) Update A SET Field=1 FROM TableName1 A INNEr JOIN CTE B ON
A.id=
B.id WHERE B.Datedifference <=12 You are using the wrong script to get the data. 1- Aggregates are used in Select, having. 2- In DateDiff use: mm/dd/yy I used this data ![alt text][1] [1]: /storage/temp/1362-anne.jpg
14 People are following this question.
