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)