# 出勤管理数据表在项目上应用举例

# EB搭建缺勤报表

select MAX(a.id) AS ID,  
a.EMPLOYEE AS '缺勤人员',  
a.department AS '部门',  
a.attend_date AS '缺勤日期'  
from (select a.id,  
a.EMPLOYEE,  
b.department,  
a.attend_date  
from ATTEND_STATUS_DETAIL AS a  
left join eteams.dbo.employee AS b on a.EMPLOYEE = b.ID  
where EMPLOYEE not in (select EMPLOYEE  
from ATTEND_STATUS_DETAIL  
where (SIGN_IN_RECORD is not null)  
AND attend_date = '2024-08-01')  
AND attend_date = '2024-08-01'  
and a.delete_type = 0) a  
GROUP BY a.employee,  
a.department,  
a.attend_date

参考客户:福建冠维汽配

项目人员:陈毅滨

# 查询应出勤时长，实际出勤时长

PERIOD_LENGTH_DAY--应出勤天数

PERIOD_LENGTH_MSEC--应出勤毫秒数

WORK_LENGTH_DAY--出勤天数

WORK_LENGTH_MSEC--出勤毫秒数

ACTUAL_WORK_LENGTH_DAY--实际出勤天数

ACTUAL_WORK_LENGTH_MSEC--实际出勤毫秒数

select S.PERIOD_LENGTH_MSEC, s.WORK_LENGTH_MSEC,
s.ACTUAL_WORK_LENGTH_MSEC, S.EMPLOYEE, S.ATTEND_DATE, AST.NAME  
FROM ATTEND_STATUS_DETAIL S  
LEFT JOIN ATTEND_PERIOD P ON P.ID = S.ATTEND_PERIOD and P.delete_type =
0  
LEFT JOIN attend_shift AST ON P.ATTEND_SHIFT_ID = AST.ID and
AST.delete_type = 0

where s.delete_type=0 and s.day_type='WORK'

# 当月加班时长

select sum(OVER_LENGTH_MSEC) / (1000 * 60 * 60) as hour,  
sum(OVER_LENGTH_MSEC) / (1000 * 60) as minute,  
sum(OVER_LENGTH_DAY) as day  
from attend_overtime_detail  
where ATTEND_DATE \>= '2022-01-01'  
and ATTEND_DATE \<= '2022-06-30'  
and EMPLOYEE = 738609108068179969 and delete_type = 0  
group by EMPLOYEE

客户的使用场景是在流程里配置了sql联动查询当月的加班时长，EMPLOYEE 和
ATTEND_DATE的参数都是通过字段联动配置出来的

参考客户:新美材料

项目人员:陈超凡

# 获取考勤报表中的部分字段

select *

from (SELECT SUM(CASE DAY_TYPE WHEN 'WORK' THEN
ifnull(PERIOD_LENGTH_DAY, 0) ELSE 0 END) AS 应出勤天数,

SUM(CASE DAY_TYPE

WHEN 'WORK' THEN ifnull(ACTUAL_WORK_LENGTH_DAY, 0)

ELSE 0 END) AS 实际出勤天数,

COUNT(CASE FINAL_SIGN_IN_STATUS WHEN 'SIGN_IN_LATE' THEN 1 END) AS
迟到次数,

COUNT(CASE

WHEN FINAL_SIGN_IN_STATUS = 'SIGN_IN_ABSENCE' AND SIGN_OUT_RECORD IS NOT
NULL

THEN 1 END) AS 未签到次数,

COUNT(CASE FINAL_SIGN_OUT_STATUS WHEN 'SIGN_OUT_EARLY' THEN 1 END) AS
早退次数,

COUNT(CASE

WHEN FINAL_SIGN_OUT_STATUS = 'SIGN_OUT_ABSENCE' AND SIGN_IN_RECORD IS
NOT NULL

THEN 1 END) AS 未签退次数,

COUNT(CASE

WHEN FINAL_SIGN_IN_STATUS = 'SIGN_IN_ABSENCE' AND SIGN_OUT_RECORD IS
NULL

THEN 1 END) AS 未打卡次数

FROM ATTEND_STATUS_DETAIL D

WHERE D.TENANT_KEY = 't4mvz69ph3'

AND D.ATTEND_DATE BETWEEN '2024-08-01' and '2024-08-31'

and D.delete_type = 0

and D.employee = 837013620493729802

and D.delete_type = 0) a

