Looping and updating records

Hi all,

I have gone blank!

I have a table of candidates, and in this table a DAYS_LAST_CONTACTED column. I also have a table called candidate_notes, and there are M:1 notes per candidate.

What I am trying to do, is create a nightly job, that loops through all candidates and populates the DAYS_LAST_CONTACTED field with ... you guessed it ... the last time (in days) when a candidate was contacts.

Am I right to say I should somehow SELECT all the candidates, and then somehow do a select on the NOTES table, and get the most recent date and calculate the diff??

Eg: To get the date diff for a candidate...

CAST(DATEDIFF(DD, (SELECT TOP 1 N.[DateLogged] FROM [TBL_BLUECARE_CANDIDATES_NOTES] N WHERE N.[EXT_REF_ID] = C.[EXT_REF_ID] ORDER BY N.[DateLogged] DESC), getDate()) AS INT)

Would be grateful for a solution.

Cheers EE!

SELECT [EXT_REF_ID] (PK)
      ,[Title]
      ,[First Name]
      ,[Surname]
      ,[Mobile]
      ,[Email]
      ,[Postcode]
      ,[DaysLastContacted]
FROM [TBL_BLUECARE_CANDIDATES]
 
 
SELECT [EXT_REF_ID] (FK)
      ,[DateLogged]
      ,[Initials]
      ,[Notes]
  FROM [TBL_BLUECARE_CANDIDATES_NOTES]

Select allOpen in new window