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