inner join

(SELECT COUNT(distinct case when APPEAL_TYPE = 'leave' then FLOW_DATA
end) AS 请假次数,

sum(case when APPEAL_TYPE = 'leave' THEN APPEAL_LENGTH_DAY ELSE 0 END)
as 请假天数,

SUM(CASE WHEN APPEAL_TYPE = 'businessTrip' THEN APPEAL_LENGTH_DAY ELSE 0
END) AS 出差天数

FROM ATTEND_STATUS_DETAIL_LINK

WHERE TENANT_KEY = 't4mvz69ph3'

AND ATTEND_DATE BETWEEN '2024-08-01' and '2024-08-31'

and employee = 837013620493729802

and delete_type = 0) b

参考客户:富煌集团

项目人员:胡雨欣

# 统计外勤签到的次数和外勤时长

外勤次数

SELECT

EMPLOYEE AS employee,

COUNT(ID) AS oattend_num

FROM TIMECARD

WHERE TENANT_KEY = \#{tenantKey} AND TYPE = 'OCHECKIN' AND ADD_TIME
BETWEEN \#{beginDate} AND \#{endDate}

AND EMPLOYEE IN ()

AND delete_type=0

GROUP BY EMPLOYEE

外出时长

SELECT T.EMPLOYEE AS employee, SUM(TOS.TIME_LENGTH_MSEC)*1.00/3600000
AS oattend_length_hour

FROM timecard_o_signout TOS

LEFT JOIN timecard T ON T.ID = TOS.TIMECARD_ID

WHERE TOS.TENANT_KEY = \#{tenantKey}

AND T.TYPE = 'OCHECKIN' AND T.ADD_TIME BETWEEN \#{beginDate} AND
\#{endDate}

AND T.EMPLOYEE IN ()

GROUP BY T.EMPLOYEE

# 获取请假类型是病假的时长

select * from attend_vacation_setting where delete_type=0 and
tenant_key='' and name = '你的病假名称'

select * from attend_status_detail_link where APPEAL_TYPE='leave' and
delete_type=0 and VACATION_TYPE=前面sql查询到的病假类型id and
TENANT_KEY=''

# 把调休明细全部铺开显示

select

id,

employee as '人员id',

employee as '人员',

(select USERNAME from eteams.employee where id=employee) as '人员姓名',

(select PERSONNEL_STATUS from eteams.employee where id=employee) as
'人员状态',

vacation_type as '假期类型',

vacation_value as '调休额度',

effective_date as '生效日',

expired_date as '到期日',

source as '来源',

year as '所属年',

overtime_date as '加班日期',

extra as '额外余额',

used as '已休余额',

if(expired_date\<(select curdate()),vacation_value+extra-used,0) as
'失效额度'

from

attend_vacation_info_detail_v2 where delete_type=0

# 想要查一个人当月里面不关联调休的加班时长

select * from attend_overtime_detail where TENANT_KEY='t5ntigbkrl' and
EMPLOYEE= and IS_LIEU_LEAVE=0 and delete_type=0

and ATTEND_DATE between '' and ''

# 考勤报表里状态和打卡时间字段咨询

考勤状态可以参考

关于考勤报表里状态的字段咨询

select
a.SIGN_IN_STATUS,a.FINAL_SIGN_IN_STATUS,a.SIGN_OUT_STATUS,a.FINAL_SIGN_OUT_STATUS
from attend_status_detail a

STATUS是原始状态，没有经过考勤流程处理的，FINAL是最终状态，具体取值参考下面

SIGN_IN_NORMAL("正常签到"),

SIGN_IN_LATE("迟到"),

SIGN_IN_ABSENCE("未签到"),

SIGN_IN_ABSENTEEISM("旷工"),

SIGN_OUT_NORMAL("正常签退"),

SIGN_OUT_ABSENCE("未签退"),

SIGN_OUT_EARLY("早退"),

SIGN_OUT_ABSENTEEISM("旷工"),

SIGN_IN_ABSENCE_ONLY("未签到"),

OATTEND("外勤"),

OCHECKIN("外勤签到"),

OCHECKOUT("外勤签退"),

NO_SIGN("免打卡"),

SERIOUSLY_LATE("严重迟到"),

SERIOUSLY_EARLY("严重早退");

打卡时间可以参考：

