OraFAQ Forum: SQL & PL/SQL » Calculating leaves

Home » SQL & PL/SQL » SQL & PL/SQL » Calculating leaves (Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production, win2003)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Calculating leaves [message #564465]

Fri, 24 August 2012 02:37

hissam78
Messages: 47
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;

Report message to a moderator

Re: Calculating leaves [message #564467 is a reply to message #564465]

Fri, 24 August 2012 03:16

Michel Cadot
Messages: 54155
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

Report message to a moderator

Re: Calculating leaves [message #564492 is a reply to message #564467]

Fri, 24 August 2012 05:59

hissam78
Messages: 47
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

Report message to a moderator

Re: Calculating leaves [message #564493 is a reply to message #564492]

Fri, 24 August 2012 06:02

Michel Cadot
Messages: 54155
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

Report message to a moderator

Previous Topic:	Ranking Non-Consecutive Rows
Next Topic:	Question about sys_guid

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 17:24:23 CDT 2013

Total time taken to generate the page: 0.59664 seconds