EAS-考勤系统数据迁移方案(保留近3年数据)

需要迁移表单

1、打卡记录 CT_BIL_CardRecord

 -- 检查最早的日期数据:结果为2019-01-01,--无需迁移（历史迁移过）
 select min(FBizDate) from CT_BIL_CardRecord

表名	现有数量（至今）	迁移数量(20190101之前)
CT_BIL_CardRecord	15,899,541	0

数据分布如下：

年份	数量
2019	4182652
2020	3267096
2021	3333207
2022	3722723
2023	1393843
2025	20

执行记录：

 --打卡记录
 select * from CT_BIL_CardRecord where to_char(FBizDate,'yyyy') < '2021'
 
 -- 备份打卡记录2019年数据
 create table CT_BIL_CardRecord_his_2019 as select * from CT_BIL_CardRecord where to_char(FBizDate,'yyyy') = '2019';
 -- 备份打卡记录2020年数据
 create table CT_BIL_CardRecord_his_2020 as select * from CT_BIL_CardRecord where to_char(FBizDate,'yyyy') = '2020';
 
 -- 4182652
 select count(*) from  CT_BIL_CardRecord_his_2019;
 
 --3267096
 select count(*) from  CT_BIL_CardRecord_his_2020;
 
 delete from CT_BIL_CardRecord where to_char(FBizDate,'yyyy') = '2019';
 commit;
 delete from CT_BIL_CardRecord where to_char(FBizDate,'yyyy') = '2020';
 commit;

2、请假单 CT_BIL_HolidayBill CT_BIL_HolidayBillEntry

 -- 检查最早的日期数据：结果为2009-01-01
 select min(FBizDate) from CT_BIL_HolidayBill
 
 -- 统计需要迁移的数据行数：结果为265111
 select count(*) from CT_BIL_HolidayBill where FBizDate < to_date('2019-01-01','yyyy-MM-dd')
 
 -- 检查分录需要迁移的数据行数：562887
 select count(*) from CT_BIL_HolidayBillEntry where fparentid in (
 select fid from CT_BIL_HolidayBill where FBizDate < to_date('2019-01-01','yyyy-MM-dd')
 )
 
 -- 检查调整分录需要迁移的数据行数：2715
 select count(*) from CT_BIL_HolidayBillAdjustEntry where fparentid in (
 select fid from CT_BIL_HolidayBill where FBizDate < to_date('2019-01-01','yyyy-MM-dd')
 )

年份	数量（单据量，不含分录）
2009	1
2011	25
2012	3
2015	23327
2016	54305
2017	80036
2018	107412
2019	98391
2020	75472
2021	82760
2022	89371
2023	28464

执行记录：

-- 请假单记录2012年前数据
 create table CT_BIL_HolidayBill_his_2012pre as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') <= '2012';
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_2012pre as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2012pre);
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_2012pre as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2012pre);
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_2012pre);
 commit;
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_2012pre);
 commit;
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2012pre);
 commit;
 
 --
 
 -- 请假单记录2015年数据
 create table CT_BIL_HolidayBill_his_2015 as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') = '2015';commit;
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_his_2015 as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2015);commit;
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_his_2015 as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2015);commit;
 
 select count(*) qty,'请假单' as billName from CT_BIL_HolidayBill_his_2015
 union all 
 select count(*) qty,'分录' as billName from CT_BIL_HolidayBillEntry_his_2015
 union all 
 select count(*) qty,'调整分录' as billName from CT_BIL_HolidayBillAdjustEntry_his_2015;
 
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_his_2015);commit;
 
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_his_2015);commit;
 
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2015);commit;
 
 
 
 -- 请假单记录2016年前数据
 create table CT_BIL_HolidayBill_his_2016 as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') = '2016';commit;
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_his_2016 as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2016);commit;
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_his_2016 as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2016);commit;
 
 select count(*) qty,'请假单' as billName from CT_BIL_HolidayBill_his_2016
 union all 
 select count(*) qty,'分录' as billName from CT_BIL_HolidayBillEntry_his_2016
 union all 
 select count(*) qty,'调整分录' as billName from CT_BIL_HolidayBillAdjustEntry_his_2016;
 
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_his_2016);commit;
 
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_his_2016);commit;
 
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2016);commit;
 
 
 
 
 -- 请假单记录2017年前数据
 create table CT_BIL_HolidayBill_his_2017 as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') = '2017';commit;
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_his_2017 as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2017);commit;
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_his_2017 as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2017);commit;
 
 --select count(*) qty,'请假单' as billName from CT_BIL_HolidayBill_his_2017
 --union all 
 --select count(*) qty,'分录' as billName from CT_BIL_HolidayBillEntry_his_2017
 --union all 
 --select count(*) qty,'调整分录' as billName from CT_BIL_HolidayBillAdjustEntry_his_2017;
 
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_his_2017);commit;
 
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_his_2017);commit;
 
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2017);commit;
 
 
 
 
 -- 请假单记录2018年前数据
 create table CT_BIL_HolidayBill_his_2018 as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') = '2018';commit;
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_his_2018 as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2018);commit;
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_his_2018 as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2018);commit;
 
 --select count(*) qty,'请假单' as billName from CT_BIL_HolidayBill_his_2018
 --union all 
 --select count(*) qty,'分录' as billName from CT_BIL_HolidayBillEntry_his_2018
 --union all 
 --select count(*) qty,'调整分录' as billName from CT_BIL_HolidayBillAdjustEntry_his_2018;
 
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_his_2018);commit;
 
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_his_2018);commit;
 
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2018);commit;
 
 
 
 
 -- 请假单记录2019年前数据
 create table CT_BIL_HolidayBill_his_2019 as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') = '2019';commit;
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_his_2019 as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2019);commit;
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_his_2019 as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2019);commit;
 
 --select count(*) qty,'请假单' as billName from CT_BIL_HolidayBill_his_2019
 --union all 
 --select count(*) qty,'分录' as billName from CT_BIL_HolidayBillEntry_his_2019
 --union all 
 --select count(*) qty,'调整分录' as billName from CT_BIL_HolidayBillAdjustEntry_his_2019;
 
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_his_2019);commit;
 
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_his_2019);commit;
 
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2019);commit;
 
 
 
 -- 请假单记录2020年前数据
 create table CT_BIL_HolidayBill_his_2020 as select * from CT_BIL_HolidayBill where to_char(FBizDate,'yyyy') = '2020';commit;
 -- 请假单分录
 create table CT_BIL_HolidayBillEntry_his_2020 as select * from CT_BIL_HolidayBillEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2020);commit;
 -- 请假单调整分录
 create table CT_BIL_HolidayBillAdjustEntry_his_2020 as select * from CT_BIL_HolidayBillAdjustEntry where fparentid in(select fid from CT_BIL_HolidayBill_his_2020);commit;
 
 --select count(*) qty,'请假单' as billName from CT_BIL_HolidayBill_his_2020
 --union all 
 --select count(*) qty,'分录' as billName from CT_BIL_HolidayBillEntry_his_2020
 --union all 
 --select count(*) qty,'调整分录' as billName from CT_BIL_HolidayBillAdjustEntry_his_2020;
 
 
 -- 删除
 delete from CT_BIL_HolidayBillAdjustEntry where fid in (select fid from CT_BIL_HolidayBillAdjustEntry_his_2020);commit;
 
 delete from CT_BIL_HolidayBillEntry where fid in (select fid from CT_BIL_HolidayBillEntry_his_2020);commit;
 
 delete from CT_BIL_HolidayBill where fid in (select fid from CT_BIL_HolidayBill_his_2020);commit;

