SQL条件查询 [英] SQL Conditional Query

问题描述

有人可以帮我吗，我想在此添加条件..如仅显示
员工的总工作时间超过12小时

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 

推荐答案

您是否尝试过以下方法:其中EVENTS.EVENTIME在"2012-05-08 00:00:01"和"2012-05-08 12:59:59"与EMP之间.总工作时间> 12 * 60 * 60吗?

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 ?

尝试一下:

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

这篇关于SQL条件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！