| Annual leave calculation [message #555835] |
Tue, 29 May 2012 04:58  |
 |
hissam78
Messages: 210
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
We want to calculate Annual leave the scenerio is as follows
1. Employee service more the one year
2. if joining date is 07-04-2008 than on 07-04-2009 completed one year and on 07-04-2010 completed two years and so on. day and month of date must be the same.
so we need YEAR*14. e.g. if 1 year completed then 1*14 if two years completed then 2*14 and so on.
by this we will get the opening of Annual leaves.
Another scenorio is
We have another table where we entered the leave day by day in whole year, if he avail 10 leaves in first year then 4 will be remaining and we can say it is the closing of first year and opening of second year. so on second year he will entitled 14 more leaves so 14 + 4 = 18 should be opening of next year.
In year Caclulation Day and Month must be the same for example 07 day, 04 month for every year.
14 Annual leaves are fix for each year
following are columns for output.
empid, ename, date of joining, sysdate, leave opening, leave avail, closing balance,
1, jone, 29-05-2009, 29-05-2012
2, herry,29-05-2008, 29-05-2012
3, bell, 29-05-2006, 29-05-2012
between two dates (date of joining and system date)
some body can help me to make a query?
thanx and regards
|
|
|
|
| Re: Annual leave calculation [message #555839 is a reply to message #555835] |
Tue, 29 May 2012 05:42  |
 |
Michel Cadot
Messages: 68771
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:so we need YEAR*14. e.g. if 1 year completed then 1*14 if two years completed then 2*14 and so on.
What does this mean?
For any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
