CODEWITHSUNDEEP
sqlsql-server
Dolu bolu
Dolu bolu

Reputation: 189

I want to sum time and then multiply with salary using sql

I am trying to calculate overtime hour ,then based on overtime hour calculate with per day overtime salary. 

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

I want another column to calculate ot with salary ot amt per day

E.G: 1.25 overtime * 5.3 per day ot salary

= result

Upvotes: 0

Views: 86

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

You have some Syntax issue in your code like converting STRING to INT. Can you please try with replace you last column in selection with this below code-

   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
    )

Upvotes: 3

Related Questions