LEAVE MANAGEMENT

[Phuduhutswana]

I am working on creating a leave management system. The system should:
1) Leave ID (Primary key)
2) Employee ID (is a lookup from the employee table)
3) Store Employee name (is a lookup from the employee table)
2) Store employee start date (is a lookup from the employee table)
3) Store current date
4) Store accrued leave days. 1.5 of leave days is suppose to be accrued monthly (is a calculated value: ([current date] - [employee start date]) / 30 days * 1.5
5) Store Leave start date
6) store leave end date
7) Current leave days (is a calculated value: [leave end date] - [Leave start date])
8) Remaining days (is a calculated value: [accrued leave days] - [current leave days])


The above functionality works well for the employee's first record. But now the problem comes when the same employee applies for another leave. When the same employee applies for another leave, the accrued leave days should not be [employee start date] - [current date],instead it should be [remaining days] (from the first leave) + additional days accrued from the months which were not covered in the initial record. This is where i"'m stuck now



Kindly assist me the gurus

 

[MajP]

Is it not one formula. Leave Balance = 1.5[(Date - startdate) / 30] - sum(leave taken)
The sum of leave taken is probably a separate aggregate query where you sum the periods of leave taken.
You should not be storing employee name or employee id start date or current date.

  tblEmployee
     EmployeeID
     EmployeeLName
     EmployeeFName
     other employee fields

  tblLeave
      employeeID_FK
     LeaveStartDate
     LeaveEndDate

That is pretty much all you need.
This assumes you still accrue leave on leave. If you are a Salary employee this is normally true (at least for me). If Hourly this may not be the case and based on days worked not start date.

 

[GaP42]

My view is that there are some shortcomings in the data:

Some items require redefining

Leave ID (Primary key)\
Employee ID (is a lookup from the employee table)
Employee name (is a lookup from the employee table)

Not required – use the name in the employee table – don’t use a second copy – otherwise you would need to maintain it. BTW will you be notified / or see if an employee is no longer an employee? Inactive?

Employee start date (is a lookup from the employee table)

While this item may be sourced for a new employee from the employee table, it is not relevant thereafter for any existing employee. It should be renamed and defined as the Start Date for each leave accrual period for the employee recorded in the leave management system.

Current date

