How to select OT hours from below table?

Question

empid name OThours no of days 1 s 0 1 2 n 2 3 I have a table like below i want to select a record like this when working hours >8 means calculate OT

Nitin Sontakke · Answer

Here is the query please try it: select e.empid e. name  sum ( case when datediff( hour  intime outtime) > 8 then datediff( hour  intime outtime) - 8 else 0 end ) OTHours count (empid) [ No of Days] from Master_Salary ms left outer join Master_Employee e on ms.empid = e.empid group by e.empid e.name

Abhinav Abhishek · Answer

Please use below query : SELECT ID CASE WHEN SUM(HH) % 8 = 0 THEN 0 ELSE SUM(HH) % 8 END AS OTHOURS COUNT (ID) AS NODAYS FROM ( SELECT IDATTDATE DATEDIFF(HHINTIMEOUTTIME) AS HH FROM ATTENDANCE GROUP BY IDATTDATEINTIMEOUTTIME ) S GROUP BY ID

Nitin Sontakke · Answer

just add group by Id at the end of the query....

siva nathan · Answer

im getting this error

siva nathan · Answer

i have a table like this empid logintime logouttime attdate 1 8.15am 18.00pm 01-01-17 2 8.15am 20.00pm 02-01-17 2 8.15am 18.00pm 03-01-17 empid OTHours 1 0 2 2 when oneday present hour is greater than 8 hours means calculate OT hours

Nitin Sontakke · Answer

You could have given the data in the script (text) format. It is easy for us to create one sample table put data there write a fully function query and give it to you.