Columns for every day of the month

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.