select SIGN_IN_RECORD,SIGN_OUT_RECORD,SIGN_RANGE_START,SIGN_RANGE_END
from attend_status_detail

SIGN_IN_RECORD 签到id对应timecard的id

SIGN_OUT_RECORD 签到id对应timecard的id

SIGN_RANGE_START 签到时间-开始时间

SIGN_RANGE_END 签退时间-结束时间

# 排班表咨询

排班表使用如下

select * from attend_scheduling where TENANT_KEY = 'T4MVZ69PH3' and
ATTEND_CONFIG_ID = 120531350000000004 and RANGE_ID = 120100132000000002
and delete_type = 0

-- ATTEND_CONFIG_ID是所在规则的ID，

-- 考勤规则的使用范围范围添加的是具体的人，

那么 rang_id就是考勤attend_config_range的的id值；

如果适用范围添加的的部门/分部 查询具体人员的排班的rang_id就是员工ID

attend_config_range表结构如下

<img
src="media/出勤管理数据表在项目上应用举例/media/image1.png"
style="width:5.76319in;height:2.18958in" />

查询某个人某个日期的排班班次：

select T.id,T.NAME

from attend_scheduling S

left join attend_shift T on T.id=S.SHIFT_ID

where S.TENANT_KEY = '团队识别码'

and S.ATTEND_CONFIG_ID = (select max(attend_config)

from attend_status_detail

where TENANT_KEY = '团队识别码'

and EMPLOYEE = 人员id

and attend_date = '当前今天的日期yyyy-MM-dd'

and delete_type = 0)

and S.RANGE_ID = ifnull((select id from attend_config_range where
TENANT_KEY = '团队识别码' and ATTEND_CONFIG = (select max(attend_config)

from attend_status_detail

where TENANT_KEY = '团队识别码'

and EMPLOYEE = 人员id

and attend_date = '当前今天的日期yyyy-MM-dd'

and delete_type = 0) and target_id = 人员id and delete_type=0), 人员id)

and S.attend_date='想要查询班次的日期yyyy-MM-dd'

and S.delete_type = 0;

根据人员、日期带出排班班次

SELECT SHIFT_ID  
FROM attend_scheduling  
WHERE TENANT_KEY = 'tzlca83gtc'  
AND ATTEND_CONFIG_ID = (  
SELECT S.ATTEND_CONFIG  
FROM ATTEND_STATUS_DETAIL S  
WHERE s.delete_type = 0  
AND s.EMPLOYEE = '1181769077880029193'  
AND s.ATTEND_DATE = '2026-01-30'  
AND S.TENANT_KEY IN ('tzlca83gtc', 'all_teams')  
)  
AND delete_type = 0  
AND ATTEND_DATE = '2026-02-26'  
AND attend_scheduling.TENANT_KEY IN ('tzlca83gtc', 'all_teams')  
AND RANGE_ID = ifnull((select id from attend_config_range where TENANT_KEY = 'tzlca83gtc'   
and ATTEND_CONFIG = (select max(attend_config) from attend_status_detail  
                            where TENANT_KEY = 'tzlca83gtc'  
                              and EMPLOYEE = 1181769077880029193  
                              and attend_date = '2026-01-30'  
                              and delete_type = 0)   
and target_id = 1181769077880029193 and delete_type = 0), 1181769077880029193)

# 查询今日之前的人员日期和班次

select S.ATTEND_PERIOD,p.ATTEND_SHIFT_ID,ast.id  
FROM ATTEND_STATUS_DETAIL S  
LEFT JOIN ATTEND_PERIOD P ON P.ID = S.ATTEND_PERIOD and P.delete_type =
0  
LEFT JOIN attend_shift AST ON P.ATTEND_SHIFT_ID = AST.ID and
AST.delete_type = 0  
where s.delete_type=0 and s.EMPLOYEE=人员id and s.ATTEND_DATE='日期'

字段说明:  
S.ATTEND_PERIOD对应ATTEND_PERIOD的id  
ATTEND_PERIOD的ATTEND_SHIFT_ID对应attend_shift的id  
attend_shift是班次表

ATTEND_DAT:'日期'

# 查询人员日期属性

