HI friends,

   Can u tell the logic for my problem?
I am generating payslip. I have different table to store leaves, attendance,emloyee,holidays. 2 types of employees(Staff and Worker). Staff can avail 6 leaves(paid) for 6 months. It count from the date of join of emp. If he avails more than 6, excess will be deducted from the current salary. If not used, balance will be brought forward to next 6 months.(next 6 months he can avail 6+prev balance). If he didnt utilize 12(total 6+6 or 12-usedleaves), the balance is brought forward to next year. So next year first 6 months he can avail prev year balance+6. This is upto 2 years. We have to follow according to this country labour law.
I think u got it. I am not able to find the logic for counting 6 months from the dateofjoin and if excess(>6 in 6 months) will be deducted on the spot. So, the law states that emp can avail 6 leaves in 6 MONTHS. We have to count leaves for every 6 months from the date of join.

If any reply is highly appreciate. Thanx in advance.

U can send mail to [email protected]

It is just very simple if u r using SQL server because it has the functions of calculating months from where u want
eg
Select * from employee where month(date_of _joining) = 6

Love 4 all

Hi,

You have not mentioned about what database server you use.

If you are using SQL server, you can create a job and schedule it to run everyday. It can be as follows

Create a procedure that
1) Checks if TODAY - 6 months = Date_Of_Joining(DOJ) of any staff.
2) If so populate those rows in a cursor and do the calculations for those staffs and update their remaining leave details.
3) Schedule it to run daily as an SQL job. As staff might have joined on any day.

Within the procedure you may code like this.

If (Select count(Staff) from YOURTABLE where DOJ = '''' + cast(year(getdate()) as char(4)) + '-' + cast(month(getdate())-6 as varchar(2)) + '-' + cast(day(getdate()) as varchar(2)) + '''' > 0)
BEGIN
-- Have a CURSOR Populating rows that satisfy the above condition,
-- because there may be more than one staff memeber joined that day.
-- within the cursor pick row by row and
-- if (6 - staff_availed_Leave) < 0 then calculate (6 - staff_availed_Leave) * Day_Salary and
-- store that in deductions column to be deducted from current salary.
-- if (6 - staff_availed_Leave) > 0 then update add up (6 - staff_availed_Leave) to REMAINING leave for next half year.
-- close the cursor.
END

Follow the same logic for cases of 12 months, 18 months and 24 months within the same procedure using different IF constructs. This can be donw without CURSOR too, provided you derive at a formula that can do all that(calculate deduction, and remaining leaves) in one step for one IF.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection