统计上下班时间的数据库筛选问题
各位大大我语言表达能力比较差希望大家不要介意- -

情况如下：
   表的三列为 [id] [time] [device]分别表示工号，打卡时间，打卡设备号（1为上班，2为下班），其中打卡设备号用于区分上班和下班的打卡时间，目前我得到了持续一个月的所有员工的全部打卡记录，都记录在这个表中，现在我想通过sql的筛选计算功能得到类似下面列的新表：
  [id] [InTime] [OutTime] [LT]分别表示工号，上班时间，下班时间，在岗时间（也就是下班减去上班），但由于情况特殊，员工的上下班并没有确切的时间段，24小时内都有人上下班，不知道有没有解决方案呢？
------解决方案--------------------

select a.id,a.time as Intime, b.Time as OutTime, 

    datediff(ss/*以秒记*/,a.time,b.time) as LT 

from tb a cross apply 

  (select top (1)* from tb b where a.ID = b.ID and b.time > a.time 

     and b.device = 2 order by b.time desc)p 

where a.device = 1 

------解决方案--------------------

WITH test (id,TIME,device)

 AS 

 (

 SELECT 1,'2012-09-23 12:10:01',1

 UNION ALL 

 SELECT 1,'2012-09-24 12:10:01',2 --模拟跨天

 UNION ALL 

 SELECT 2,'2012-09-23 12:10:01',1

 UNION ALL 

 SELECT 2,'2012-09-23 14:10:01',2 --模拟当天

 )

 --SELECT * FROM test 

 

 SELECT id,InTime=MAX(CASE WHEN device=1 THEN TIME END ),OutTime=MAX(CASE WHEN device=2 THEN TIME END ),

 CASE WHEN DATEPART(DAY,MAX(CASE WHEN device=1 THEN TIME END ))=DATEPART(DAY,MAX(CASE WHEN device=2 THEN TIME END )) 

 THEN CONVERT(VARCHAR(10),DATEDIFF (hh,MAX(CASE WHEN device=1 THEN TIME END ),MAX(CASE WHEN device=2 THEN TIME END)))+'小时'

 + CONVERT(VARCHAR(10),DATEDIFF (mi,DATEPART(mi,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(mi,MAX(CASE WHEN device=2 THEN TIME END))))

 +'分'+CONVERT(VARCHAR(10),DATEDIFF (ss,DATEPART(ss,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(ss,MAX(CASE WHEN device=2 THEN TIME END))))+'秒' 

 

 ELSE  CONVERT(VARCHAR(10),DATEDIFF (hh,MAX(CASE WHEN device=1 THEN TIME END ),MAX(CASE WHEN device=2 THEN TIME END)))+'小时'

 + CONVERT(VARCHAR(10),DATEDIFF (mi,DATEPART(mi,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(mi,MAX(CASE WHEN device=2 THEN TIME END))))

 +'分'+CONVERT(VARCHAR(10),DATEDIFF (ss,DATEPART(ss,MAX(CASE WHEN device=1 THEN TIME END )),DATEPART(ss,MAX(CASE WHEN device=2 THEN TIME END))))+'秒'

 END 

 FROM test 

 GROUP BY id

 

 /*

 ----------- ------------------- ------------------- --------------------------------------

 1           2012-09-23 12:10:01 2012-09-24 12:10:01 24小时0分0秒

 2           2012-09-23 12:10:01 2012-09-23 14:10:01 2小时0分0秒

 警告: 聚合或其他 SET 操作消除了 Null 值。

 

 (2 行受影响)

 */

--