select S.day_type  
FROM ATTEND_STATUS_DETAIL S  
LEFT JOIN ATTEND_PERIOD P ON P.ID = S.ATTEND_PERIOD and P.delete_type =
0  
LEFT JOIN attend_shift AST ON P.ATTEND_SHIFT_ID = AST.ID and
AST.delete_type = 0  
where s.delete_type=0 and s.EMPLOYEE=人员id and s.ATTEND_DATE='日期'

字段说明:  
day_type属性:

WORK:工作日

PLAYDAY:休息日

HOLIDAY:节假日

# 删除流程抵扣打卡的数据

//查询流程抵扣打卡生成的打卡数据

select * from timecard where attend_source like '%flowcard%'

//流程抵扣打卡生成的签到打卡

select signinid from attend_flowcard_link where flow_data in(

select id from attend_flow_data where id in(

select flow_data from attend_status_detail_link where
appeal_type='leave' and delete_type=0

)and delete_type=0

)

//流程抵扣打卡生成的签退打卡

select signoutid from attend_flowcard_link where flow_data in(

select id from attend_flow_data where id in(

select flow_data from attend_status_detail_link where
appeal_type='leave' and delete_type=0

)and delete_type=0

)

# 查询节假日

节假日目前是和考勤规则关联的，需要先查询考勤规则的id

//根据名称查询到考勤规则的最新id (NEWEST_ID )

select NEWEST_ID from stream_config_version where CURRENT_ID in(  
select id from attend_config where name = '排班zss1测试'  
)

//传入前面的NEWEST_ID  
select * from attend_day where config_id=考勤规则id

如果attend_day查询不到就取默认表attend_holiday_default

# 某个部门或者分部被删除了，原考勤相关的规则开启了分权需要把所属机构调整下

这些是和分权所属机构相关的

select * from attend_config where belong_department =
1016556623902572949

select *from attend_base_config where belong_department =
1016556623902572949

select *from attend_shift where belong_department = 1016556623902572949

select *from attend_overtime_rule where belong_department =
1016556623902572949

select * from attend_flow_setting where belong_department =
1016556623902572949

select * from attend_vacation_setting where belong_department =
1016556623902572949

select * from attend_leave_rule where belong_department =
1016556623902572949

这些是和考勤报表共享权限相关的

select *from attend_report_share_setting where
target_id=1016556623902572949

select *from attend_report_share_scope where target_id
=1016556623902572949

# 出勤汇总报表部分字段

SELECT

ASD.employee,

COUNT(CASE WHEN SIGN_IN_RECORD IS NOT NULL THEN 1 END) AS 签到次数,

COUNT(CASE FINAL_SIGN_IN_STATUS WHEN 'SIGN_IN_LATE' THEN 1 END) AS
迟到次数,

COUNT(CASE WHEN SIGN_OUT_RECORD IS NOT NULL THEN 1 END) AS 签退次数,

COUNT(CASE FINAL_SIGN_OUT_STATUS WHEN 'SIGN_OUT_EARLY' THEN 1 END) AS
早退次数,

COUNT(CASE FINAL_SIGN_IN_STATUS WHEN 'SERIOUSLY_LATE' THEN 1 END) AS
严重迟到次数,

COUNT(CASE FINAL_SIGN_OUT_STATUS WHEN 'SERIOUSLY_EARLY' THEN 1 END) AS
严重早退次数,

COUNT(CASE FINAL_SIGN_IN_STATUS WHEN 'SIGN_IN_ABSENTEEISM' THEN 1 END) +

COUNT(CASE WHEN FINAL_SIGN_OUT_STATUS = 'SIGN_OUT_ABSENTEEISM' AND
SIGN_IN_RECORD IS NOT NULL THEN 1 END) AS 旷工次数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN PERIOD_LENGTH_MSEC ELSE 0
END)*1.00/3600000 AS 应出勤小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN PERIOD_LENGTH_DAY ELSE 0 END) AS
应出勤天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN WORK_LENGTH_MSEC ELSE 0
END)*1.00/3600000 AS 出勤小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN WORK_LENGTH_DAY ELSE 0 END) AS
出勤天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN ACTUAL_WORK_LENGTH_MSEC ELSE 0
END)*1.00/3600000 AS 实际出勤小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN ACTUAL_WORK_LENGTH_DAY ELSE 0 END) AS
实际出勤天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN LATE_LENGTH_MESC ELSE 0
END)*1.00/3600000 AS 迟到小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN LATE_LENGTH_DAY ELSE 0 END) AS
迟到天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN SERIOUS_LATE_LENGTH_MESC ELSE 0
END)*1.00/3600000 AS 严重迟到小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN SERIOUS_LATE_LENGTH_DAY ELSE 0 END)
AS 严重迟到天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN EARLY_OUT_LENGTH_MESC ELSE 0
END)*1.00/3600000 AS 早退小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN EARLY_OUT_LENGTH_DAY ELSE 0 END) AS
早退天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN SERIOUS_EARLY_LENGTH_MESC ELSE 0
END)*1.00/3600000 AS 严重早退小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN SERIOUS_EARLY_LENGTH_DAY ELSE 0 END)
AS 严重早退天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN AAD.ABSENTEEISM_LENGTH_MESC ELSE 0
END)*1.00/3600000 AS 旷工小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN AAD.ABSENTEEISM_LENGTH_DAY ELSE 0
END) AS 旷工天数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN

