Error in case when statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error in case when statement

Author

Topic

klbaiju
Starting Member

4 Posts

Posted - 2014-08-06 : 01:05:43

Hi following is a working code

create table #temp(empid numeric(18,0),empname nvarchar(20),intime datetime,outtime datetime)
insert into #temp values(2500,'Sachin','2014-01-01 08:00:00','2014-01-01 10:30:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 11:00:00','2014-01-01 12:45:00:00')
insert into #temp values(2500,'Sachin','2014-01-01 13:35:00','2014-01-01 16:30:00:00')
select EmpId ,CONVERT(VARCHAR(20), InTime,106 ) as workingday,left(CONVERT(TIME,max(outTime),108),5)as outtime ,left(CONVERT(TIME,MIN(InTime),108),5)as intime,EmpName,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 GROUP BY EmpId , EmpName, CONVERT(VARCHAR(20), InTime,106 )
drop table #temp

and its output is

EmpId workingday outtime intime EmpName dstatus
2500 01 Jan 2014 16:30 08:00 Sachin right

i want to add one more field that is calculate early arrival.

condition for that is if min(intime) < 08:30 then earlyarrival = '08:30'- intime else 0

i struck in conversion .

how to solve this

Regards

Baiju

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-08-06 : 07:54:21

SELECT
EmpId,
CONVERT(VARCHAR(20), InTime,106 ) AS WorkingDay,
LEFT(CONVERT(TIME,MAX(outTime),108),5)AS OutTime ,
LEFT(CONVERT(TIME,MIN(InTime),108),5)AS InTime,
EmpName,
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 ),
CASE WHEN LEFT(CONVERT(VARCHAR(10),MIN(InTime),108),5) < '08:30' THEN '08:30' ELSE '0' END AS ColumnName
FROM
#temp
GROUP BY
EmpId,EmpName,CONVERT(VARCHAR(20), InTime,106 )

Regards

Veera

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources