我想用sql对时间求和，然后乘以工资

Question

我正在尝试计算加班时间，然后根据加班时间计算每天的加班工资。

emp_reader_id   in_time                 out_time                TotalHrs            OT
         1002   2019-05-14 08:09:42.000 2019-05-14 23:28:43.000 08:30:00.0000000    14:58:43.0000000    04.58
         1003   2019-05-14 08:18:47.000 2019-05-14 21:59:12.000 08:30:00.0000000    13:29:12.0000000    03.29
         1004   NULL    NULL    08:30:00.0000000    NULL    NULL
         1005   2019-05-14 08:24:29.000 2019-05-14 21:58:46.000 08:30:00.0000000    13:28:46.0000000    03.28
         1006   2019-05-14 08:05:30.000 2019-05-14 23:29:14.000 08:30:00.0000000    14:59:14.0000000    04.59
 select in_time,out_time,shifthrs,TotalHrs,cast( replace(cast(left(case 
 when  (isnull(TotalHrs,'00:00:00')>'10:00:00') then
 convert(time,dateadd(ms,datediff(ss,'10:00:00',
 isnull(TotalHrs,'00:00:00'))*1 
 000,0),114)
 else NULL end,5) as varchar(7)),':','.') as int) from 
 table order by att_date desc

我想要另一列来计算 ot 与每天的工资 ot amt

例如：1.25 加班 * 5.3 每天 OT 工资

= 结果

Answer 1

代码中有一些语法问题，例如将字符串转换为 INT。您能否尝试用以下代码替换选择的最后一列-

   CAST
   (
        REPLACE
        (
            CAST
            (
                LEFT
                (
                    CASE
                        WHEN(ISNULL(TotalHrs, '00:00:00') > '10:00:00')
                            THEN CONVERT(TIME, DATEADD(ms, DATEDIFF(ss, '10:00:00', ISNULL(TotalHrs, '00:00:00')) * 1000, 0), 114)
                        --Your code had: * 1 000
                        --Changed to: * 1000
                        ELSE NULL
                    END
                    , 5
                ) AS VARCHAR(7)
            )
            , ':'
            , '.'
        ) AS FLOAT --Changed from INT to FLOAT
    )