3、批量请假

 -- 27187
 select count(*) from CT_BIL_BatchHoliday where CFEndDate < to_date('2019-01-01','yyyy-MM-dd')
 
 -- 161469
 select count(*) from CT_BIL_BatchHolidayEntry where fparentid in (
 select fid from CT_BIL_BatchHoliday where CFEndDate < to_date('2019-01-01','yyyy-MM-dd')
 )

4、加班单

5、批量考勤

6、考勤明细

 -- 总数：14107882
 select count(*) from CT_BIL_AttenDetail
 -- 20190101之前5793100
 select count(*) from CT_BIL_AttenDetail where FBizDate < to_date('2019-01-01','yyyy-MM-dd')

年份	数量
2014	1
2015	720444
2016	1276186
2017	1647401
2018	2149068
2019	2093295
2020	1830718
2021	1839663
2022	2015174
2023	535932

执行记录

 -- 考勤明细2015年前数据
 create table CT_BIL_AttenDetail_his_2015pre as select * from CT_BIL_AttenDetail where to_char(FBizDate,'yyyy') = '2015';commit;
 
 select count(*) from CT_BIL_AttenDetail_his_2015pre;
 
 -- 删除
 delete from CT_BIL_AttenDetail where fid in (select fid from CT_BIL_AttenDetail_his_2015pre);commit;
 
 -- 考勤明细2016年数据
 create table CT_BIL_AttenDetail_his_2016 as select * from CT_BIL_AttenDetail where to_char(FBizDate,'yyyy') = '2016';commit;
 
 select count(*) from CT_BIL_AttenDetail_his_2016;
 
 -- 删除
 
 delete from CT_BIL_AttenDetail where fid in (select fid from CT_BIL_AttenDetail_his_2016);commit;
 
 
 -- 考勤明细2017年数据
 create table CT_BIL_AttenDetail_his_2017 as select * from CT_BIL_AttenDetail where to_char(FBizDate,'yyyy') = '2017';commit;
 
 select count(*) from CT_BIL_AttenDetail_his_2017;
 
 -- 删除
 
 delete from CT_BIL_AttenDetail where fid in (select fid from CT_BIL_AttenDetail_his_2017);commit;
 
 -- 考勤明细2018年数据
 create table CT_BIL_AttenDetail_his_2018 as select * from CT_BIL_AttenDetail where to_char(FBizDate,'yyyy') = '2018';commit;
 
 select count(*) from CT_BIL_AttenDetail_his_2018;
 
 -- 删除
 
 delete from CT_BIL_AttenDetail where fid in (select fid from CT_BIL_AttenDetail_his_2018);commit;
 
 -- 考勤明细2019年数据
 create table CT_BIL_AttenDetail_his_2019 as select * from CT_BIL_AttenDetail where to_char(FBizDate,'yyyy') = '2019';commit;
 
 select count(*) from CT_BIL_AttenDetail_his_2019;
 
 -- 删除
 
 delete from CT_BIL_AttenDetail where fid in (select fid from CT_BIL_AttenDetail_his_2019);commit;
 
 
 -- 考勤明细2020年数据
 create table CT_BIL_AttenDetail_his_2020 as select * from CT_BIL_AttenDetail where to_char(FBizDate,'yyyy') = '2020';commit;
 
 select count(*) from CT_BIL_AttenDetail_his_2020;
 
 -- 删除
 
 delete from CT_BIL_AttenDetail where fid in (select fid from CT_BIL_AttenDetail_his_2020);commit;

Space shortcuts

Page tree

需要迁移表单

相关的文章