New New

[NayLahsSQL]

I am a trying to learn SQL/ORACLE on my own and I am completely lost on one issue. I have to write a program that calculates a yearly bonus amount based on three factors: years employee has worked, job type, and if the employee deserves extra merit.

I have a habit of trying to figure out the code in sections so right now I am trying to figure out the date calculation portion.
I am trying to calculate the difference between the employee start date and the current date in years so I can determine for how mucu bonus pay the employee should receive.

This is where I am so far and the results in SQL/ORACLE:

SQL> DECLARE
2 todays_date DATE;
3 employee_start_date DATE;
4 years_worked VARCHAR2(5);
5 BEGIN
6 todays_date := TRUNC(SYSDATE, 'YEAR');
7 todays_date := TO_CHAR(todays_date);
8 employee_start_date := TO_CHAR(employee_start_date);
9 employee_start_date := '05-JAN-93';
10 years_worked := (todays_date - employee_start_date);
11 DBMS_OUTPUT.PUT_LINE('There are ' || years_worked || ' years worked ' || 'between ' || employe
e_start_date || ' and ' || TO_CHAR(todays_date));
12 END;
13 /
There are 5109 years worked between 05-JAN-93 and 01-JAN-07

PL/SQL procedure successfully completed.

What is the problem here??

[skhanal]

You have declared today_date and employee_start_date as DATE and you are assigning character values to those. That could be the problem.

Here is a sample code from Oracle doc

Solution Description:
=====================

You must use PL/SQL or other program code to display date
differences in the format Years, Months, Days, Hours,
Minutes, Seconds.

Because Oracle returns the difference in days and partial
days, the difference must be manually converted to display
it in the format of Years, Months, Days, Hours, Minutes,
Seconds.


Solution Explanation
====================

The following example loads a table with 2 records with
2 date values for starting and ending dates. The pl/sql
block will calculate the difference of the dates and display
the result in the format described above.


create table ddiff
(stdate date,
endate date);

insert into ddiff (stdate,endate) values
(to_date('01-jan-96 00:00:00','dd-mon-yy hh24:mi:ss'),
to_date('01-mar-97 01:01:01','dd-mon-yy hh24:mi:ss'));

insert into ddiff (stdate,endate) values
(to_date('01-jun-99 00:00:00','dd-mon-yy hh24:mi:ss'),
to_date('03-oct-99 02:02:02','dd-mon-yy hh24:mi:ss'));

set echo off
set serverout on
declare
cursor c1 is
select stdate,endate from ddiff;
date_res number;
end_now date;
f_end_now char(20);
st_now date;
temp_now date;
f_st_now char(20);
yy_now number := 0;
mm_now number := 0;
dd_now number := 0;
hh_now number := 0;
f_hh_now number := 0;
mi_now number := 0;
f_mi_now number := 0;
ss_now number := 0;
f_ss_now number := 0;
begin
open c1;
loop
fetch c1 into st_now, end_now;
exit when c1%notfound;

-- Make st_now the earlier date
if (end_now < st_now) then
temp_now := end_now;
end_now := st_now;
st_now := temp_now;
end if;

f_end_now := to_char(end_now,'Mon-dd-yyyy hh24:mi:ss');
f_st_now := to_char(st_now,'Mon-dd-yyyy hh24:mi:ss');

yy_now := trunc(months_between(end_now,st_now)/12);
mm_now := mod(trunc(months_between(end_now,st_now)),12);
dd_now :=
trunc(end_now-add_months(st_now,months_between(end_now,st_now))) ;

date_res := (end_now-st_now);

hh_now := (date_res-floor(date_res))*24;
f_hh_now := trunc(hh_now);
mi_now := (hh_now-floor(hh_now))*60;
f_mi_now := trunc(mi_now);
ss_now := (mi_now-floor(mi_now))*60;
f_ss_now := round(ss_now);

dbms_output.put_line(chr(10)||
'The time difference between ');
dbms_output.put_line('starting date '||f_st_now);
dbms_output.put_line('and ending date '||
f_end_now||' is');
dbms_output.put_line(chr(10)||yy_now||' year(s), '||
mm_now||' month(s), '||dd_now||' day(s)');
dbms_output.put_line('and '||f_hh_now||' hour(s), '||
f_mi_now||' minute(s), '||
f_ss_now||' second(s)');
end loop;
close c1;
end;
/


SQL> @ddiff

The time difference between
starting date Jan-01-1996 00:00:00
and ending date Mar-01-1997 01:01:01 is

1 year(s), 2 month(s), 0 day(s)
and 1 hour(s), 1 minute(s), 1 second(s)

The time difference between
starting date Jun-01-1999 00:00:00
and ending date Oct-03-1999 02:02:02 is

0 year(s), 4 month(s), 2 day(s)
and 2 hour(s), 2 minute(s), 2 second(s)

[junOOni]

It seems like you are getting number of days, instead of years, in your output. Divide 5109 by 365 to get the approximate number of years.

[NayLahsSQL]

I did that and it doesnt work so Im stuck...

[skhanal]

When you subtract one date from another you are getting result in number of days.

DECLARE
todays_date DATE;
employee_start_date DATE;
years_worked VARCHAR2(50);
BEGIN
todays_date := SYSDATE;
employee_start_date := SYSDATE-5;
years_worked := (todays_date - employee_start_date);
DBMS_OUTPUT.PUT_LINE('There are ' || years_worked || ' years worked ' || 'between ' || employee_start_date || ' and ' || TO_CHAR(todays_date));
END;
/

