troubleshooting Question

Looping and updating records

Avatar of kloner
kloner asked on
Microsoft SQL Server 2005
2 Comments1 Solution167 ViewsLast Modified:
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]
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros