SQL Conditional Query

Question

4.00/5 (1 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

SQL

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

Amaan23

Add a Solution

Comments

Herman<T>.Instance 27-Nov-12 5:57am

if you set the - sign's to / signs Sql Server will automatically convert the varchar to a datetime value

2 solutions

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 ?

Posted 26-Nov-12 21:53pm

WebMaster

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"

[no name] 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?

[no name] 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

[no name] 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

dimpledevani · Accepted Answer · 2012-11-26T23:54:00

Try this:

SQL

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