Aggregate function error in sqlserver

Posted by Klbaiju under Sql Server on 2/13/2014 | Points: 10 | Views : 865 | Status : [Member] | Replies : 6
Hi,following code is working one

.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
FROM [Baiju].[dbo].[HbaEmp]
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

I want to add a case when statement in the code.final code is as follows


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,intime,108)>'08:35' and CONVERT(TIME,intime,108) <'11:00' THEN 'L' WHEN CONVERT(TIME,intime,108)>'11:00' THEN 'halfday' else 'right' end )
FROM [Baiju].[dbo].[HbaEmp]
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
following error is showing

Msg 8120, Level 16, State 1, Line 3
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 3
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 3
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 this

Regards

Baiju




Responses

Posted by: Kshaik on: 2/14/2014 [Member] Starter | Points: 25

Up
0
Down
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 [HbaEmp]

GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kshaik on: 2/14/2014 [Member] Starter | Points: 25

Up
0
Down


executed successfully


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 25

Up
0
Down
Hi,
you cant specify the columns other than in group by in select statement.ofcoursee you can keep using aggregate function.
but in this case you cant do what you want using group by

the following query may help you or mat give you your desired result
SELECT * 
,dstatus=
(CASE
WHEN CONVERT(TIME,intime,108)>'08:35' and CONVERT(TIME,intime,108) <'11:00'
THEN 'L'
WHEN CONVERT(TIME,intime,108)>'11:00'
THEN 'halfday'
else
'right'
end )
FROM [Baiju].[dbo].[HbaEmp]

Hope this helps you
revert if you have any issues


sravan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 25

Up
0
Down
Hi,
Please ignore my previous post
This would give you correct result


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 Temp_EmpTimes
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)


Mark as answer if satisfied

sravan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 2/14/2014 [Member] Starter | Points: 25

Up
0
Down
hi sravan your code is working fine.
I want to solve another problem.

RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2)-convert(varchar(5), max(OutTime)-MIN(InTime),108)
means i want subtract
total work_hours-Emp_Intime.
how it is possible
Regards
Baiju

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/14/2014 [Member] Bronze | Points: 25

Up
0
Down
Mark as answer if WORKED


sravan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response