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
Amaan23393
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 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
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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 424
1 Sergey Alexandrovich Kryukov 336
2 Shai Vashdi 318
3 OriginalGriff 265
4 Abhinav S 150
0 Sergey Alexandrovich Kryukov 9,169
1 OriginalGriff 5,290
2 Peter Leow 4,020
3 Maciej Los 3,535
4 Abhinav S 3,263


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