Hi I am using below query for employees work hours calulation
select transdate,round(isnull(sum(case when CONVERT(float, SUBSTRING (totalhrs,1,2))+ round((CAST( substring(totalhrs,4,2) as float)/60),2) < 8 then
CONVERT(float, SUBSTRING (totalhrs,1,2))+round((CAST( substring(totalhrs,4,2) as float)/60),2)
else 10 end ),0),0) as hrs from timesheetentry inner join project on projectid = projid
where psno='20006869' and transdate between
'2016-07-01' and '2016-07-22' and LN_Proj_Code='DNB045001'
group by transdate
getiing result is below .
transdate hrs
2016-07-01 9
2016-07-02 4
2016-07-04 7
2016-07-05 8
2016-07-06 11
2016-07-07 3
2016-07-08 9
2016-07-11 9
2016-07-12 8
2016-07-13 7
2016-07-14 9
2016-07-15 12
2016-07-16 8
2016-07-18 5
2016-07-19 7
2016-07-20 9
2016-07-21 7
if empoyess working 8 hours or below 8 hours per day need display actual hours only but if working hours more then 8 hours at time also i want calculate 8 hours only
Please view query
Thanks
venkat
