Attendance report Using Mysql

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 my database.

I have following tables.

Code:

---

Employee Table with Columns

    emp_id  emp_Name  Joining_Date
    1      john        11-01-2012
    2      Scott        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 person will select year and month and he should be able to get attendance report in following format Example : suppose user selects year as '2011' and month as 'Dec' then output should be

Code:

---

Employee  year  Month    1  2  3  4  5  6    7....
 John      2011  Nov    Y  Y  Y  Y  Y  L    S....
 Scott    2011  Nov    Y  Y  L  M  Y  L    S

---

here in output 1,2,3.... are days from 0-30 for a month which we can write using 'case'

Consider if employee is present on day show its status as 'Y' else L else if he gone to any customer like doctor,chemist,then replace it with 'S'.

So how should I write a query to achieve this output?? any suggestions will be helpful for me....