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