Update Query Problem

[Shaun]

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:

EMPLOYEE table

TRANS_DATE DATE
EMPLOYEE_ID NUMBER(10)
CAMPAIGN_CODE CHAR(1)
PRODUCT_CODE CHAR(3)
TALK_TIME NUMBER(5)
TALK_SCORE NUMBER(3)


TALK_TARGET table

START_DATE DATE
CAMPAIGN_CODE CHAR(1)
PRODUCT_CODE CHAR(3)
TALK_TIME_UPPER_LIMIT NUMBER(5)
TALK_TIME_LOWER_LIMIT NUMBER(5)
TALK_SCORE NUMBER(3)

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:

START_DATE
CAMPAIGN_CODE
PRODUCT_CODE
TALK_TIME_UPPER_LIMIT
TALK_TIME_LOWER_LIMIT

The EMPLOYEE table has a primary Key of:

TRANS_DATE
EMPLOYEE_ID
CAMPAIGN_CODE
PRODUCT_CODE

For scoring the START_DATE has to be less than or equal to the TRANS_DATE for scoring.

For Example:

I have a TRANS_DATE of 2001-10-01
I have the following START_DATE: 2001-11-01
2001-09-01
2001-08-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
E-Mail: shaun.desouza@cibc.com

[mageri]

Hi,

I'm not sure that I fully understand your question. But can this
be something?

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!