Columns for every day of the month
I have to develop an employee attendence system. I want to add all the fields horizontally. My fields are Empid,...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Name, 1, 2, 3, 4, 5, 6, 7 up to 31 and total present. I want to get the number of days present in a month. I use '1' to represent Present and '0' to represent Absent.
One immediate problem you will have is with your column names. "Name" may work in some databases, but those day numbers won't. So let's assume the table columns are Empid, EmpName, Day1, Day2, etc. You said total present would also be a column, so let's call that TotalPresent. The number of days present in a month can be obtained by the following SQL --
select Empid, EmpName, TotalPresent from Attendance
But this merely shifts your problem -- and it's a big problem -- over to the update process.
Let's say it's the 5th of the month and you want to update the database. Everybody is present today except Joe and Mary. You will need the following SQL --
update Attendance set Day5 = 1
update Attendance set Day5 = 0 where EmpName in ('Joe', 'Mary')
Almost finished. You still have to update TotalPresent as well!
update Attendance set TotalPresent = Day1 + Day2 + Day3 + Day4 + Day5
Now pretend it's the 6th. Can you see where the update queries would have to change? You have to update a different column each day! This means you need 62 different update queries. Luckily, you need only one query to update TotalPresent, assuming that you simply add up all DayN columns. And this implies that you should pre-update them at the beginning of the month and set them all to 0. You cannot set them all to 1, otherwise you cannot query TotalPresent on the 5th because it will return a number between 26 and 31.
Whew. So, 64 different queries, and you have to update every row every day! That's a lot of updating, to say nothing of an administrative nightmare. Here's a better solution:
create table Employees ( Empid integer , EmpName varchar(50) , EmpStartDate datetime )
create table Absences ( Empid integer , EmpAbsentDate datetime )
The Employees table will store names. This table will be updated only when an employee leaves or joins. The Absences table will store absences only. The idea is that you will be updating this table as little as possible, and it is assumed that most employees will be present most days. On any day where an employee is absent, just insert another row into the Absences table --
insert into Absences ( Empid, EmpDate) values ( 937, CURRENT_DATE)
To determine everyone's attendance for the month, use a LEFT OUTER JOIN from the Employees table to the Absences table --
select EmpName , 31 - sum( case when EmpAbsentDate is null then 0 else 1 ) as TotalPresent from Employees left outer join Absences on Employees.Empid = Absences.Empid group by EmpName
The above query is incomplete insofar as you will need to check EmpStartDate for employees that joined part way through the month, and also to adjust the TotalPresent formula for the different numbers of days in different months.