时间戳

首页 > 解决方案 > 在 SQL Server 中根据时间范围计算工资

sql - 在 SQL Server 中根据时间范围计算工资

问题描述

我有一个包含员工工作时间的数据集。工资表包含每人不同的工资EmployeeNumber。当新的工资被添加到特定员工的工资表中时，它还包含该工资的开始日期。

在下面的输出中使用左连接显示 2 个薪水，但我只想根据日期（第一列）计算薪水。因此，正确的工资应该是 18,77。

非常感谢所有帮助。

SELECT Cast([date] AS DATE)               AS Date, 
       en.number                          AS EmployeeNumber, 
       Cast([startwork] AS SMALLDATETIME) AS StartTime, 
       Cast([endwork] AS SMALLDATETIME)   AS EndingTime, 
       ew.salary                          AS Salary, 
       ew.startdate                       AS StartDateSalary 

FROM   [employeebeginendsummary] eb 
       LEFT JOIN employee e 
              ON eb.employee_id = e.id 
       LEFT JOIN employeegroup eg 
              ON e.employeegroup_id = eg.id 
       LEFT JOIN employeenumber en 
              ON eb.employee_id = en.employee_id 
       LEFT JOIN employeewage ew 
              ON eb.employee_id = ew.employee_id 
ORDER  BY date DESC

输出：

Date    EmployeeNumber  StartTime   EndingTime  Salary  StartDateSalary
2020-03-13  303 2020-03-13 06:00:00 2020-03-13 08:30:00 17,84   2020-01-06 00:00:00.000
2020-03-13  303 2020-03-13 06:00:00 2020-03-13 08:30:00 18,77   2020-01-27 00:00:00.000
2020-03-13  303 2020-03-13 08:50:00 2020-03-13 12:22:00 17,84   2020-01-06 00:00:00.000
2020-03-13  303 2020-03-13 08:50:00 2020-03-13 12:22:00 18,77   2020-01-27 00:00:00.000

标签： sqlsql-server

解决方案

这是你要找的吗？您需要使用row_number()函数根据日期获取最新记录并对其进行过滤。

select 
Date, 
EmployeeNumber, 
StartTime, 
EndingTime, 
Salary, 
StartDateSalary
from 
(
SELECT Cast([date] AS DATE)               AS Date, 
       en.number                          AS EmployeeNumber, 
       Cast([startwork] AS SMALLDATETIME) AS StartTime, 
       Cast([endwork] AS SMALLDATETIME)   AS EndingTime, 
       ew.salary                          AS Salary, 
       ew.startdate                       AS StartDateSalary,
       row_number() over(partition by EmployeeNumber,[date] order by ew.startdate desc) as rn
FROM   [employeebeginendsummary] eb 
       LEFT JOIN employee e 
              ON eb.employee_id = e.id 
       LEFT JOIN employeegroup eg 
              ON e.employeegroup_id = eg.id 
       LEFT JOIN employeenumber en 
              ON eb.employee_id = en.employee_id 
       LEFT JOIN employeewage ew 
              ON eb.employee_id = ew.employee_id 
ORDER  BY date DESC
)
where rn=1;

推荐阅读