How to select OT hours from below table?

Nitin Sontakke9y ago

Accepted 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

Nitin Sontakke9y ago

just add

group by Id

at the end of the query....

siva nathan9y ago

im getting this error

Abhinav Abhishek9y ago

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 ID,ATTDATE, DATEDIFF(HH,INTIME,OUTTIME) AS HH FROM ATTENDANCE GROUP BY ID,ATTDATE,INTIME,OUTTIME

) S

GROUP BY ID

siva nathan9y ago

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 Sontakke9y ago

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.

empid	name	OThours	no of days
1	s	0	1
2	n	2	3