Attendance Report using MySql

pravingate07 · #1 (**permalink**) 02-04-12, 10:14

I want to write a query to generate attendance report of employee. First I will tell you how the presence of employee is stored in database.

I have following tables.

Code:

Employee Table with Columns

    emp_id  emp_Name  Joining_Date
     1        L         11-01-2012
     2        M         12-01-2012


Holiday Table

Holiday_Name         Date 
Chrismas          25-12-2012  
Dushera           08-03-2012
Independance Day  15-08-2012

Leave Table

Subject  from_Date     to_Date      Emp_Id     status 
PL       02-01-2012    04-01-2012      1       Approved
CL       11-01-2012    12-01-2012      2       Declined   


Doctor Table

Subject   Call_Date    call_Done_By(emp_id)
 Call     15-01-2012      1
 CA       21-02-2012      2

Chemist Table
Subject   Call_Date    call_Done_By(emp_id)
 Chemist   1-02-2012     2
 Texo      21-03-2012      1

If employee is visited to doctor or chemist,that particular date is stored in that particular doctor or chemist table with employee_id

Now I want output format like this way..................

Code:

Employee    Month    1  2   3   4   5   6    7.... 
 Name       Nov11    Y  Y   Y   Y   Y   L    S....
 Name 1     Dec11    Y  Y   Y   Y   Y   Y    S....

what will be the possible solutions for it?

Pat Phelan · #2 (**permalink**) 02-04-12, 11:27

Can you fix the underlying problems with the schema, or are you required to use bubble gum and bailing wire to make the existing schema work as best you can?

-PatP