Click here to Skip to main content
13,148,387 members (55,363 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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
Amaan23440
Comments
digimanus 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 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"
yunhua_lee 27-Nov-12 21:50pm
   
Why error message shows the column name is 'TotalWorkTime' but not 'TOTALWORKTIME'?
I think it should be case sensitive. Did you change it or your database change it automatically?
yunhua_lee 27-Nov-12 5:54am
   
try having: group by EMP.FIRSTNAME, EMP.LASTNAME having TOTALWORKTIME > 12 * 60 * 60
Amaan23 27-Nov-12 8:33am
   
same error
yunhua_lee 27-Nov-12 21:48pm
   
What's your database?

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

mysql: http://dev.mysql.com/doc/refman/5.1/en/select.html
Amaan23 28-Nov-12 0:06am
   
SQL Server 2008
Amaan23 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 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.170924.2 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2017
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