# 排班常用查询sql
## 排班重复
### 查询重复排班的sql
- 不带人名
``` sql
select a.fproposerId,a.fattendDate, count(0)
from t_hr_ats_scheduleShift a
where a.FPROPOSERID = 'm4kAAAAUPOeA733t' -- 员工id
and a.fattendDate >= {ts '2022-01-01'} -- {#开始日期}
and a.fattenDDate <= {ts '2022-01-02'} -- {#结束日期}
group by a.fproposerId,a.fattendDate
having count(0) > 1;
```
- 带人名
``` sql
select a.fproposerId,person.fname_l2,a.fattendDate, count(0)
from t_hr_ats_scheduleShift a
left join t_bd_person person on a.fproposerId = person.fid
where a.FPROPOSERID = 'm4kAAAAUPOeA733t' -- 员工id
and a.fattendDate >= {ts '2022-01-01'} -- {#开始日期}
and a.fattenDDate <= {ts '2022-01-02'} -- {#结束日期}
group by a.fproposerId,person.fname_l2,a.fattendDate
having count(0) > 1;
```
### 删除重复排班
- 删除全部in
``` sql
--先删除重复的分录数据
delete from t_hr_ats_scheduleshiftitem where fgroupid in (select min(fid) from t_hr_Ats_scheduleshift group by FPROPOSERID ,FATTENDDATE having count(1)>1)
--后删除排班主表的数据
delete from t_hr_ats_scheduleshift where fid in (select min(fid) from t_hr_Ats_scheduleshift group by FPROPOSERID ,FATTENDDATE having count(1)>1)
```
- 删除全部 子查询
``` sql
-- 删除分录
delete from t_hr_ats_scheduleshiftItem item
where item.fgroupId < (
select max(innerTable2.fid) from t_hr_ats_scheduleshift innerTable1
join t_hr_ats_scheduleshift innerTable2 on innerTable1.fattenddate=innerTable2.fattenddate and innerTable1.fproposerid=innerTable2.fproposerid
where innerTable1.fid=item.fgroupId
)
;
-- 删除排班主表
delete from t_hr_ats_scheduleshift outerTable
where fid < (
select max(innerTable.fid)
from t_hr_ats_scheduleshift innerTable
where outerTable.fattenddate=innerTable.fattenddate
and outerTable.fproposerid=innerTable.fproposerid
)
;
```
- 删除全部 可添加日期过滤
``` sql
-- 删除分录
delete from t_hr_ats_scheduleshiftItem item
where item.fgroupId < (
select max(innerTable2.fid) from t_hr_ats_scheduleshift innerTable1
join t_hr_ats_scheduleshift innerTable2 on innerTable1.fattenddate=innerTable2.fattenddate and innerTable1.fproposerid=innerTable2.fproposerid
where innerTable1.fid=item.fgroupId
and innerTable1.fattendDate >= {ts '2022-01-01'} -- {#开始日期}
and innerTable1.fattendDate <= {ts '2022-01-02'} -- {#结束日期}
)
;
-- 删除排班主表
delete from t_hr_ats_scheduleshift outerTable
where fid < (
select max(innerTable.fid)
from t_hr_ats_scheduleshift innerTable
where outerTable.fattenddate=innerTable.fattenddate
and outerTable.fproposerid=innerTable.fproposerid
)
and outerTable.fattendDate >= {ts '2022-01-01'} -- {#开始日期}
and outerTable.fattendDate <= {ts '2022-01-02'} -- {#结束日期}
;
```
- 删除全部 保留最后修改时间 (带时间）
``` sql
-- 删除分录
delete from t_hr_ats_scheduleshiftItem item
where item.fgroupId in (
select fid
from t_hr_ats_scheduleshift outerTable
where outerTable.flastUpdateTime < (
select max(innerTable.flastUpdateTime)
from t_hr_ats_scheduleshift innerTable
where outerTable.fattenddate=innerTable.fattenddate
and outerTable.fproposerid=innerTable.fproposerid
)
and outerTable.fattendDate >= {ts '2022-01-01'} -- {#开始日期}
and outerTable.fattendDate <= {ts '2022-01-02'} -- {#结束日期}
)
;
-- 删除排班主表
delete from t_hr_ats_scheduleshift outerTable
where outerTable.flastUpdateTime < (
select max(innerTable.flastUpdateTime)
from t_hr_ats_scheduleshift innerTable
where outerTable.fattenddate=innerTable.fattenddate
and outerTable.fproposerid=innerTable.fproposerid
)
and outerTable.fattendDate >= {ts '2022-01-01'} -- {#开始日期}
and outerTable.fattendDate <= {ts '2022-01-02'} -- {#结束日期}
;
```
## 修复排班8点问题
- 修复排班考勤日期中8点的问题
``` sql
-- 如果排班出现了fattendDate考勤日期为8点的情况：
-- 查询sql如下 前100
select count(*) from t_hr_ats_ScheduleShift where hour(fattendDate) = 8;
select top 100 * from t_hr_ats_ScheduleShift where hour(fattendDate) = 8;
select top 100 fproposerId,fattendDate, * from t_hr_ats_ScheduleShift where hour(fattendDate) = 8 order by fproposerId, fattendDate
;
```
- 修复sql如下
``` sql
update t_hr_ats_scheduleShift
set
fattendDate = to_date(to_char(FATTENDDATE, 'yyyy-MM-dd')), -- 不同数据库不同：oracle to_date(to_char(FATTENDDATE, 'yyyy-MM-dd'), 'yyyy-MM-dd'),其它：to_date(to_char(FATTENDDATE, 'yyyy-MM-dd'))
FLASTUPDATEUSERID = {#'用户Id'}, -- 用户ID，选择特定人员最后啊
FLASTUPDATETIME = now() -- 时间：默认是使用数据库中的now() 函数，表示当前时间。建议使用特定时间：如 {ts '2022-08-29 16:00:00'} 替换
where
hour(fattendDate) = 8 -- 条件
;
update t_hr_ats_scheduleShift
set fattendDate = to_date(to_char(FATTENDDATE, 'yyyy-MM-dd')),
FLASTUPDATEUSERID = '6Sy1pwDURYm09wYgsz7eSIDvfe0=',
FLASTUPDATETIME = now()
where
hour(fattendDate) = 8
;
```
## 排班日志表
### 排班日志表查询
- 根据考勤主表id查询
```sql
select * from T_HR_ATS_SCHEDULESHIFTDATALOG where fbillId = '';
```
- 根据人员和日期查询
``` sql
select *
from T_HR_ATS_SCHEDULESHIFTDATALOG
where FPersonID = '' -- 人员Id
and FAttendDate >= {ts '2022-01-01'} -- 开始日期
and FAttendDate <= {ts '2022-01-31'} -- 结束日期
;
```
### 日志表结构
序号 | 名称 | 别名 | 数据表字段 | 字段类型 | 精度 | 默认值 | 可空 | 描述 | 枚举项 | 关联实体
--- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---
1 | billId | 单据Id | FBillId | VARCHAR | 44 | '' | true | 单据Id | |
2 | person | 姓名 | FPersonID | VARCHAR | 0 | '' | true | 姓名 | | TableName：T_BD_Person；Alias：员工_个人信息；FullName:com.kingdee.eas.basedata.person.app.Person
3 | attendDate | 考勤日期 | FAttendDate | DATETIME | 8 | | true | 考勤日期 | |
4 | dayType | 日期类型 | FDayType | INT | 4 | | true | 日期类型 | 0:工作日;1:休息日;2:法定假日 |
5 | defaultShift | 班次名称 | FDefaultShiftID | VARCHAR | 0 | '' | true | 班次名称 | | TableName：T_HR_ATS_Shift；Alias：班次设置实体；FullName:com.kingdee.eas.hr.ats.app.AtsShift
6 | otCompens | 加班补偿方式 | FOtCompensID | VARCHAR | 0 | '' | true | 加班补偿方式 | | TableName：T_HR_ATS_OverTimeCompens；Alias：加班补偿方式实体；FullName:com.kingdee.eas.hr.ats.app.OverTimeCompens
7 | operType | 操作类型 | FOperType | VARCHAR | 0 | '' | false | | otBill:加班单;leaveBill:请假单;other:其他;atsResultSum:考勤汇总;atsResult:考勤明细;tripBill:出差单;scheduleShift:员工排班;signCard:补签卡;cancelLeaveBill:请假确认单;holidayLimit:假期额度;cancelTripBill:出差确认单 |
8 | operAction | 操作动作 | FOperAction | VARCHAR | 1 | '' | false | | leaveRecalLimit:离职额度重算;other:其他;submit:提交工作流;submitEffect:提交生效;noPass:审批不通过;addNew:新增;delete:删除;approved:审批通过;transferLimitOut:转出;batchAssignment:批量赋值;cancelTransferLimitIn:撤销转入;backgroudTransaction:后台事务生成;cancelLeave:请假确认;submitEffectByCancelLB:请假确认单审批通过;importFile:导入;cancelSalary:撤销转薪资;cancelTransferLimitOut:撤销转出;againstApprove:反审批;sumTranSalary:汇总直接转薪资;schedualRecalculate:排班重算;frontEndGen:前端手工生成;modify:修改;transferLimitIn:转入;abort:撤回;batchModify:排班时间修订;tranSalary:转薪资 |
9 | objectId | 源数据Id | FObjectId | VARCHAR | 0 | '' | false | | |
10 | objectOldDesc | 对象旧值 | FObjectOldDesc | VARCHAR | 0 | '' | false | | |
11 | objectDesc | 对象信息 | FObjectDesc | VARCHAR | 0 | '' | false | | |
12 | creator | 创建者 | FCreatorID | VARCHAR | 0 | '' | true | 创建者 | | TableName：T_PM_User；Alias：用户；FullName:com.kingdee.eas.base.permission.app.User
13 | createTime | 创建时间 | FCreateTime | DATETIME | 8 | | true | 创建时间 | |
14 | lastUpdateUser | 最后修改者 | FLastUpdateUserID | VARCHAR | 0 | '' | true | 最后修改者 | | TableName：T_PM_User；Alias：用户；FullName:com.kingdee.eas.base.permission.app.User
15 | lastUpdateTime | 最后修改时间 | FLastUpdateTime | DATETIME | 8 | | true | 最后修改时间 | |
16 | CU | 控制单元 | FControlUnitID | VARCHAR | 0 | '' | true | 控制单元 | | TableName：T_ORG_CtrlUnit；Alias：管理单元；FullName:com.kingdee.eas.basedata.org.app.CtrlUnit
17 | id | ID | FID | VARCHAR | 44 | '' | false | | |
