Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    12

    Wink Unanswered: how to work out length of employement?

    hi! i have table called employee. in there i have the fields

    startDate Date,
    lengthOfEmployement number(5),

    i want to work out the lenghtOfEmployement but when i try the code

    Code:
    insert into employee values(
    1001, 7777, 'John', 'Smith', '12', 'Woodcock', 'Cheshunt', 'Hertfordshire', 
     'ER3RR3', '01233433456', '12-03-1999', round(sysdate - startdate), 100101);
    it gives me an error 'column not allowed'. how can i work out the lenghtOfEmployement within that field.
    thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Don't INSERT, UPDATE!
    Code:
    UPDATE employee SET
      lengthOfEmployement = ROUND(SYSDATE - startdate);
    However, storing computed values in a table is not a very good idea. "Number of days" is about to change every day and there's really no purpose in doing that.

  3. #3
    Join Date
    Mar 2007
    Posts
    12
    ok thanks. but have u got any ideas how i could improve this then? can i have it calculate only the months then. this would be more sensible.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, it wouldn't.

    This information isn't worth much. What would you do with it in a table, anyway? Select it and display in a report? If so, calculate it when the report is run. Otherwise, you'd first have to update a table (which might take a while if a table is large) and then display the result in a report.

    Once again: do not store such information in a table - calculate it whenever needed.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Littlefoot is totally correct. When you need the years of service (for example), simply compute it during the select.

    SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,STARTDATE))/12) YEARS
    FROM EMPLOYEE;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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