Link to home
Create AccountLog in
Avatar of kloner
kloner

asked on

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]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of kloner
kloner

ASKER

Thank you so much for your help!