时间戳

首页 > 解决方案 > 每个部门出勤率最低的前 5 名员工

sql-server - 每个部门出勤率最低的前 5 名员工

问题描述

如何找到每个部门出勤率最低的前 5 名员工？

---员工表

员工表

--- 部门表

部门表

--- 考勤表

考勤表

我尝试以这种方式找到每个部门的前 5 名薪水，但是如何在此特别适用于员工和部门表的考勤表如何找到出勤率最低的前 5 名员工，请在这方面帮助我 cte。

谢谢

With ctetemp As
(
    Select E.EmpId, E.Ename, E.Salary,D.Dname, 
           DENSE_RANK() OVER(PARTITION BY D.DeptId ORDER BY Salary desc) as rnw
    from EmployeeTbl AS E JOIN Departmet AS D
    ON   D.DeptId = e.Dept_Id_Emp
)
Select * from ctetemp
where rnw IN (5)

标签： sql-servertsql

解决方案

您需要参考考勤表。

我假设“a”在这种情况下意味着“缺席”。

SELECT
  AbsenceSummary.DeptID
 ,AbsenceSummary.EmpId
 ,AbsenceSummary.AbsentDays
 ,AbsenceSummary.AbsentRank
FROM
  (
    SELECT
      dept.DeptId
     ,att.EmpId
     ,COUNT(*) AS AbsentDays
     ,DENSE_RANK() OVER (PARTITION BY dept.DeptId ORDER BY COUNT(*) DESC) AS AbsentRank
    FROM
      attendance att
    INNER JOIN
      employee emp
        ON emp.EmpID = att.EmpId
    INNER JOIN
      department dept
        ON dpt.DeptId = emp.DeptId
    WHERE
      dept.DeptId IN (<the departments you care about>)
        AND att.atdate >= <your minimum date>
        AND att.atdate <= <your maximum date>
        AND att.attendance = 'a' --absent
    GROUP BY
      dept.DeptId
     ,att.EmpId
  ) AbsenceSummary
WHERE
  AbsenceSummary.AbsentRank <= 5

郑重声明，这种情况下的AtId专栏是无用的，可能会适得其反。关键是(EmpId,atDate)。

推荐阅读