ATS考勤2.18考勤计算（考勤汇总数据拆分表）

--建表脚本--

create table T_ATS_SUMCALC_9001(

FID int not null  comment 'FID'

,FLATETIMES int default null  comment '迟到次数'

,FLATEMINUTESUM int default null  comment '迟到分钟数'

,FEARLYTIMES int default null  comment '早退次数'

,FEARLYMINUTESUM int default null  comment '早退分钟数'

,FABSENTTIMES int default null  comment '旷工次数'

,FABSENTHOURSUM decimal(19,6) default null  comment '旷工时长小时'

,FSUPPLEMENTTIMES int default null  comment '补卡次数'

,FCHECKINTIMES int default null  comment '签卡次数'

,FUNCHECKINTIMES int default null  comment '缺卡次数'

,FLEAVETIMES int default null  comment '请假次数'

,FLEAVEHOURSUM decimal(19,6) default null  comment '请假时长小时'

,FLEAVEDAYSUM decimal(19,6) default null  comment '请假时长天'

,FSICKLEAVETIMES int default null  comment '病假次数'

,FSICKLEAVEDAYSUM decimal(19,6) default null  comment '病假时长天'

,FSICKLEAVEHOURSUM decimal(19,6) default null  comment '病假时长小时'

,FANNUALLEAVETIMES int default null  comment '年假次数'

,FANNUALLEAVEDAYSUM decimal(19,6) default null  comment '年假时长天'

,FANNUALLEAVEHOURSUM decimal(19,6) default null  comment '年假时长小时'

,FTHINGLEAVETIMES int default null  comment '事假次数'

,FTHINGLEAVEDAYSUM decimal(19,6) default null  comment '事假时长天'

,FTHINGLEAVEHOURSUM decimal(19,6) default null  comment '事假时长小时'

,FTRIPTIMES int default null  comment '出差次数'

,FTRIPDAYSUM decimal(19,6) default null  comment '出差时长天'

,FTRIPHOURSUM decimal(19,6) default null  comment '出差时长小时'

,FOT1 decimal(19,6) default null  comment '调休ot1小时数'

,FOT2 decimal(19,6) default null  comment '调休ot2小时数'

,FOT3 decimal(19,6) default null  comment '调休ot3小时数'

,FOT4 decimal(19,6) default null  comment '计加班费ot1小时数'

,FOT5 decimal(19,6) default null  comment '计加班费ot2小时数'

,FOT6 decimal(19,6) default null  comment '计加班费ot3小时数'

,primary key (FID)

) comment = '考勤汇总数据拆分表'

--查询--

select FID as "fid",FLATETIMES as "flatetimes",FLATEMINUTESUM as "flateminutesum",FEARLYTIMES as "fearlytimes",FEARLYMINUTESUM as "fearlyminutesum",FABSENTTIMES as "fabsenttimes",FABSENTHOURSUM as "fabsenthoursum",FSUPPLEMENTTIMES as "fsupplementtimes",FCHECKINTIMES as "fcheckintimes",FUNCHECKINTIMES as "funcheckintimes",FLEAVETIMES as "fleavetimes",FLEAVEHOURSUM as "fleavehoursum",FLEAVEDAYSUM as "fleavedaysum",FSICKLEAVETIMES as "fsickleavetimes",FSICKLEAVEDAYSUM as "fsickleavedaysum",FSICKLEAVEHOURSUM as "fsickleavehoursum",FANNUALLEAVETIMES as "fannualleavetimes",FANNUALLEAVEDAYSUM as "fannualleavedaysum",FANNUALLEAVEHOURSUM as "fannualleavehoursum",FTHINGLEAVETIMES as "fthingleavetimes",FTHINGLEAVEDAYSUM as "fthingleavedaysum",FTHINGLEAVEHOURSUM as "fthingleavehoursum",FTRIPTIMES as "ftriptimes",FTRIPDAYSUM as "ftripdaysum",FTRIPHOURSUM as "ftriphoursum",FOT1 as "fot1",FOT2 as "fot2",FOT3 as "fot3",FOT4 as "fot4",FOT5 as "fot5",FOT6 as "fot6" from T_ATS_SUMCALC_9001

