SQL 统计每日上班打卡和下班打卡语句

select A1.card_no,

A1.atte_time as "上班时间",

A2.atte_time as "上班时间1",

A3.atte_time as "上班时间2",

A4.atte_time as "上班时间3",

B1.atte_time as "下班时间",

B2.atte_time as "下班时间1",

B3.atte_time as "下班时间2",

B4.atte_time as "下班时间3"

from (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID

from attetime

where doorinout = 1) T

where T.In_ID = 1) A1

left join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID

from attetime

where doorinout = 1) T

where T.In_ID = 2) A2

on A1.card_no = A2.card_no

left join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID

from attetime

where doorinout = 1) T

where T.In_ID = 3) A3

on A1.card_no = A3.card_no

left join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID

from attetime

where doorinout = 1) T

where T.In_ID = 4) A4

on A1.card_no = A4.card_no

full join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID

from attetime

where doorinout = 0) T

where T.Out_ID = 1) B1

on A1.card_no = B1.card_no

left join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID

from attetime

where doorinout = 0) T

where T.Out_ID = 2) B2

on A1.card_no = B2.card_no

left join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID

from attetime

where doorinout = 0) T

where T.Out_ID = 3) B3

on A1.card_no = B3.card_no

left join (select card_no, atte_time

from (select card_no,

atte_time,

ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID

from attetime

where doorinout = 0) T

where T.Out_ID = 4) B4

on A1.card_no = B4.card_no

投入验证数据如下：

执行结果如下：

没有提供字段名，不方便描述。

假设员工都是上白班，早上上班，下午下班；便可以得出一个结论，每天最早的刷卡记录就是上班时刷的，每天最晚的刷卡记录便是下班记录，不理会中间刷了几次。

1. 先按工号和日子分组，同一天的记录分到一个组，求最早记录和最晚记录。

select emp_id, cast(convert(varchar(10),punch_time,102) as datetime) as workday, min(punch_time) as punch_in, max(punch_time) as punch_out

from TableName

group by emp_id, cast(convert(varchar(10),punch_time,102) as datetime)

-- 其中语句cast(convert(varchar(10),punch_time,102) as datetime) 是去掉打卡时间字段里的时间部分，只保留日期部分，使同一天的记录聚合到同一个组内。

2. 用下班时间减去上班时间就得出上班的小时数。完整的SQL语句如下：

select emp_id, cast(convert(varchar(10),punch_time,102) as datetime) as workday, datediff(hour,min(punch_time) , max(punch_time) ) as hours

from TableName

group by emp_id, cast(convert(varchar(10),punch_time,102) as datetime)

如果有夜班（即跨24:00的班次），就复杂一些了，需要额外处理。

---