Hi bandi
following is working code
SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)],
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)
I want to write a condition for [total work_hour(s)].
I tried this code
SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,
--error in below line if i avoid case when it will work
case when CONVERT(TIME,(InTime),108)>'12:45' and CONVERT(TIME,(InTime),108) <'01:30' then RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) else '' end AS [total work_hour(s)],
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)
but showing this error
Msg 8120, Level 16, State 1, Line 10
Column 'Baiju.dbo.HbaEmp.InTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 10
Column 'Baiju.dbo.HbaEmp.InTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
how to solve
Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator