tsql - 查询考勤卡数据库

Question

我有一张工作人员打卡打卡的时间表，布局如下:

employeeID  Clock-in DateTime   Clock Type
1004        24/09/2013 13:08    in
1004        25/09/2013 00:50    out
1005        24/09/2013 12:08    in
1005        25/09/2013 03:55    out
2003        24/09/2013 10:06    in
2003        24/09/2013 15:42    out
2003        24/09/2013 17:33    in
2003        24/09/2013 22:17    out

我怎样才能让它变成这样:

Date        employeeID  Clock-in            Clock-out           time worked (in hours to 2 decimal place)
24/09/2013  1004        24/09/2013 13:08    25/09/2013 00:50    xx.xx
24/09/2013  1005        24/09/2013 12:08    25/09/2013 03:55    xx.xx
24/09/2013  2003        24/09/2013 10:06    24/09/2013 15:42    xx.xx
24/09/2013  2003        24/09/2013 17:33    24/09/2013 22:17    xx.xx

笔记:

员工可以有多个打卡进/出(即上午/下午，夜类)

日期从 04:00:00 开始至 03:59:59 (即在上面的例子中，所有分类为 24/09/2013 )

如何在没有下类的情况下处理记录？我可以设置类似 if null 的内容吗？ , 时间等于下一个日期 03:59:59 ?

提前致谢。

我用 jods 建议更新了以下内容:

3 组输入/输出记录的示例

EmployeeID  CardDate            PunchType
1000        21/09/2013 07:43    in
1000        21/09/2013 11:29    out
1000        21/09/2013 12:39    in
1000        21/09/2013 20:37    out
1000        21/09/2013 21:58    in
1000        22/09/2013 00:16    out

您的查询返回

Date        EmployeeID  clockInTime clockOutTime
21/09/2013  1000        07:43:57    00:16:22
21/09/2013  1000        21:58:40    11:29:22

Answer 1

如果您使用 SQL SERVER 2005 及更高版本。你可以试试这个

WITH cte AS (
SELECT
   [employeeID]
    ,ti.ClockIn
    ,ClockOut = (SELECT MIN(t.ClockIn) 
                 FROM timecard t 
                 WHERE ti.employeeID = t.employeeID 
                 AND t.Type = 'out' 
                 AND t.ClockIn > ti.ClockIn
                 AND t.ClockIn < DATEADD(hour, 24 + 4 , CAST(CAST(ti.ClockIn AS DATE) AS datetime))
                )  
    ,NextDayLimit = DATEADD(SECOND, -1, DATEADD(hour, 24 + 4 , CAST(CAST(ti.ClockIn AS DATE) AS datetime)))
FROM timecard ti
WHERE Type = 'in'
)
SELECT 
    employeeID,
    ClockIn,
    ISNULL(ClockOut, NextDayLimit) AS ClockOut,
    CAST(DATEDIFF(minute, ClockIn, ISNULL(ClockOut, NextDayLimit))/60.0 AS NUMERIC(19,2)) AS TimeWorked
from cte
ORDER BY    employeeID, ClockIn