error in case when statement in sqlserver [Resolved]

Hi bandi
this query returns 1
SELECT case when CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(InTime),108) >'01:30' THEN 1 else 0 end as ValidData
FROM baiju.dbo.HbaEmp

i changed CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(InTime),108)< '01:30'

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

You forgot to give ELSE part value in CASE statement

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_in_and_out

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

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

SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,
--error in below line if i avoid case when it will work
 RIGHT('0' + CONVERT(varchar, FLOOR(SUM(case when CONVERT(TIME,(InTime),108)>'12:45' and CONVERT(TIME,(InTime),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,(InTime),108) <'01:30' then DATEDIFF( MI, InTime, OutTime) END )%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 @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Hi bandi,
when i run ur code [total work_hour(s)] retuns all null values

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

Hi,

Run the below query and let me know if you get any ValidData = 1...

If you are not getting 1 for any record then your input data doesn't match with the CASE condition

SELECT case when CONVERT(TIME,(InTime),108)>'12:45' and CONVERT(TIME,(InTime),108) <'01:30' THEN 1 else 0 end as ValidData
FROM @Emp

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Then make that change in the query too...

SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,
--error in below line if i avoid case when it will work
 RIGHT('0' + CONVERT(varchar, FLOOR(SUM(case when CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(InTime),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,(InTime),108)< '01:30'  then DATEDIFF( MI, InTime, OutTime) END)%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 @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

Let me know the result

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Hi bandi,
Your code is working.thanks once again.
Regards
Baiju

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

You should mark the post which one helped you....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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