CASE WHEN FINAL_SIGN_IN_STATUS='SIGN_IN_ABSENCE' THEN
ABSENCE_LENGTH_MSEC-IFNULL(AAD.ABSENTEEISM_LENGTH_MESC,0)-IFNULL(EARLY_OUT_LENGTH_MESC,0) -
IFNULL(SERIOUS_EARLY_LENGTH_MESC, 0)

WHEN SIGN_IN_RECORD IS NOT NULL THEN

CASE FINAL_SIGN_OUT_STATUS WHEN 'SIGN_OUT_ABSENCE' THEN
ABSENCE_LENGTH_MSEC-IFNULL(AAD.ABSENTEEISM_LENGTH_MESC,0)-IFNULL(LATE_LENGTH_MESC,0)
-IFNULL(SERIOUS_LATE_LENGTH_MESC,0) ELSE 0 END

ELSE 0 END

ELSE 0 END)*1.00/3600000 AS 未出勤小时数,

SUM(CASE DAY_TYPE WHEN 'WORK' THEN

CASE WHEN FINAL_SIGN_IN_STATUS='SIGN_IN_ABSENCE' THEN
ABSENCE_LENGTH_DAY-IFNULL(AAD.ABSENTEEISM_LENGTH_DAY,0)-IFNULL(EARLY_OUT_LENGTH_DAY,0)-IFNULL(SERIOUS_EARLY_LENGTH_DAY,0)

WHEN SIGN_IN_RECORD IS NOT NULL THEN

CASE FINAL_SIGN_OUT_STATUS WHEN 'SIGN_OUT_ABSENCE' THEN
ABSENCE_LENGTH_DAY-IFNULL(AAD.ABSENTEEISM_LENGTH_DAY,0)-IFNULL(LATE_LENGTH_DAY,0)-IFNULL(SERIOUS_LATE_LENGTH_DAY,0)
ELSE 0 END

ELSE 0 END

ELSE 0 END) AS 未出勤天数,

COUNT(CASE WHEN FINAL_SIGN_IN_STATUS = 'SIGN_IN_ABSENCE' AND
SIGN_OUT_RECORD IS NOT NULL THEN 1 END) AS 未签到次数,

COUNT(CASE WHEN FINAL_SIGN_OUT_STATUS = 'SIGN_OUT_ABSENCE' AND
SIGN_IN_RECORD IS NOT NULL THEN 1 END) AS 未签退次数,

COUNT(CASE WHEN FINAL_SIGN_IN_STATUS = 'SIGN_IN_ABSENCE' AND
SIGN_OUT_RECORD IS NULL THEN 1 END) AS 未打卡次数

FROM ATTEND_STATUS_DETAIL ASD

LEFT JOIN attend_absence_detail AAD ON ASD.ID = AAD.ID and
ASD.delete_type=AAD.delete_type

where ASD.delete_type = 0

# 历史的班次查询当前最新生效班次

select DERIVED_SHIFT_ID

from attend_shift_version

where orig_shift_id in (select ORIG_SHIFT_ID from attend_shift_version
where DERIVED_SHIFT_ID = 8523744222943839508)

order by VERSION desc

limit 1

8523744222943839508是历史班次id

# 查询某一天的漏签人员

select case

when SIGN_IN_RECORD is null and SIGN_OUT_RECORD is null then '未打卡'

when SIGN_IN_RECORD is null then '未签到' else '未签退' end as
'状态',EMPLOYEE

