关于考勤打卡查询sql

[Dingzi0312]

id checktime checktype
001 2009-01-21 08:30:48 0
002 2009-01-21 08:30:48 0
003 2009-01-21 80:31:00 0
001 2009-01-21 10:31:48 2
001 2009-01-21 11:31:48 3
002 2009-01-21 14:31:48 2
001 2009-01-21 15:31:48 2

id是员工号,checktime打卡时间 checktype打卡类型 0是上班 2是外出 3是外出返回 1是下班
现在想查询当前在岗的人员记录,也就是包括上班中和外出返回的
但是同一个员工可能外出返回了一次,又再一次外出未返回..
请问sql语句怎么写...

[ws_hgo]

先看下

[ws_hgo]

感觉你的逻辑有问题
应该查询某个时间段的
在岗人数

[Dingzi0312]

就是查询从上班到当前这个时间段内的在岗人数..

[ws_hgo]

[Quote=引用 3 楼 Dingzi0312 的回复:]
就是查询从上班到当前这个时间段内的在岗人数..
[/Quote]
几点到几点

[Dingzi0312]

8:00-17:00

[ws_hgo]

create table #PP
(
  ID char(6),
  checktime datetime,
  checktype int
)
insert into #PP select '001','2009-01-21 08:30:48',0
union all select '002','2009-01-21 08:30:48',0
union all select '003','2009-01-21 08:31:48',0
union all select '001','2009-01-21 10:31:48',2
union all select '001','2009-01-21 11:31:48',3
union all select '002','2009-01-21 14:31:48',2
union all select '001','2009-01-21 15:31:48',2

/*
 checktype打卡类型 0是上班 2是外出 3是外出返回 1是下班 
现在想查询当前在岗的人员记录,也就是包括上班中和外出返回的 
但是同一个员工可能外出返回了一次,又再一次外出未返回.. 
请问sql语句怎么写...
*/

select * from #PP where checktype in(0,3) and checktime 
between '2009-01-21 08:00' and '2009-01-21 17:00'

ID     checktime               checktype
------ ----------------------- -----------
001    2009-01-21 08:30:48.000 0
002    2009-01-21 08:30:48.000 0
003    2009-01-21 08:31:48.000 0
001    2009-01-21 11:31:48.000 3

[Dingzi0312]

id为001的15:31又外出,未返回..说明不在岗啊..??

这个语句没有把这个情况考虑进去吧???

[you_tube]

楼上不对

[ws_hgo]

这个是在岗的啊

[Dingzi0312]

我的意思是在这段时间内,实际在岗的人员,而不是记录
比如001这个人 8:30时在岗, 10点-11点外出又返回了,15点又外出未返回,这样算不在岗
再比如003这个人,8:30-目前,除了上班打卡,再也没有记录,说明一直在岗

[you_tube]

DECLARE  @tab TABLE (
id  VARCHAR(32),   
checktime DATETIME,             
checktype int)
INSERT INTO @tab 
SELECT '001','2009-01-21 08:30:48',0  UNION ALL 
SELECT '002','2009-01-21 08:30:48',0  UNION ALL 
SELECT '003','2009-01-21 12:31:00',0   UNION ALL 
SELECT '001','2009-01-21 10:31:48',2  UNION ALL 
SELECT '001','2009-01-21 11:31:48',3  UNION ALL 
SELECT '002','2009-01-21 14:31:48',2  UNION ALL 
SELECT '001','2009-01-21 15:31:48',2 

SELECT * FROM (
SELECT T1.id ,  ISNULL((INput - out),0) COUNTER   FROM (SELECT id,count(checktype) INput FROM  @tab t WHERE checktype = 0
GROUP BY id )  T1
LEFT JOIN (SELECT id,count(checktype) Out FROM  @tab t WHERE checktype = 2
GROUP BY id ) T2
ON t1.id = t2.id ) T
WHERE COUNTER >= 0

id                               COUNTER
-------------------------------- -----------
002                              0
003                              0

[you_tube]

DECLARE  @tab TABLE (
id  VARCHAR(32),   
checktime DATETIME,             
checktype int)
INSERT INTO @tab 
SELECT '001','2009-01-21 08:30:48',0  UNION ALL 
SELECT '002','2009-01-21 08:30:48',0  UNION ALL 
SELECT '003','2009-01-21 12:31:00',0   UNION ALL 
SELECT '001','2009-01-21 10:31:48',2  UNION ALL 
SELECT '001','2009-01-21 11:31:48',3  UNION ALL 
SELECT '002','2009-01-21 14:31:48',2  UNION ALL 
SELECT '001','2009-01-21 15:31:48',2 

SELECT id,CASE  WHEN COUNTER >=0 THEN '在职'
ELSE '外出未回'
END

 FROM (
SELECT T1.id ,  ISNULL((INput - out),0) COUNTER   FROM (SELECT id,count(checktype) INput FROM  @tab t WHERE checktype = 0
GROUP BY id )  T1
LEFT JOIN (SELECT id,count(checktype) Out FROM  @tab t WHERE checktype = 2
GROUP BY id ) T2
ON t1.id = t2.id ) T

id                               出勤
-------------------------------- --------
001                              外出未回
002                              在职
003                              在职

[you_tube]

SELECT T1.id ,  CASE WHEN ISNULL((INput - out),0) >= 0 THEN '在职' ELSE '外出未归'
 end '出勤'  FROM ( SELECT id,count(checktype) INput FROM  @tab t WHERE checktype = 0
GROUP BY id )  T1
LEFT JOIN (SELECT id,count(checktype) Out FROM  @tab t WHERE checktype = 2
GROUP BY id ) T2
ON t1.id = t2.id 

不过还要取得刷卡时间最大的数据

[xiguofeng]

select b.ID
from (
select ID,MAX(checktime) 'dat'
from #PP
group by Id
) a ,#PP b
where b.checktime=a.dat and a.id=b.id and( a.id>2 or a.id<1)