Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Can someone help me, i want to add condition on this .. such as it should only show
the emp whos totalworkTime is more then 12 hours
 
 
select EMP.FIRSTNAME, 
EMP.LastName, MIN(CONVERT(varchar,Eventime,14)) as TIMEIN, max(CONVERT(varchar,Eventime,14)) as TIMEOUT,
CONVERT(VARCHAR(20),DateAdd(SS,Datediff(ss,MIN(CONVERT(varchar,Eventime,14)), max(CONVERT(varchar,Eventime,14)))%(60*60*24),0),114) AS TOTALWORKTIME
from EMP inner join EVENTS on EVENTS.EMPID = EMP.ID 
where EVENTS.EVENTIME  between '2012-05-08 00:00:01' and '2012-05-08 12:59:59' 
group by EMP.FIRSTNAME, EMP.LASTNAME 
Posted 26-Nov-12 21:39pm
Amaan23424
Comments
digimanus at 27-Nov-12 5:57am
   
if you set the - sign's to / signs Sql Server will automatically convert the varchar to a datetime value
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Did you try this: where EVENTS.EVENTIME between '2012-05-08 00:00:01' and '2012-05-08 12:59:59' and EMP.TOTALWORKTIME > 12 * 60 * 60 ?
  Permalink  
Comments
Amaan23 at 27-Nov-12 4:59am
   
thanks for the reply, but this will not work surely and as you said, i have tried that... it gives Error "Invalid Column name " TotalWorkTime"
Amaan23 at 27-Nov-12 8:33am
   
same error
Amaan23 at 28-Nov-12 0:06am
   
SQL Server 2008
Amaan23 at 29-Nov-12 5:17am
   
any more suggestion. still struck with this :( please help
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this:
 

select EMP.FIRSTNAME,
EMP.LastName, MIN(CONVERT(varchar,Eventime,14)) as TIMEIN, max(CONVERT(varchar,Eventime,14)) as TIMEOUT,
CONVERT(VARCHAR(20),DateAdd(SS,Datediff(ss,MIN(CONVERT(varchar,Eventime,14)), max(CONVERT(varchar,Eventime,14)))%(60*60*24),0),114) AS TOTALWORKTIME
from EMP inner join EVENTS on EVENTS.EMPID = EMP.ID
where EVENTS.EVENTIME  between '2012-05-08 00:00:01' and '2012-05-08 12:59:59' and  CONVERT(VARCHAR(20),DateAdd(SS,Datediff(ss,MIN(CONVERT(varchar,Eventime,14)), max(CONVERT(varchar,Eventime,14)))%(60*60*24),0),114)>12
group by EMP.FIRSTNAME, EMP.LASTNAME
  Permalink  
Comments
Amaan23 at 27-Nov-12 8:32am
   
thanks friend. but
its showing error
 
Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Amaan23 at 29-Nov-12 7:37am
   
Solved !!! thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CPallini 330
1 Sergey Alexandrovich Kryukov 313
2 George Jonsson 291
3 Prasad Avunoori 155
4 OriginalGriff 149
0 OriginalGriff 4,623
1 CPallini 3,410
2 Sergey Alexandrovich Kryukov 2,929
3 George Jonsson 2,319
4 Gihan Liyanage 2,077


Advertise | Privacy | Mobile
Web01 | 2.8.140905.1 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100