--查询(中文字段)--

select FID as "FID",FLATETIMES as "迟到次数",FLATEMINUTESUM as "迟到分钟数",FEARLYTIMES as "早退次数",FEARLYMINUTESUM as "早退分钟数",FABSENTTIMES as "旷工次数",FABSENTHOURSUM as "旷工时长小时",FSUPPLEMENTTIMES as "补卡次数",FCHECKINTIMES as "签卡次数",FUNCHECKINTIMES as "缺卡次数",FLEAVETIMES as "请假次数",FLEAVEHOURSUM as "请假时长小时",FLEAVEDAYSUM as "请假时长天",FSICKLEAVETIMES as "病假次数",FSICKLEAVEDAYSUM as "病假时长天",FSICKLEAVEHOURSUM as "病假时长小时",FANNUALLEAVETIMES as "年假次数",FANNUALLEAVEDAYSUM as "年假时长天",FANNUALLEAVEHOURSUM as "年假时长小时",FTHINGLEAVETIMES as "事假次数",FTHINGLEAVEDAYSUM as "事假时长天",FTHINGLEAVEHOURSUM as "事假时长小时",FTRIPTIMES as "出差次数",FTRIPDAYSUM as "出差时长天",FTRIPHOURSUM as "出差时长小时",FOT1 as "调休ot1小时数",FOT2 as "调休ot2小时数",FOT3 as "调休ot3小时数",FOT4 as "计加班费ot1小时数",FOT5 as "计加班费ot2小时数",FOT6 as "计加班费ot3小时数" from T_ATS_SUMCALC_9001

--INSERT脚本--

insert into T_ATS_SUMCALC_9001(FID,FLATETIMES,FLATEMINUTESUM,FEARLYTIMES,FEARLYMINUTESUM,FABSENTTIMES,FABSENTHOURSUM,FSUPPLEMENTTIMES,FCHECKINTIMES,FUNCHECKINTIMES,FLEAVETIMES,FLEAVEHOURSUM,FLEAVEDAYSUM,FSICKLEAVETIMES,FSICKLEAVEDAYSUM,FSICKLEAVEHOURSUM,FANNUALLEAVETIMES,FANNUALLEAVEDAYSUM,FANNUALLEAVEHOURSUM,FTHINGLEAVETIMES,FTHINGLEAVEDAYSUM,FTHINGLEAVEHOURSUM,FTRIPTIMES,FTRIPDAYSUM,FTRIPHOURSUM,FOT1,FOT2,FOT3,FOT4,FOT5,FOT6) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ATS_SUMCALC_9001 set FID=?,FLATETIMES=?,FLATEMINUTESUM=?,FEARLYTIMES=?,FEARLYMINUTESUM=?,FABSENTTIMES=?,FABSENTHOURSUM=?,FSUPPLEMENTTIMES=?,FCHECKINTIMES=?,FUNCHECKINTIMES=?,FLEAVETIMES=?,FLEAVEHOURSUM=?,FLEAVEDAYSUM=?,FSICKLEAVETIMES=?,FSICKLEAVEDAYSUM=?,FSICKLEAVEHOURSUM=?,FANNUALLEAVETIMES=?,FANNUALLEAVEDAYSUM=?,FANNUALLEAVEHOURSUM=?,FTHINGLEAVETIMES=?,FTHINGLEAVEDAYSUM=?,FTHINGLEAVEHOURSUM=?,FTRIPTIMES=?,FTRIPDAYSUM=?,FTRIPHOURSUM=?,FOT1=?,FOT2=?,FOT3=?,FOT4=?,FOT5=?,FOT6=? where FID=?

--delete脚本--

delete from T_ATS_SUMCALC_9001 where FID=?

--给字段加备注--

alter table T_ATS_SUMCALC_9001 comment '考勤汇总数据拆分表';

