Hi,
this is working code
SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,
--error in below line
RIGHT('0' + CONVERT(varchar, FLOOR(SUM(case when CONVERT(TIME,(InTime),108) < '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI, InTime, OutTime)/60.0 END))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM(case when CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI, InTime, OutTime) END)%60), 2) AS [totalwork_hours)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,dstatus=
(CASE
WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00'
THEN 'L'
WHEN CONVERT(TIME,MIN(InTime),108)>'11:00'
THEN 'halfday'
else 'right' end )
FROM baiju.dbo.hbaemp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
error is in Totalwork_hours
if intime is greater than outtime is working fine
every intime is greater than outtime except first intime
i changed like this
RIGHT('0' + CONVERT(varchar, FLOOR(SUM(case when CONVERT(TIME,(InTime),108) < '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI, OutTime,InTime)/60.0 END))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM(case when CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI,OutTime,InTime) END)%60), 2) AS [totalwork_hour]
but not working fine