Home » SQL & PL/SQL » SQL & PL/SQL » Calculating leaves (Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production, win2003)
Calculating leaves [message #564465] Fri, 24 August 2012 02:37 Go to next message
hissam78
Messages: 64
Registered: August 2011
Location: PAKISTAN
Member
Dear all,
we have the following test table script given below, there are three records with leave id 1,2,3
if bal is greater than 0 and leave id is 3 then insert 1 in QF field and -1 in QF_mul field.
when balance of leave id no. 3 is 0 and bal is greater than 0 in leave id is 2 then insert 1 in QF field and -1 in QF_mul field.
when balance of leave id no. 2 is 0 and bal is greater than 0 in leave id is 1 then insert 1 in QF field and -1 in QF_mul field.
sequence start from 3 then 2 then 1.
some body can help me to make a query

leave id 3 means annual leave
leave id 2 means sick leave
leave id 1 means casual leave

we want to first deduct the annual leave(3) when bal 0 then sick leave bal and then casual leave.

Test Table:

INSERT INTO LEAVE_TEST ( EMP_ID, LEAVE_ID, OPN_LEAVE, EMP_LEAVE, BAL, QF,
QF_MUL ) VALUES (
'000005', 3, 1, 36, 35, NULL, NULL);
INSERT INTO LEAVE_TEST ( EMP_ID, LEAVE_ID, OPN_LEAVE, EMP_LEAVE, BAL, QF,
QF_MUL ) VALUES (
'000005', 2, 254, 36, 218, NULL, NULL);
INSERT INTO LEAVE_TEST ( EMP_ID, LEAVE_ID, OPN_LEAVE, EMP_LEAVE, BAL, QF,
QF_MUL ) VALUES (
'000005', 1, 254, 36, 218, NULL, NULL);
commit;
Re: Calculating leaves [message #564467 is a reply to message #564465] Fri, 24 August 2012 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Test Table:


We have not the table so we can't insert.
Also give us the result you want with the data you gave.
Do these data cover all the cases you mention in your specification? If not, then post more data.

Regards
Michel
Re: Calculating leaves [message #564492 is a reply to message #564467] Fri, 24 August 2012 05:59 Go to previous messageGo to next message
hissam78
Messages: 64
Registered: August 2011
Location: PAKISTAN
Member
this is complete data,
following is the table script
CREATE TABLE LEAVE_TEST
(
EMP_ID VARCHAR2(6 BYTE),
LEAVE_ID NUMBER,
OPN_LEAVE NUMBER,
EMP_LEAVE NUMBER,
BAL NUMBER,
QF NUMBER,
QF_MUL NUMBER
)
output will be the following:


EMP_ID LEAVE_ID BAL QF QF_MUL

5 3 35 1 -1
5 2 0
5 1 0

if bal is greater than 0 then QF field insert 1 and QF_mul will insert -1

thanx and regards
Re: Calculating leaves [message #564493 is a reply to message #564492] Fri, 24 August 2012 06:02 Go to previous message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a more complete test case.
You have nothing in your data that satisfies the second and third rules.
It is easy to write a query that satisfies the rules but not the other one but we can't see it as we have nothing to test with.

Also, Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Ranking Non-Consecutive Rows
Next Topic: Question about sys_guid
Goto Forum:
  


Current Time: Wed Nov 26 09:10:02 CST 2014

Total time taken to generate the page: 0.18917 seconds