MySql中的考勤报告 [英] Attendance Report in 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.

我有以下表格。

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

如果员工被访问医生或化学家，该特定日期存储在具有employee_id的特定医生或化学师表中

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

现在个人将选择年和月，他应该能够以以下格式获得出勤报告
示例：假设用户选择年份为2011，月份为十二月，则输出应为

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

这里的输出1,2,3 ....是从0-30开始的天数月份，我们可以使用案例写入

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

考虑如果员工在当天出席其状态为Y，否则，如果他去任何客户喜欢医生，化学家，然后用'S'替换。

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

我的建议是创建一个视图，为每个员工执行if语句，使代码更容易维护。请记住，这是伪代码，可能需要一些更改才能运行。

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.

希望这有帮助。

这篇关于MySql中的考勤报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！