I need to create a procedure that will update a score for an employee based upon targets whihc are found in a look up table.
I have two tables:
TALK_TARGET table has multiple records per START DATE because of the UPPER & LOWER LIMITS, as well as having multiple START DATES. The primary key for the TLK_TARGET is:
The EMPLOYEE table has a primary Key of:
For scoring the START_DATE has to be less than or equal to the TRANS_DATE for scoring.
I have a TRANS_DATE of 2001-10-01
I have the following START_DATE: 2001-11-01
For this record I would have to use the START_DATE: 2001-09-01 on exclude 2001-11-01 and 2001-08-01.
I also have to check the TALK_TARGET table and compare the various UPPER & LOWER limits to determine the TALK_SCORE for the TALK_TARGET to update the TALK_SCORE on the EMPLOYEE table
Does anyone know how I would create the Update statement??
I am totally lost??? HELP!!!!!
Shaun de Souza
I'm not sure that I fully understand your question. But can this
UPDATE EMPLOYEE SET TALK_SCORE = ( SELECT TALK_SCORE FROM TALK_TARGET WHERE START_DATE =
( SELECT MAX(START_DATE) FROM TALK_TARGET WHERE START_DATE <= '2001-10-01'));
Hope that this give you some hint!
Click Here to Expand Forum to Full Width