Update Query Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Update Query Problem

  1. #1
    Join Date
    May 2000
    Location
    Toronto, Ontario, Canada
    Posts
    2

    Exclamation

    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

  2. #2
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    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!
    Regards/
    Magnus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width