Implementing Holidays in an Employee Management System

Question

I'm working on an Employee Management System where employees can

• Clock in/Clock Out (Mark daily attendance)
• Apply for Leaves i.e. Sick
• Generate Reports i.e. Thier daily check in/Check out time, Total Worked Hours etc.

Now I have to introduce a way to deal with public holidays and employee absentees in these reports. I read a few articles online such as

• https://softwareengineering.stackexchange.com/questions/262272/integrating-holidays-and-attendance-in-an-hr-management-system

but I couldn't figure out anything. My questions are basically.

• What is the best way to deal with an employee being absent? Considering the fact that on that particular day an employee will not check in.

• How to Add/specify Weekend/Public Holidays in reports? As changing public/regular holidays in the future may well affect our reports of the previous months.

I'd really appreciate some help here! Thank you

Answer 1

Very few (if any) databases deal with date ranges as a data type, and while there are ways to calculate date range intersections they are expensive and will cause any large datasets to crawl. thus you need to trade calculation speed for data storage

because of this the best way to check is to have a table that stores dates, then use the dates in that table to inflate your date ranges to all dates inside the range, the structure is simple just a single column with the date in it

i would not suggest using the date table to keep track of holidays instead just include these as absences of type "Public Holiday" you may want to mark weekends (or what day of the week it is) in the datetable but don't use that to say whether a person did or didn't work that day instead have a workingPattern table that says employee of type factory worker works 4 hours on a monday, 6 hours on a tuesday. this will then give you an employees expected working hours and which days they are expected to work in a week with out forcing every employ to follow the same working pattern

eg

select d.Date, a.*
from absence as a
join dates as d on d.Date between a.StartDate and a.EndDate

this will then give you every date that the person is absent allowing you you very easily compare different absence periods together

most employ systems rarely allow employees to work more than a year in advance, so I would suggest having a monthly job that runs and populates the date table with all dates between (Today + 12 months) and (Today + 13 months) you can also delete old dates but before doing so make sure there are no circumstances where you will need to query the data. ie everything before the min date you are leaving has been archived.

The next thing to keep in mind is the human element, people will forget to clock in or out so you need to keep this in mind and have a daily job that looks for clock ins with out a matching clock out or visa versa, then you can either flag these for human correction or deal with them automatically

once you have these two elements sorted you can calculate each persons daily hours worked,cross join that to the daily absence view, which will allow you to check for inconsistency like dates on the Date table that have no clockings or absence (unauthorised absence) that can be corrected with appropriate entries into the absence table or clashes such as someone coming into work when they should have been on holiday, again you can flag for human correction or handle automatically, say if clocking is present ignore absence

How you actually build your system depends on what you need in your systems but these are the common factors that will kill your system if you don't take them into account

Answer 2

I was writing a comment, but that gone to far and I considered to write an answer.

How to Add/specify Weekend/Public Holidays in reports?

You can use a table (like in this question) to store all days of the given year with flag is a day working or not. We use something like this in our environment. It is filled manually once a year.

What is the best way to deal with an employee being absent?

I bet there will be some table with rows like:

EmployeeID  InOut   Date                
1           In      2016-07-24 07:00:01.000
1           Out     2016-07-24 18:00:09.908
etc

So if Employee being absent - there will be no rows on particular date.