from attend_status_detail where TENANT_KEY='租户编码' and
attend_date='日期'

and (FINAL_SIGN_IN_STATUS='SIGN_IN_ABSENCE' or
FINAL_SIGN_OUT_STATUS='SIGN_OUT_ABSENCE')

and delete_type=0;

# 根据人员和日期查询当前的考勤规则

select ac.name,ac.id from attend_status_detail asd left join
attend_config ac on asd.ATTEND_CONFIG=ac.id  
and asd.TENANT_KEY=ac.TENANT_KEY and asd.delete_type=ac.delete_type  
where asd.delete_type=0 and asd.TENANT_KEY='tx33zstjid'  
and asd.EMPLOYEE=1084152068840062976 and attend_date = '2025-01-02'

参数说明:

EMPLOYEE是人id

attend_date是日期

TENANT_KEY是租户

# 分组获取不同人员不同请假类型，获取上面请假类型里在休息日和节假日的时长

举例:

工伤假（1083521842651963393）、婚假（7313140674194693275）、产假（7313140674194693277）、丧假按照自然日计算

分组获取不同人员不同请假类型，获取上面请假类型里在休息日和节假日的时长？

参考下面的sql

select employee,VACATION_TYPE,sum(APPEAL_LENGTH_DAY) from
attend_status_detail_link

where APPEAL_TYPE='leave'

and delete_type=0

and VACATION_TYPE in (select id from attend_vacation_setting where
account_type='NATURE')

and VACATION_TYPE=7313140674194693279--假期类型

--工伤假（1083521842651963393）、婚假（7313140674194693275）、产假（7313140674194693277）、丧假（7313140674194693279）

and employee=1066331094279127274

and day_type in ('PLAYDAY','HOLIDAY')

and left(ATTEND_DATE,7)='2025-03'

group by employee,VACATION_TYPE

# 查询班次统计数据

<img
src="media/出勤管理数据表在项目上应用举例/media/image2.png"
style="width:5.75764in;height:1.19931in" />

select *from attend_status_shift_detail where delete_type=0 and
tenant_key='租户' and employee=人员id and attend_date='日期'

# 查询原始打卡记录

SELECT employee,

ADD_TIME

FROM TIMECARD

WHERE TENANT_KEY = \#{tenantKey} AND TYPE in('CHECKIN','CHECKOUT') AND
ADD_TIME BETWEEN \#{beginDate} AND \#{endDate}

AND EMPLOYEE IN (人员id)

AND delete_type = 0

employee--人员id

ADD_TIME--打卡日期时间

# 根据人员id和开始日期、结束日期查询补卡数据

SELECT

L.*,

E.USERNAME AS E_USERNAME,

E.job_num AS job_num,

D.NAME AS D_NAME,

E.STATUS AS E_STATUS

FROM ATTEND_REISSUE_FLOW_DATA L

LEFT JOIN {\$publicdb}.EMPLOYEE E ON E.ID = L.APPLY_EMP_ID

LEFT OUTER JOIN {\$publicdb}.DEPARTMENT D ON D.ID = E.DEPARTMENT

LEFT JOIN ATTEND_NOATTEND AN ON L.TENANT_KEY = AN.TENANT_KEY AND
L.APPLY_EMP_ID = AN.EMPLOYEE AND AN.REMOVE_TIME IS NULL and
AN.delete_type=0

WHERE L.TENANT_KEY = \#{tenantKey} and L.delete_type=0 AND
L.attribute_varchar2='VALID'

AND L.REISSUE_CARD_DATE BETWEEN \#{开始日期} AND \#{结束日期}

AND E.STATUS != 'unavailable'

AND E.ID = \#{人员id}

AND E.TYPE != 'external'

order by L.apply_emp_id,L.reissue_card_date

# 查询attend_status_detail_link和attend_flow_data的关系

select * from attend_status_detail_link a left join attend_flow_data b

on a.FLOW_DATA = b.ID and a.delete_type=b.delete_type

# 查询今日和今日之前的班次的休息时间

select a.REST_RANGE_START, a.REST_RANGE_END, b.*

from attend_status_detail a

left join attend_status_rest_detail b

on a.id = b.id and a.delete_type = b.delete_type

where a.delete_type = 0

and a.EMPLOYEE = 人id

and a.ATTEND_DATE = '日期'

# 查询班次里的休息时间

select a.REST_START,

a.reset_start_cross,