There are 5 years worked between 04-MAR-07 and 09-MAR-07

So you have to divide it by 365, which may not be accurate as it does not account for leap year

DECLARE
todays_date DATE;
employee_start_date DATE;
years_worked VARCHAR2(50);
BEGIN
todays_date := TRUNC(SYSDATE, 'YEAR');
employee_start_date := to_date('05-JAN-93');
years_worked := (todays_date - employee_start_date)/365;
DBMS_OUTPUT.PUT_LINE('There are ' || years_worked || ' years worked ' || 'between ' || employee_start_date || ' and ' || TO_CHAR(todays_date));
END;
/

There are 13.9972602739726027397260273972602739726 years worked between
05-JAN-93 and 01-JAN-07

[NayLahsSQL]

Thank you soooo much hopefully this will work for me!!

NayLah

[NayLahsSQL]

Ok, it is very difficult trying to learn ORACLE PL/SQL on my own especially when I don't have a professor to check my work....so maybe you can help. The first question I asked was only for one portion of this case study from this ORACLE 9i book. Here is the rest:

I have to calculate an employees bonus based on the number of years he has worked at the company, whether or not he is in job_type A, and B and if he/she has been commended for extra merit.

If the employee has worked from 1- 5 years they get $100, 6-10 is $200 and 11-15 merits a $300 bonus and over 16 years merits a $500. If the employee is Job Type A they get an additional bonus of 100 and $200 for Job Type B. Also, if the employee has been commended for extra merit, they get a 5% bonus based on his or her current salary. Whew.........now this is what I am trying to write but it is just not clicking in my head and I keep getting an error:

my test data is this: employee_start_date = 1/5/1982, job_type = A, extra_merit = TRUE and current_salary = 75,800.

This is my code:
DECLARE
todays_date DATE;
employee_start_date DATE;
job_type CHAR(1);
extra_merit BOOLEAN;
current_salary NUMBER;
years_worked VARCHAR2(50);
employee_bonus NUMBER;
current_salary NUMBER;
BEGIN
todays_date := TRUNC(SYSDATE, 'YEAR');
employee_start_date := to_date('01-MAY-93');
years_worked := (todays_date - employee_start_date)/365;
years_worked :=TRUNC(years_worked, 0);
extra_merit := 'TRUE'
job_type := A;
IF years_worked >=5 AND extra_merit = 'False' THEN
DBMS_OUTPUT.PUT_LINE('The bonus for this employee is ' || '$100' || );
END;
/

Can ANYONE help me on this - I dont want the answer but can anyone explain what I am doing wrong - I thought I needed a LOOP to test the data but like I said its not clicking in my head. PLEASE HELP!!

[eila]

it seems that theres something wrong with the line job_type:=A;

try to fix it before u do a loop

[NayLahsSQL]

Ok, but I dont understand it still........will try some more

[eila]

try this code.. is it clickin now

DECLARE
  employee_start_date DATE := TO_DATE('07/15/2001', 'MM/DD/YYYY');
  job_type CHAR(1) := 'A';
  extra_merit BOOLEAN := TRUE;
  current_salary NUMBER := 37800;
  employee_bonus NUMBER;
  years_of_service BINARY_INTEGER := 0;
BEGIN
  years_of_service := TRUNC((SYSDATE - employee_start_date)/365);
  IF years_of_service > 1 AND years_of_service < 6 THEN
    employee_bonus := 100;
  ELSIF years_of_service > 6 AND years_of_service < 11 THEN
    employee_bonus := 200;
  ELSIF years_of_service > 11 AND years_of_service < 16 THEN
    employee_bonus := 300;
  ELSIF years_of_service > 16 THEN
    employee_bonus := 500;
  ELSE
    employee_bonus := 0;
  END IF;

  IF job_type = 'A' THEN
    employee_bonus := employee_bonus + 100;
  ELSE
    employee_bonus := employee_bonus + 200;
  END IF;

  IF extra_merit = TRUE THEN
    employee_bonus := employee_bonus + (.05 * current_salary);
  END IF;

  DBMS_OUTPUT.PUT_LINE ('This Employee has been working here for the last ' || to_char(years_of_service)|| ' year(s).');
  DBMS_OUTPUT.PUT_LINE (' his/her salary is: '|| TO_CHAR(current_salary, '$99,999') || ', and his/her bonus is:' || TO_CHAR(employee_bonus, '$99,999'));
  
END;

[NayLahsSQL]

It worked!!! I appreciate your help. The book I have is cool but its difficult to do the work when you are not actually in the class itself and you cant check your work against the books answers.......I started working on using Loops with Explicity and Implicit cursors - this seems easier but if I need help can I ask you?? Thanks!

[eila]

It worked!!! I appreciate your help. The book I have is cool but its difficult to do the work when you are not actually in the class itself and you cant check your work against the books answers.......I started working on using Loops with Explicity and Implicit cursors - this seems easier but if I need help can I ask you?? Thanks!

I guess we're studying the same book material, i'm a student and we're having oracle forms lab sessions, but as u said, u cant check ur work against the books answers.
Hey, i might need some help from you as well !! talk to u soon