Does not appear to be relevant – this will change day to day. Do you mean this is the date upon which the accrued leave is calculated (to answer say an employee query about how much leave I have accrued to today? If so – this date is simply supplied by the OS – it is not stored. The accrued leave to date can easily be calculated on the fly. If instead it is meant to be the End Date for the period in which leave has been accrued then rename and define it as such.

Accrued leave days. 1.5 of leave days is suppose to be accrued monthly (is a calculated value: ([current date] - [employee start date]) / 30 days * 1.5

This is a calculated value – it should not be stored – unless there is some reason to keep the history of changes to reported values – in which case it probably would not be stored in this table anyway

Leave start date
Assume this is the date on which a leave period commences
Leave end date
Assume that this is the date on which a period of leave ends
Current leave days (is a calculated value: [leave end date] - [Leave start date])
Not required – calculated value – calculate on the fly
Remaining days (is a calculated value: [accrued leave days] - [current leave days])
While a calculated value this may be useful to store as a record of reference – refer to explanation below

When the same employee applies for another leave. When the same employee applies for another leave, the accrued leave days should not be [employee start date] - [current date],instead it should be [remaining days] (from the first leave) + additional days accrued from the months which were not covered in the initial record.

So now when an employee applies for another leave, a new accrued leave entitlement record is generated

• Leave start date = Leave Start Date (7)
• Leave End Date = the end date for the leave that the employee is seeking (8)
• The Accrued leave entitlement is the leave entitlement at th e end of the immediate prior leave taken where:
• Accrued Leave entitlement is CALCULATED as : ([leave start date] - [leave end Date form previous record]) / 30 days * 1.5 (+ any Accrued leave balance from the previous period) [ you referred to these as remaining days]

 

[arnelgp]

you may also try this demo.

 

[MajP]

However in reality doing this in a query is almost undoable and you are probably going to run some code to update a leave table. Because most of the time you do have all these exceptions.
Leave is sometimes not accrued daily but weekly/biweekly/ monthly at specific times.
You may have to track paid and unpaid leave and that changes accrual.
You may have other periods of absence.
You often are bringing leave forward (signing bonus) or other leave bonus.
Some companies allow selling of leave or purchasing.

So depends on how detail you need. I did one recently that was complicated with people coming and going and different rules for accrual by position.

I think I need the following tables

tblEmployees
  employeeID
  other fields

tblEmployeeAccrual
  EmployeeID_FK
  AccrualRate
  AccrualRule (used by code )

tblLeaveAdditionsDeduction
  employeeID_FK
  DateAddDeduct
  Amount  (positive for additions and neg for deduction)
  Reason (Bonus, sold, bought)

tblLeaveAbsence
  employeeID_FK
  LeaveStartDateTime
  LeaveEndDateTime
  LeaveAbsenceType (paid, unpaid, maternity also used in code in conjunction with accrual rules)

Then I had to run the code and push results to a table.
I still had to run code for the leave statements

 

[arnelgp]

the calculation (point #4) on post#1 is a typical example of Vacation leave and does not imply any other leave such as sick leave, emergency leave, maternity, etc.

for 1 year, 365 days you get 18.25 days, which means to get 30 days vacation, you need at least 2 years of service (typical 2-year contract in the middle east).

 

[arnelgp]

edit the db.

 

[bob fitz]

<Phuduhutswana>​

When entering and calculating leave taken, will your app need to differentiate between the employee's usual working days and those that are not usual working days? i.e. if an employee usually works Mon - Fri (5 days) and goes on leave for one week, will you calculate that as 5 days leave or 7 days leave?

 

[DickyP]

<Phuduhutswana>​

When entering and calculating leave taken, will your app need to differentiate between the employee's usual working days and those that are not usual working days? i.e. if an employee usually works Mon - Fri (5 days) and goes on leave for one week, will you calculate that as 5 days leave or 7 days leave?

Well done for flagging a this which can be a real problem - years back when I served in a tri-service unit it was a nightmare as each of the three services calculated entitlements and counting of days taken in different ways. (Not bad when in theory we all had the same numbers of days annually!)

 

[Gasman]

That is a bit harsh Bob, counting their days off as leave.

 

[bob fitz]

That is a bit harsh Bob, counting their days off as leave.

Well quite!! but if you simply count the number of days in the period of leave, that's what happens

 

[DickyP]

This really depends on how many people you are administering leave for. I suggest that if it is less than twenty it's easier done on papaer!

 

[Solo712]

My view is that there are some shortcomings in the data:

Some items require redefining

Leave ID (Primary key)\
Employee ID (is a lookup from the employee table)
Employee name (is a lookup from the employee table)

Not required – use the name in the employee table – don’t use a second copy – otherwise you would need to maintain it. BTW will you be notified / or see if an employee is no longer an employee? Inactive?

Employee start date (is a lookup from the employee table)

While this item may be sourced for a new employee from the employee table, it is not relevant thereafter for any existing employee. It should be renamed and defined as the Start Date for each leave accrual period for the employee recorded in the leave management system.

Current date

Does not appear to be relevant – this will change day to day. Do you mean this is the date upon which the accrued leave is calculated (to answer say an employee query about how much leave I have accrued to today? If so – this date is simply supplied by the OS – it is not stored. The accrued leave to date can easily be calculated on the fly. If instead it is meant to be the End Date for the period in which leave has been accrued then rename and define it as such.

Accrued leave days. 1.5 of leave days is suppose to be accrued monthly (is a calculated value: ([current date] - [employee start date]) / 30 days * 1.5

This is a calculated value – it should not be stored – unless there is some reason to keep the history of changes to reported values – in which case it probably would not be stored in this table anyway

Leave start date
Assume this is the date on which a leave period commences
Leave end date
Assume that this is the date on which a period of leave ends
Current leave days (is a calculated value: [leave end date] - [Leave start date])
Not required – calculated value – calculate on the fly
Remaining days (is a calculated value: [accrued leave days] - [current leave days])
While a calculated value this may be useful to store as a record of reference – refer to explanation below


So now when an employee applies for another leave, a new accrued leave entitlement record is generated

• Leave start date = Leave Start Date (7)
• Leave End Date = the end date for the leave that the employee is seeking (8)
• The Accrued leave entitlement is the leave entitlement at th e end of the immediate prior leave taken where:
• Accrued Leave entitlement is CALCULATED as : ([leave start date] - [leave end Date form previous record]) / 30 days * 1.5 (+ any Accrued leave balance from the previous period) [ you referred to these as remaining days]

There appear to be some loose ends still.
1) It is not clear what should happen if the records simply reflect employees' requests for leave, or only approved leaves by the management. If all requests are kept then a status field (request approved/rejected) is needed.
2) I would prefer not to store the calculation of the accrued leave entitlement either. It can be easily made as part of the BEFORE_UPDATE data testing.

Best,
Jiri

 

[Solo712]

That is a bit harsh Bob, counting their days off as leave.

It's not only harsh but quite irregular as well. Time off has always been calculated as time off days one is supposed to work, TMK.

Jiri.

 

[williamharry]

To make this work for multiple leave requests, try storing the "remaining days" after each leave, then update your accrual calculation to add leave days starting from the end date of the last leave. This way, the next leave request picks up from where the last one left off, using the previous "remaining days" plus the new accrual.