a.REST_END,

a.reset_end_cross,

b.*

from attend_period a

left join attend_period_rest b

on a.id = b.id and a.delete_type = b.delete_type and a.TENANT_KEY =
b.tenant_key

where a.delete_type = 0 and a.id=班次时段id

多休息时段的话

select P.*, apr.*

from ATTEND_PERIOD P

left join attend_period_rest apr on P.id = apr.attend_period_id

# 加班结果日期属性

select * from attend_overtime_detail where DAY_TYPE
in('WORK','PLAYDAY','HOLIDAY') and delete_type=0;

字段说明:  
day_type属性:

WORK:工作日

PLAYDAY:休息日

HOLIDAY:节假日

# 统计公司人员出差天数是哪张数据表，是否是工作日出差

\# day_type--对应的日期属性

select * from attend_status_detail_link where
APPEAL_TYPE='businessTrip' and delete_type=0 and

EMPLOYEE=人员id

and ATTEND_DATE='日期' and TENANT_KEY='租户'

# rpc接口获取日期属性

RemoteAttendService remoteAttendService;

remoteAttendService.queryDayType(List\<Long\> empIds, Date begin, Date
end, String tenantKey)

返回

Map\<Long, Map\<String,AttendDayType\>\>

AttendDayType是个枚举

HOLIDAY("节假日"),NATURE("自然日（不跳过节假日）"),NATURE_SKIP("自然日（跳过节假日）"),
WORK("工作日"), PLAYDAY("休息日");

# 请假查询流程重复的sql

先查询归档的请假数据

SELECT *

FROM ATTEND_FLOW_DATA

WHERE TENANT_KEY = 't6rcdya7tn'

and apply_emp_id in (1169523765260894213)

AND START_TIME \<= '2025-10-20 13:33:00'

AND END_TIME \>= '2025-10-19 09:00:00'

and workflowid in

(1171015819722334208)

AND STATUS = 'VALID'

and delete_type = 0

再查询销假的数据

SELECT *

FROM ATTEND_FLOW_DATA

WHERE TENANT_KEY = 't6rcdya7tn'

AND APPLY_EMP_ID IN (1169523765260894213)

AND LEAVE_FLOW_IDS IN (1195440637161848833)

AND FORM_TYPE = 'respLeave'

AND STATUS = 'VALID'

and delete_type = 0

再查询在途的请假数据

select * from attend_flow_data_before

where tenant_key = 't6rcdya7tn'

and apply_emp_id in (1169523765260894213)

and start_time \<= '2025-10-20 13:33:00' and end_time \>= '2025-10-19
09:00:00'

and workflowid in (1171015819722334208)

and delete_type = 0

# 指定日期人员的班次开始和结束时间

select a.PERIOD_RANGE_START, a.PERIOD_RANGE_END

from attend_status_detail a

where a.delete_type = 0

and a.EMPLOYEE = 人id

and a.ATTEND_DATE = '日期'

# 查询不考勤人员

select * from attend_noattend where delete_type=0

# 根据加班结果查询加班流程

如果加班有加班流程flow_id就是流程的requestid

select b.FLOW_ID,a.* from attend_overtime_detail a left join attend_flow_data b on a.FLOW_DATA = b.id and a.delete_type=b.delete_type and a.TENANT_KEY=b.TENANT_KEY

where a.TENANT_KEY='' and a.delete_type=0

# 怎么用SQL获取申诉次数

\<!-- 查询某人某段时间内的有效申诉次数（申诉中和已成功的） --\>

\<select id="queryEmpAppealNum" resultType="Integer"\>

SELECT COUNT(1) FROM attend_appeal AP

LEFT JOIN attend_deal AD ON AP.attend_deal = AD.id and AD.delete_type=0

WHERE AP.TENANT_KEY = \#{tenantKey}

AND AP.EMP_ID = \#{userId}

AND AP.ATTEND_DAY between \#{begin} and \#{end}

AND AD.TENANT_KEY = \#{tenantKey}

AND (AD.handle_result IN ('UNDO', 'NORMAL') or AD.handle_result is null)

and AP.delete_type=0

\</select\>

# 报表自定义字段sql咨询

select b.* from attend_status_detail a left join
attend_report_col_detail_1 b on a.id = b.id and
a.delete_type=b.delete_type and a.TENANT_KEY=b.tenant_key

