使用出勤率创建月度报告 [英] Creating a monthly report using of Attendence

问题描述

你好，


我想创建一个关于员工出勤的月度报告.我有一个包含字段
的出勤表 1. EmployeeID
2. InTime
3.超时时间

还有另一个员工表.我的问题是，如果某个员工不来或没有工作日，那么出勤表中没有该日期的行.但我想显示一个月的所有31个日期.我的意思是周日将是空白行.现在，我该如何使用存储过程来做到这一点?

Hello ,


I want to create a monthly report on employees attendance. I have a Attendance table which has fields
1. EmployeeID
2. InTime
3. Out Time

there is another employee table. My problem is if a employee doesn''t come or there is holyday then there will be no row for that date in the attendance table. But I want to show all 31 dates of a month. I mean the holyday date will be a blank row. Now how can I do that using store procedure?

推荐答案

如果不查看出勤"表就无法为您提供确切的SQL查询.

在雇员"表和出勤"表之间使用左外部联接.

SQL左联接

使用外部联接

It would not be possible to provide you exact SQL Query without looking at your "Attedance" Table.

Use Left Outer join between your "Employee" table and "Attedance" Table.

SQL LEFT JOIN

Using Outer Joins

创建第三个临时表，每个月中的每一天都有一行.
在此临时表和出勤"表之间执行LEFT OUTER JOIN.
与employee表一起加入，您应该列出employee表的全天列表.

然后，您可以删除临时表.

Create a third temporary table with a row for every day in a month.
Do a LEFT OUTER JOIN between with this temporary table and the Attendance table.
Join this with the employee table and you should a list of all days with employee table.

You can then drop the temporary table.

这篇关于使用出勤率创建月度报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！