Home » SQL & PL/SQL » SQL & PL/SQL » Show details of employee hiredates and the date of their first payday.
Show details of employee hiredates and the date of their first payday. [message #10630] Thu, 05 February 2004 12:13 Go to next message
Fred Smith
Messages: 2
Registered: February 2004
Junior Member
Hello I have the following question:

Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)

using the following table:

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7369,'SMITH','CLERK',7902,'17-DEC-1996',13750,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7499,'ALLEN','SALESMAN',7698,'20-FEB-1992',19000,6400,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7521,'WARD','SALESMAN',7902,'22-FEB-1995',18500,4250,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7566,'JONES','MANAGER',7939,'02-APR-1991',26850,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7654,'MARTIN','SALESMAN',7698,'28-SEP-2001',15675,3500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7698,'BLAKE','MANAGER',7839,'01-MAY-1993',24000,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7782,'CLARK','MANAGER',7839,'09-JUN-1990',27500,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7788,'SCOTT','ANALYST',7566,'19-APR-1999',19500,20);
insert into emp(empno,ename,job,hiredate,sal,deptno)
values(7839,'KING','PRESIDENT','17-NOV-1986',82500,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7844,'TURNER','SALESMAN',7698,'08-SEP-1996',18500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7876,'ADAMS','CLERK',7788,'23-MAY-1996',11900,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7900,'JAMES','CLERK',7698,'03-DEC-2000',12500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7902,'FORD','ANALYST',7566,'03-DEC-1994',21500,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(7934,'MILLER','CLERK',7782,'23-JAN-1998',13250,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(3258,'GREEN','SALESMAN',4422,'24-JUL-1999',18500,2750,50);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(4422,'STEVENS','MANAGER',7839,'14-JAN-1997',24750,50);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
values(6548,'BARNES','CLERK',4422,'16-JAN-2002',11950,50);

I have managed to get this question to work by using the following statements:

SQL> SELECT ename, hiredate, NEXT_DAY((LAST_DAY(hiredate)), 'Friday')-7, TO_CHAR((NEXT_DAY((LAST_DAY
(hiredate)), 'Friday')-7),'DAY') FROM emp;

Which produces this output:

ENAME      HIREDATE  NEXT_DAY( TO_CHAR((
---------- --------- --------- ---------
SMITH      17-DEC-90 28-DEC-90 FRIDAY
ALLEN      20-FEB-89 24-FEB-89 FRIDAY
WARD       22-FEB-93 26-FEB-93 FRIDAY
JONES      02-APR-89 28-APR-89 FRIDAY
MARTIN     28-SEP-97 26-SEP-97 FRIDAY
BLAKE      01-MAY-90 25-MAY-90 FRIDAY
CLARK      09-JUN-88 24-JUN-88 FRIDAY
SCOTT      19-APR-87 24-APR-87 FRIDAY
KING       17-NOV-83 25-NOV-83 FRIDAY
TURNER     08-SEP-92 25-SEP-92 FRIDAY
ADAMS      23-MAY-96 31-MAY-96 FRIDAY
JAMES      03-DEC-95 29-DEC-95 FRIDAY
FORD       03-DEC-91 27-DEC-91 FRIDAY
MILLER     23-JAN-95 27-JAN-95 FRIDAY
GREEN      24-JUL-95 28-JUL-95 FRIDAY
STEVENS    14-JAN-94 28-JAN-94 FRIDAY
BARNES     16-JAN-95 27-JAN-95 FRIDAY

17 rows selected.

but the problem is that martin gets paid on the 26th and is hired on 28th.

So how do I get it to work if an employee is hired after the last Friday of the month?

  Kindest Regards Fred

   Your help would be appreciated.                   

       

 

 

 

Report message to a moderator

Re: Sorry the hiredate in the table for the above for martin should be 26th sept not 28th [message #10631 is a reply to message #10630] Thu, 05 February 2004 12:20 Go to previous messageGo to next message
Fred Smith
Messages: 2
Registered: February 2004
Junior Member
Sorry the hiredate in the table for the above for martin should be 26th sept not 28th

Report message to a moderator

Re: Show details of employee hiredates and the date of their first payday. [message #10637 is a reply to message #10630] Fri, 06 February 2004 03:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
You need to check to see if the hiredate is on or after, or in other words greater than or equal to, the last Friday of the current month and if it is, then use the last Friday of the next month as the payday. I will just use one record where an employee is hired after the last Friday of the month to demonstrate.

scott@ORA92> SELECT ename, hiredate
  2  FROM   emp
  3  /

ENAME      HIREDATE
---------- ---------
NEW_EMP    26-JAN-02

scott@ORA92> 
scott@ORA92> SELECT ename, hiredate,
  2  	    NEXT_DAY (LAST_DAY (hiredate) - 7, 'Friday') AS this_pay,
  3  	    NEXT_DAY (LAST_DAY (ADD_MONTHS (hiredate, 1)) - 7, 'Friday') next_pay
  4  FROM   emp
  5  /

ENAME      HIREDATE  THIS_PAY  NEXT_PAY
---------- --------- --------- ---------
NEW_EMP    26-JAN-02 25-JAN-02 22-FEB-02

scott@ORA92> 
scott@ORA92> 
scott@ORA92> <b>SELECT ename, hiredate,
  2  	    CASE WHEN hiredate >= NEXT_DAY (LAST_DAY (hiredate) - 7, 'Friday')
  3  		 THEN NEXT_DAY (LAST_DAY (ADD_MONTHS (hiredate, 1)) - 7, 'Friday')
  4  		 ELSE NEXT_DAY (LAST_DAY (hiredate) - 7, 'Friday')
  5  	    END AS payday
  6  FROM   emp
  7  /</b>

ENAME      HIREDATE  PAYDAY
---------- --------- ---------
NEW_EMP    26-JAN-02 22-FEB-02

Report message to a moderator

Re: Show details of employee hiredates and the date of their first payday. [message #10781 is a reply to message #10637] Tue, 17 February 2004 03:18 Go to previous messageGo to next message
Jarod
Messages: 1
Registered: February 2004
Junior Member
Is it possible to not use the CASE statement ?

Report message to a moderator

Re: Show details of employee hiredates and the date of their first payday. [message #10869 is a reply to message #10781] Sat, 21 February 2004 04:45 Go to previous message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
You could use teh DECODE and SIGN Oracle built-in functions instead of CASE. Why don't you want to use CASE? Are you using an older version of Oracle? If you are using Oracle 8i and you want to use CASE in PL/SQL, then you need to execute it dynamically. Please see the example of the alternative method using DECODE and SIGN below.

scott@ORA92> SELECT ename, hiredate,
  2  	    DECODE (SIGN (NEXT_DAY (LAST_DAY (hiredate) - 7, 'Friday') - hiredate),
  3  		    -1, NEXT_DAY (LAST_DAY (ADD_MONTHS (hiredate, 1)) - 7, 'Friday'),
  4  		    NEXT_DAY (LAST_DAY (hiredate) - 7, 'Friday'))
  5  	       AS payday
  6  FROM   emp
  7  /

ENAME      HIREDATE  PAYDAY
---------- --------- ---------
SMITH      17-DEC-80 26-DEC-80
ALLEN      20-FEB-81 27-FEB-81
WARD       22-FEB-81 27-FEB-81
JONES      02-APR-81 24-APR-81
MARTIN     28-SEP-81 30-OCT-81
BLAKE      01-MAY-81 29-MAY-81
CLARK      09-JUN-81 26-JUN-81
SCOTT      19-APR-87 24-APR-87
KING       17-NOV-81 27-NOV-81
TURNER     08-SEP-81 25-SEP-81
ADAMS      23-MAY-87 29-MAY-87
JAMES      03-DEC-81 25-DEC-81
FORD       03-DEC-81 25-DEC-81
MILLER     23-JAN-82 29-JAN-82
NEW_EMP    26-JAN-02 22-FEB-02

Report message to a moderator

Previous Topic: PL/SQL function to insert a new row into the table and fetch the key of the newly created row
Next Topic: Parsing comma separated String
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 23:48:28 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.