mysql处理考勤数据_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.

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

解决方案

Here is a long way that should work as expected:

SELECT

Employee.emp_Name,

'2011' AS `Year`,

'Dec' AS `Month`,

CASE (

IF(

DATE('1-12-2011') < DATE(Employee.Joining_Date)),

'0' --Not joined yet

IF (

(SELECT COUNT(*) FROM Holiday WHERE DATE('1-12-2011') = DATE(Holiday.date)) = 1,

'1', --National Holiday

IF (

(SELECT COUNT(*) FROM Leave WHERE DATE('1-12-2011') > DATE(Leave.to_Date) AND DATE('1-12-2011') < DATE(Leave.from_Date) AND Leave.Emp_Id = Employee.emp_id) = 1,

'2', --On Leave

IF(

(SELECT COUNT(*) FROM Doctor WHERE DATE('1-12-2011') > DATE(Doctor.Call_Date) AND Doctor.call_Done_By = Employee.emp_id) = 1 OR

(SELECT COUNT(*) FROM Chemist WHERE DATE('1-12-2011') > DATE(Chemist.Call_Date) AND Chemist.call_Done_By = Employee.emp_id) = 1,

'3' --Visit Doctor or Chemist

'4' --Employee was at work

)

)

)

)

)

WHEN 0 THEN 'N/A' --Not joined yet

WHEN 1 THEN 'L' --National Holiday

WHEN 2 THEN 'L' --On Leave

WHEN 3 THEN 'S' --Visit Doctor or Chemist

ELSE 'Y' --Employee was at work

END AS `1`, --first day of month

... AS `2`, --repeat for second day of the month till max day of current month replace '1-12-2011' with each different day of month

...

... AS `30`

FROM

Employee

My suggestion is to create a view that does the if statement for each employee that way your code will be easier to maintain. Please keep in mind that this is pseudo code that might need some some changing to run.

Hope this helps.