需要迁移表单
-- 检查最早的日期数据:结果为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;
0 Comments