alter table T_ATS_SUMCALC_9001 modify column FID int not null  comment 'FID';

alter table T_ATS_SUMCALC_9001 modify column FLATETIMES int default null  comment '迟到次数';

alter table T_ATS_SUMCALC_9001 modify column FLATEMINUTESUM int default null  comment '迟到分钟数';

alter table T_ATS_SUMCALC_9001 modify column FEARLYTIMES int default null  comment '早退次数';

alter table T_ATS_SUMCALC_9001 modify column FEARLYMINUTESUM int default null  comment '早退分钟数';

alter table T_ATS_SUMCALC_9001 modify column FABSENTTIMES int default null  comment '旷工次数';

alter table T_ATS_SUMCALC_9001 modify column FABSENTHOURSUM decimal(19,6) default null  comment '旷工时长小时';

alter table T_ATS_SUMCALC_9001 modify column FSUPPLEMENTTIMES int default null  comment '补卡次数';

alter table T_ATS_SUMCALC_9001 modify column FCHECKINTIMES int default null  comment '签卡次数';

alter table T_ATS_SUMCALC_9001 modify column FUNCHECKINTIMES int default null  comment '缺卡次数';

alter table T_ATS_SUMCALC_9001 modify column FLEAVETIMES int default null  comment '请假次数';

alter table T_ATS_SUMCALC_9001 modify column FLEAVEHOURSUM decimal(19,6) default null  comment '请假时长小时';

alter table T_ATS_SUMCALC_9001 modify column FLEAVEDAYSUM decimal(19,6) default null  comment '请假时长天';

alter table T_ATS_SUMCALC_9001 modify column FSICKLEAVETIMES int default null  comment '病假次数';

alter table T_ATS_SUMCALC_9001 modify column FSICKLEAVEDAYSUM decimal(19,6) default null  comment '病假时长天';

alter table T_ATS_SUMCALC_9001 modify column FSICKLEAVEHOURSUM decimal(19,6) default null  comment '病假时长小时';

alter table T_ATS_SUMCALC_9001 modify column FANNUALLEAVETIMES int default null  comment '年假次数';

alter table T_ATS_SUMCALC_9001 modify column FANNUALLEAVEDAYSUM decimal(19,6) default null  comment '年假时长天';

alter table T_ATS_SUMCALC_9001 modify column FANNUALLEAVEHOURSUM decimal(19,6) default null  comment '年假时长小时';

alter table T_ATS_SUMCALC_9001 modify column FTHINGLEAVETIMES int default null  comment '事假次数';

alter table T_ATS_SUMCALC_9001 modify column FTHINGLEAVEDAYSUM decimal(19,6) default null  comment '事假时长天';

alter table T_ATS_SUMCALC_9001 modify column FTHINGLEAVEHOURSUM decimal(19,6) default null  comment '事假时长小时';

alter table T_ATS_SUMCALC_9001 modify column FTRIPTIMES int default null  comment '出差次数';

alter table T_ATS_SUMCALC_9001 modify column FTRIPDAYSUM decimal(19,6) default null  comment '出差时长天';

alter table T_ATS_SUMCALC_9001 modify column FTRIPHOURSUM decimal(19,6) default null  comment '出差时长小时';

alter table T_ATS_SUMCALC_9001 modify column FOT1 decimal(19,6) default null  comment '调休ot1小时数';

alter table T_ATS_SUMCALC_9001 modify column FOT2 decimal(19,6) default null  comment '调休ot2小时数';

alter table T_ATS_SUMCALC_9001 modify column FOT3 decimal(19,6) default null  comment '调休ot3小时数';

alter table T_ATS_SUMCALC_9001 modify column FOT4 decimal(19,6) default null  comment '计加班费ot1小时数';

alter table T_ATS_SUMCALC_9001 modify column FOT5 decimal(19,6) default null  comment '计加班费ot2小时数';

alter table T_ATS_SUMCALC_9001 modify column FOT6 decimal(19,6) default null  comment '计加班费ot3小时数';