error in case when statement in sqlserver [Resolved]

Posted by Klbaiju under Sql Server on 2/19/2014 | Points: 10 | Views : 1500 | Status : [Member] | Replies : 9
Hi all,
I want to write an if condition before this query
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_in_and_out.

I want to add this condtion
CONVERT(TIME,(InTime),108) > '12:45' and convert(time,(intime),108) < '01:30'

I tried this code
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,

error is
error near end
How to solve this
Regards
Baiju




Responses

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

Up
0
Down

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

Posted by: Bandi on: 2/19/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

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

Up
0
Down
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

Posted by: Bandi on: 2/19/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

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

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

Up
0
Down
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

Posted by: Bandi on: 2/20/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/20/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

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

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

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

Posted by: Bandi on: 2/20/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response