根据SQL Server中的总小时数计算工资

How can I calculate salary base on total Hours? I have two tables and a query that will compute the total hours per day.

如何根据总工时计算工资?我有两个表和一个计算每天总小时数的查询。

Table officer_timelogs

employee_id      record_time             Day      type
--------------------------------------------------------
125         2018-02-27 18:03:31.000    Tuesday     1
125         2018-02-27 07:54:03.000    Tuesday     0

Table officer_rate

employee_id     designation      salary
125             programmer       100 (hour)

SQL Query:

select
    employee_id, 
    [Date], DATENAME(WEEKDAY, date)as [Day], 
    [Hours] = right(concat('00', diff / 3600), 2) + ' : ' + right(concat('00', diff % 3600 / 60), 2) + ' : ' + right(concat('00', diff % 60), 2)
from (
    select
        employee_id, 
        [date] = cast(record_time as date),
        diff = datediff(ss, min(iif(type = 0, record_time, null)), max(iif(type = 1, record_time, null)))
    from
        officer_timelogs
    where employee_id = '125'
    group by employee_id, cast(record_time as date)
) t
order by date desc

Output:

employee_id   Date       Day           Hours
    125    2018-03-02   Friday      09 : 00 : 00
    125    2018-03-01   Thursday    10 : 10 : 49
    125    2018-02-28   Wednesday   10 : 14 : 11
    125    2018-02-27   Tuesday     10 : 09 : 28
    125    2018-02-26   Monday      10 : 13 : 34

Desired output (I want to retrieve data like this)

期望的输出(我想要检索这样的数据)

employee_id   Date       Day           Hours        Salary
    125    2018-03-02   Friday      09 : 00 : 00     900

2 个解决方案

#1

I think you've done the most complex part.

我认为你做过最复杂的部分。

Since you already have the worked time, you just need to multiply by the salary, like this:

由于你已经有了工作时间,你只需要乘以工资,如下所示:

select
    t.employee_id, 
    [Date], DATENAME(WEEKDAY, date)as [Day], 
    [Hours] = right(concat('00', diff / 3600), 2) + ' : ' + right(concat('00', diff % 3600 / 60), 2) + ' : ' + right(concat('00', diff % 60), 2),
    diff * r.salary / 3600 AS Salary

from (
    select
        employee_id, 
        [date] = cast(record_time as date),
        diff = datediff(ss, min(iif(type = 0, record_time, null)), max(iif(type = 1, record_time, null)))
    from
        officer_timelogs
    where employee_id = '125'
    group by employee_id, cast(record_time as date)
) t
INNER JOIN officer_rate r ON t.employee_id = r.employee_id
order by date desc

You can check it live on this SQL Fiddle.

你可以在这个SQL小提琴上查看它。

Edit: Code and demo updated with OT rate:

In this case, you need to check if OT should be applied. If the person worked 8h or less, we consider the regular rate. The difference for the 8h is calculated by considering 20% of the hourly salary:

在这种情况下,您需要检查是否应该使用OT。如果该人工作8小时或更短时间,我们会考虑常规费率。 8小时的差异是通过考虑20%的小时工资来计算的:

select
    t.employee_id, 
    [Date], DATENAME(WEEKDAY, date)as [Day], 
    [Hours] = right(concat('00', diff / 3600), 2) + ' : ' + right(concat('00', diff % 3600 / 60), 2) + ' : ' + right(concat('00', diff % 60), 2),
    CAST(
    CASE WHEN diff <= 3600 * 8
        THEN diff * r.salary / 3600
        ELSE 
            (3600 * 8 * r.salary / 3600) -- salary x 8h / work
            + (diff - (3600 * 8)) * r.salary * 0.2 /3600 -- OT work
        END AS decimal(10, 2)) AS Salary
from (
    select
        employee_id, 
        [date] = cast(record_time as date),
        diff = datediff(ss, min(iif(type = 0, record_time, null)), max(iif(type = 1, record_time, null)))
    from
        officer_timelogs
    where employee_id = '125'
    group by employee_id, cast(record_time as date)
) t
INNER JOIN officer_rate r ON t.employee_id = r.employee_id
order by date desc;

Demo updated here

演示在这里更新

#2

Maybe this. I bet you need to convert hours to decimal such that 1 hour and 30 minutes will make 1.5

也许这个。我打赌你需要将小时数转换为小数,这样1小时30分钟就会达到1.5

select
    employee_id, 
    [Date], DATENAME(WEEKDAY, date)as [Day], 
    [Hours] = right(concat('00', diff / 3600), 2) + ' : ' + right(concat('00', diff % 3600 / 60), 2) + ' : ' + right(concat('00', diff % 60), 2),
    Salary = <However you need to get hour(s) as a decimal> * tl2.salary
from (
    select
        employee_id, 
        [date] = cast(record_time as date),
        diff = datediff(ss, min(iif(type = 0, record_time, null)), max(iif(type = 1, record_time, null)))
    from
        officer_timelogs
    where employee_id = '125'
    group by employee_id, cast(record_time as date)
) t
INNER JOIN officer_rate tl2 ON tl2.employee_id=t.employee_id
order by date desc