Home » SQL & PL/SQL » SQL & PL/SQL » Calculating leaves (Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production, win2003)
Calculating leaves Fri, 24 August 2012 02:37
 hissam78 Messages: 88Registered: 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
 Michel Cadot Messages: 65135Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 hissam78 Messages: 88Registered: 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
 Michel Cadot Messages: 65135Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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.

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 Aug 16 23:03:24 CDT 2017

Total time taken to generate the page: 0.13805 seconds