一条SQL语句的另外几种写法

Question

如下的SQL语句，有没有另外的写法：
select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  from attendanceState 
where not ( datepart(hh,attendanceStartTime) <=departmentStartHour and datepart(mi,attendanceStartTime) <departmentStartMin) 
and attendanceEmloyeeNo=001 and attendanceYear=2009
group by  attendanceEmloyeeNo,employeeName,departmentName
意思是  签到时间 超过 departmentStartHour 和departmentStartMin，为迟到，查询出相关的信息

Answer 1

not ( datepart(hh,attendanceStartTime) <=departmentStartHour and datepart(mi,attendanceStartTime) <departmentStartMin) 感觉好像只有这个地方可以换

Answer 2

select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from attendanceState 
where datepart(hh,attendanceStartTime) >departmentStartHour and 
datepart(mi,attendanceStartTime) >=departmentStartMin 
and attendanceEmloyeeNo=001 and attendanceYear=2009 
group by  
attendanceEmloyeeNo,employeeName,departmentName 

Answer 3

select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from attendanceState 
where datepart(hh,attendanceStartTime) <departmentStartHour or  (datepart(hh,attendanceStartTime)=departmentStartHour and datepart(mi,attendanceStartTime) <departmentStartMin )
and attendanceEmloyeeNo=001 and attendanceYear=2009 
group by  attendanceEmloyeeNo,employeeName,departmentName 

Answer 4

select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from attendanceState 
where  ( datepart(hh,attendanceStartTime) >departmentStartHour 
        or datepart(mi,attendanceStartTime) >= departmentStartMin) 
     and attendanceEmloyeeNo=001 and attendanceYear=2009 
group by  attendanceEmloyeeNo,employeeName,departmentName 

Answer 5

select 
     attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from 
attendanceState 
where  
     ( datepart(hh,attendanceStartTime) >departmentStartHour 
      or datepart(mi,attendanceStartTime) >= departmentStartMin) 
and 
     attendanceEmloyeeNo=001 and attendanceYear=2009 
group by  
     attendanceEmloyeeNo,employeeName,departmentName

Answer 6

select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from attendanceState 
where datepart(hh,attendanceStartTime) >departmentStartHour and 
datepart(mi,attendanceStartTime) >=departmentStartMin 
and attendanceEmloyeeNo=001 and attendanceYear=2009 
group by  
attendanceEmloyeeNo,employeeName,departmentName -- 这个好像不对

Answer 7

都不错，只不过在DATEPART等函数中最好不要有列中其中，以免增加服务器运算负担,正在想

Answer 8

select
 attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from 
attendanceState where  
( datepart(hh,attendanceStartTime) >departmentStartHour OR 
datepart(mi,attendanceStartTime) >=departmentStartMin) and attendanceEmloyeeNo=001 
and attendanceYear=2009 
group by  attendanceEmloyeeNo,employeeName,departmentName 

Answer 9

select
 attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from 
attendanceState where  
( 
abs(datediff(hh,departmentStartHour ,attendanceStartTime)) >0 
OR 
abs(datediff(mi,departmentStartMin,attendanceStartTime))>=0) and attendanceEmloyeeNo=001 
and attendanceYear=2009 
group by  attendanceEmloyeeNo,employeeName,departmentName 
不过这样的话,有计算列不知道效果如果

Answer 10

--设上班截止时间是8:00:00,这是迟到1分, 528480是一天中的分钟数
select datediff(n,'2009-1-1 8:00:00','2009-2-1 8:01:00')%528480
--结果-1

Answer 11

正解:declare @minPerDay int ,@minOfDay int
select  @minPerDay= datediff(n,'2008-1-1','2008-1-2')
select  @minOfDay= datediff(n,'2008-1-1','2008-1-1 8:00:00')select @minPerDay 每天的分钟数
select @minOfDay  截止时间的分钟数--迟到1分
select datediff(n,0,'2009-1-30 8:01:00')%@minPerDay-@minOfDay
--早到1分
select datediff(n,0,'2010-3-1 7:59:59')%@minPerDay-@minOfDay

Answer 12

select attendanceEmloyeeNo ,employeeName  ,departmentName ,count(*) as chidaoNum  
from attendanceState 
where  ( datepart(hh,attendanceStartTime) >departmentStartHour 
        or datepart(mi,attendanceStartTime) >= departmentStartMin) 
    and attendanceEmloyeeNo=001 and attendanceYear=2009 
group by  attendanceEmloyeeNo,employeeName,departmentName 

Answer 13

好多帖子的语句有问题!比如截止时间是8:30:00,如果是7:31分,好多帖子的语句都会认为是迟到!
问题出在:
( datepart(hh,attendanceStartTime) >departmentStartHour or datepart(mi,attendanceStartTime) >= departmentStartMin)
建议改成如:
( datepart(hh,attendanceStartTime) >departmentStartHour or (datepart(hh,attendanceStartTime) =departmentStartHour and datepart(mi,attendanceStartTime) > departmentStartMin))