ATS考勤2.6考勤计算（考勤明细数据拆分表）

--建表脚本--

create table T_ATS_DETAILCALC_9001(

FID int not null  comment 'FID'

,FSTARTTIME1 datetime default null  comment '第一段上班时间'

,FENDTIME1 datetime default null  comment '第一段下班时间'

,FSTARTTIME2 datetime default null  comment '第二段上班时间'

,FENDTIME2 datetime default null  comment '第二段下班时间'

,FSTARTTIME3 datetime default null  comment '第三段上班时间'

,FENDTIME3 datetime default null  comment '第三段下班时间'

,FSTARTTIME4 datetime default null  comment '第四段上班时间'

,FENDTIME4 datetime default null  comment '第四段下班时间'

,FSTARTTIME5 datetime default null  comment '第五段上班时间'

,FENDTIME5 datetime default null  comment '第五段下班时间'

,FLATEMINUTE1 int default null  comment '第一段迟到分钟'

,FLATEMINUTE2 int default null  comment '第二段迟到分钟'

,FLATEMINUTE3 int default null  comment '第三段迟到分钟'

,FLATEMINUTE4 int default null  comment '第四段迟到分钟'

,FLATEMINUTE5 int default null  comment '第五段迟到分钟'

,FEARLYMINUTE1 int default null  comment '第一段早退分钟'

,FEARLYMINUTE2 int default null  comment '第二段早退分钟'

,FEARLYMINUTE3 int default null  comment '第三段早退分钟'

,FEARLYMINUTE4 int default null  comment '第四段早退分钟'

,FEARLYMINUTE5 int default null  comment '第五段早退分钟'

,FABSENTMINUTE1 int default null  comment '第一段旷工分钟'

,FABSENTMINUTE2 int default null  comment '第二段旷工分钟'

,FABSENTMINUTE3 int default null  comment '第三段旷工分钟'

,FABSENTMINUTE4 int default null  comment '第四段旷工分钟'

,FABSENTMINUTE5 int default null  comment '第五段旷工分钟'

,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 '签卡次数'

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

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

,FLEAVEDAYSUM 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小时数'

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

,FFIRSTCARD datetime default null  comment '第一卡'

,FLASTCARD datetime default null  comment '最后一卡'

,primary key (FID)

) comment = '考勤明细数据拆分表'

--查询--

select FID as "fid",FSTARTTIME1 as "fstarttime1",FENDTIME1 as "fendtime1",FSTARTTIME2 as "fstarttime2",FENDTIME2 as "fendtime2",FSTARTTIME3 as "fstarttime3",FENDTIME3 as "fendtime3",FSTARTTIME4 as "fstarttime4",FENDTIME4 as "fendtime4",FSTARTTIME5 as "fstarttime5",FENDTIME5 as "fendtime5",FLATEMINUTE1 as "flateminute1",FLATEMINUTE2 as "flateminute2",FLATEMINUTE3 as "flateminute3",FLATEMINUTE4 as "flateminute4",FLATEMINUTE5 as "flateminute5",FEARLYMINUTE1 as "fearlyminute1",FEARLYMINUTE2 as "fearlyminute2",FEARLYMINUTE3 as "fearlyminute3",FEARLYMINUTE4 as "fearlyminute4",FEARLYMINUTE5 as "fearlyminute5",FABSENTMINUTE1 as "fabsentminute1",FABSENTMINUTE2 as "fabsentminute2",FABSENTMINUTE3 as "fabsentminute3",FABSENTMINUTE4 as "fabsentminute4",FABSENTMINUTE5 as "fabsentminute5",FLATETIMES as "flatetimes",FLATEMINUTESUM as "flateminutesum",FEARLYTIMES as "fearlytimes",FEARLYMINUTESUM as "fearlyminutesum",FABSENTTIMES as "fabsenttimes",FABSENTHOURSUM as "fabsenthoursum",FSUPPLEMENTTIMES as "fsupplementtimes",FCHECKINTIMES as "fcheckintimes",FLEAVETIMES as "fleavetimes",FLEAVEHOURSUM as "fleavehoursum",FLEAVEDAYSUM as "fleavedaysum",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",FUNCHECKINTIMES as "funcheckintimes",FFIRSTCARD as "ffirstcard",FLASTCARD as "flastcard" from T_ATS_DETAILCALC_9001

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

select FID as "FID",FSTARTTIME1 as "第一段上班时间",FENDTIME1 as "第一段下班时间",FSTARTTIME2 as "第二段上班时间",FENDTIME2 as "第二段下班时间",FSTARTTIME3 as "第三段上班时间",FENDTIME3 as "第三段下班时间",FSTARTTIME4 as "第四段上班时间",FENDTIME4 as "第四段下班时间",FSTARTTIME5 as "第五段上班时间",FENDTIME5 as "第五段下班时间",FLATEMINUTE1 as "第一段迟到分钟",FLATEMINUTE2 as "第二段迟到分钟",FLATEMINUTE3 as "第三段迟到分钟",FLATEMINUTE4 as "第四段迟到分钟",FLATEMINUTE5 as "第五段迟到分钟",FEARLYMINUTE1 as "第一段早退分钟",FEARLYMINUTE2 as "第二段早退分钟",FEARLYMINUTE3 as "第三段早退分钟",FEARLYMINUTE4 as "第四段早退分钟",FEARLYMINUTE5 as "第五段早退分钟",FABSENTMINUTE1 as "第一段旷工分钟",FABSENTMINUTE2 as "第二段旷工分钟",FABSENTMINUTE3 as "第三段旷工分钟",FABSENTMINUTE4 as "第四段旷工分钟",FABSENTMINUTE5 as "第五段旷工分钟",FLATETIMES as "迟到次数",FLATEMINUTESUM as "迟到分钟",FEARLYTIMES as "早退次数",FEARLYMINUTESUM as "早退分钟",FABSENTTIMES as "旷工次数",FABSENTHOURSUM as "旷工小时数",FSUPPLEMENTTIMES as "补卡次数",FCHECKINTIMES as "签卡次数",FLEAVETIMES as "请假次数",FLEAVEHOURSUM as "请假时长小时"