where a.TENANT_KEY='' and a.delete_type=0 and a.EMPLOYEE=

动态列查询

select *from attend_report_col_customize

# 班次升级版本了如何查询历史所有版本班次id

1.先查询到根源版本

SELECT orig_shift_id FROM attend_shift_version WHERE TENANT_KEY =
\#{tenantKey} AND DERIVED_SHIFT_ID = \#{derivedShiftId}

2.根据根源版本查询到所有

SELECT DERIVED_SHIFT_ID FROM attend_shift_version WHERE TENANT_KEY =
\#{tenantKey} AND ORIG_SHIFT_ID = \#{origShiftId}

# 班次表里查找加班规则sql

班次表里的加班规则id

select overtime_rule_id from ATTEND_SHIFT where delete_type=0

overtime_rule_id对应的是ATTEND_OVERTIME_RULE的id

# 根据人员和日期查询出差日期时间

select * from attend_status_detail_link where
APPEAL_TYPE='businessTrip' and delete_type=0 and  
EMPLOYEE=人员id and ATTEND_DATE='日期' and TENANT_KEY='租户'

# 表单和考勤结果接口

1.  按工作日：com.weaver.teams.attend.remote.RemoteAttendService#queryWorkingTimeNewEngine  
    2、按外出时长：com.weaver.teams.attend.remote.RemoteAttendService#queryOutsideTime  
    3、按出差时长：com.weaver.teams.attend.remote.RemoteAttendService#queryBusinessTripTime

# 查询缺离岗和返岗卡的数据

select a.employee,a.attend_date ,a.add_time

from attend_missing_timecard a left join eteams.employee E ON a.employee
= E.ID

where a.delete_type=0 and E.STATUS!='unavailable' and
a.abnormal_length_msec \>0 and a.attend_date\>='2026-04-01' and
a.attend_date\<='2026-04-30'

group by a.employee,a.attend_date ,a.add_time

备注：

1.  日期范围自行替换

2.  如果是需要查询单个人可以加上 and a.employee=人员id

3.  SQLserver数据因为是跨库查询需要加dbo.

    select a.employee,a.attend_date ,a.add_time

    from attend_missing_timecard a left join
    eteams.***<u>dbo</u>***.employee E ON a.employee = E.ID

    where a.delete_type=0 and E.STATUS!='unavailable' and
    a.abnormal_length_msec \>0 and a.attend_date\>='2026-04-01' and
    a.attend_date\<='2026-04-30'

    group by a.employee,a.attend_date ,a.add_time

# 根据人员查询他的加班规则

如果已经产生过加班数据的，可以通过下面的sql来查询加班规则

select * from attend_overtime_rule where id in (

select RULE_ID from attend_overtime_rule_detail where id in(

select overtime_id

from attend_overtime_detail

where EMPLOYEE = 5112389563469510320 and DAY_TYPE='WORK'

and delete_type = 0 order by attend_overtime_detail.create_time desc
limit 0,1))

加班规则表里查询关联调休类型:0表示统一设置，1表示流程上选择

select pay_overtype from attend_overtime_rule_detail where id in(

select overtime_id

from attend_overtime_detail

where EMPLOYEE = 5112389563469510320 and DAY_TYPE='WORK'

and delete_type = 0 order by attend_overtime_detail.create_time desc
limit 0,1)

# 签到签退按钮标签

CHECKIN: getLabel('1122', '签到'),

CHECKOUT: getLabel('1124', '签退'),

# 外勤照片

文档模块的表fileobj里的refId对应的打卡表timecard的id

# 年假和调休这些表结构说明

attend_vacation_info_detail_v2--这个调休的

attend_vacation_info_v2这个是非调休之外的

# 加班流程中间表，和E9的kq_flow_split_overtime类似

查询这个表attend_overtime_flow_detail

46. **数据库更新班次设置-打卡提醒**

①备份数据表attend_shift，一定要备份以防万一

②更新

update attend_shift set remind_time_type
='COMMON',sign_in_reminder='ADVANCE',advance_time=10
,sign_out_reminder='DELAYED',delayed_time=1

where name ='班次名称'

备注：

①上面的sql就是将班次的打卡提醒更新为：统一设置、上班提前10分钟，下班后1分钟、系统消息提醒方式

②如果是所有班次都是一样的设置，可以去掉这个where的条件，但是步骤1一定要备份
