需求:1、个人考勤明细表
2、考勤汇总表
筛选条件:可以根据时间和部门进行考勤筛选
前提:1、新建一张日历表关联,可标注节假日
2、将请假、外出、出差日期格式化为每天一条记录及请假天数
开发环境:mysql8.6 帆软10.0
可能会用到的与日期相关的函数:
date(字段)
year(字段)
cast(字段 as time)
date_format(字段,'YYYY-MM-DD %H:i')
注:后面的H大写,用24小时制
第一步 创建日期表
1、如果日期比较短的话可以自己写
2、百度sql已经写好了节假日的sql,建表插入记录,下面列一个21年的简单版
2021年所有日期
insert into day (day,code) values("2021-01-01","1");
insert into day (day,code) values("2021-01-02","1");
insert into day (day,code) values("2021-01-03","1");
insert into day (day,code) values("2021-01-04","0");
insert into day (day,code) values("2021-01-05","0");
insert into day (day,code) values("2021-01-06","0");
insert into day (day,code) values("2021-01-07","0");
insert into day (day,code) values("2021-01-08","0");
insert into day (day,code) values("2021-01-09","3");
insert into day (day,code) values("2021-01-10","3");
insert into day (day,code) values("2021-01-11","0");
insert into day (day,code) values("2021-01-12","0");
insert into day (day,code) values("2021-01-13","0");
insert into day (day,code) values("2021-01-14","0");
insert into day (day,code) values("2021-01-15","0");
insert into day (day,code) values("2021-01-16","3");
insert into day (day,code) values("2021-01-17","3");
insert into day (day,code) values("2021-01-18","0");
insert into day (day,code) values("2021-01-19","0");
insert into day (day,code) values("2021-01-20","0");
insert into day (day,code) values("2021-01-21","0");
insert into day (day,code) values("2021-01-22","0");
insert into day (day,code) values("2021-01-23","3");
insert into day (day,code) values("2021-01-24","3");
insert into day (day,code) values("2021-01-25",“0”);
insert into day (day,code) values(“2021-01-26",“0”);
insert into day (day,code) values(“2021-01-27",“0”);
insert into day (day,code) values(“2021-01-28",“0”);
insert into day (day,code) values(“2021-01-29",“0”);
insert into day (day,code) values(“2021-01-30",“3”);
insert into day (day,code) values(“2021-01-31",“3”);
insert into day (day,code) values(“2021-02-01",“0”);
insert into day (day,code) values(“2021-02-02”,“0”);
insert into day (day,code) values(“2021-02-03”,“0”);
insert into day (day,code) values(“2021-02-04”,“0”);
insert into day (day,code) values(“2021-02-05”,“0”);
insert into day (day,code) values(“2021-02-06”,“3”);
insert into day (day,code) values(“2021-02-07”,“2”);
insert into day (day,code) values(“2021-02-08”,“0”);
insert into day (day,code) values(“2021-02-09”,“0”);
insert into day (day,code) values(“2021-02-10”,“0”);
insert into day (day,code) values(“2021-02-11”,“0”);
insert into day (day,code) values(“2021-02-12”,“0”);
insert into day (day,code) values(“2021-02-13”,“3”);
insert into day (day,code) values(“2021-02-14”,“3”);
insert into day (day,code) values(“2021-02-15”,“0”);
insert into day (day,code) values(“2021-02-16”,“0”);
insert into day (day,code) values(“2021-02-17”,“0”);
insert into day (day,code) values(“2021-02-18”,“0”);
insert into day (day,code) values(“2021-02-19”,“0”);
insert into day (day,code) values(“2021-02-20”,“2”);
insert into day (day,code) values(“2021-02-21”,“3”);
insert into day (day,code) values(“2021-02-22”,“0”);
insert into day (day,code) values(“2021-02-23”,“0”);
insert into day (day,code) values(“2021-02-24”,“0”);
insert into day (day,code) values(“2021-02-25”,“0”);
insert into day (day,code) values(“2021-02-26”,“0”);
insert into day (day,code) values(“2021-02-27”,“3”);
insert into day (day,code) values(“2021-02-28”,“3”);
insert into day (day,code) values(“2021-03-01”,“0”);
insert into day (day,code) values(“2021-03-02”,“0”);
insert into day (day,code) values(“2021-03-03”,“0”);
insert into day (day,code) values(“2021-03-04”,“0”);
insert into day (day,code) values(“2021-03-05”,“0”);
insert into day (day,code) values(“2021-03-06”,“3”);
insert into day (day,code) values(“2021-03-07”,“3”);
insert into day (day,code) values(“2021-03-08”,“0”);
insert into day (day,code) values(“2021-03-09”,“0”);
insert into day (day,code) values(“2021-03-10”,“0”);
insert into day (day,code) values(“2021-03-11”,“0”);
insert into day (day,code) values(“2021-03-12”,“0”);
insert into day (day,code) values(“2021-03-13”,“3”);
insert into day (day,code) values(“2021-03-14”,“3”);
insert into day (day,code) values(“2021-03-15”,“0”);
insert into day (day,code) values(“2021-03-16”,“0”);
insert into day (day,code) values(“2021-03-17”,“0”);
insert into day (day,code) values(“2021-03-18”,“0”);
insert into day (day,code) values(“2021-03-19”,“0”);
insert into day (day,code) values(“2021-03-20”,“3”);
insert into day (day,code) values(“2021-03-21”,“3”);
insert into day (day,code) values(“2021-03-22”,“0”);
insert into day (day,code) values(“2021-03-23”,“0”);
insert into day (day,code) values(“2021-03-24”,“0”);
insert into day (day,code) values(“2021-03-25”,“0”);
insert into day (day,code) values(“2021-03-26”,“0”);
insert into day (day,code) values(“2021-03-27”,“3”);
insert into day (day,code) values(“2021-03-28”,“3”);
insert into day (day,code) values(“2021-03-29”,“0”);
insert into day (day,code) values(“2021-03-30”,“0”);
insert into day (day,code) values(“2021-03-31”,“0”);
insert into day (day,code) values(“2021-04-01”,“0”);
insert into day (day,code) values(“2021-04-02”,“0”);
insert into day (day,code) values(“2021-04-03”,“1”);
insert into day (day,code) values(“2021-04-04”,“1”);
insert into day (day,code) values(“2021-04-05”,“1”);
insert into day (day,code) values(“2021-04-06”,“0”);
insert into day (day,code) values(“2021-04-07”,“0”);
insert into day (day,code) values(“2021-04-08”,“0”);
insert into day (day,code) values(“2021-04-09”,“0”);
insert into day (day,code) values(“2021-04-10”,“3”);
insert into day (day,code) values(“2021-04-11”,“3”);
insert into day (day,code) values(“2021-04-12”,“0”);
insert into day (day,code) values(“2021-04-13”,“0”);
insert into day (day,code) values(“2021-04-14”,“0”);
insert into day (day,code) values(“2021-04-15”,“0”);
insert into day (day,code) values(“2021-04-16”,“0”);
insert into day (day,code) values(“2021-04-17”,“3”);
insert into day (day,code) values(“2021-04-18”,“3”);
insert into day (day,code) values(“2021-04-19”,“0”);
insert into day (day,code) values(“2021-04-20”,“0”);
insert into day (day,code) values(“2021-04-21”,“0”);
insert into day (day,code) values(“2021-04-22”,“0”);
insert into day (day,code) values(“2021-04-23”,“0”);
insert into day (day,code) values(“2021-04-24”,“3”);
insert into day (day,code) values(“2021-04-25”,“2”);
insert into day (day,code) values(“2021-04-26”,“0”);
insert into day (day,code) values(“2021-04-27”,“0”);
insert into day (day,code) values(“2021-04-28”,“0”);
insert into day (day,code) values(“2021-04-29”,“0”);
insert into day (day,code) values(“2021-04-30”,“0”);
insert into day (day,code) values(“2021-05-01”,“1”);
insert into day (day,code) values(“2021-05-02”,“1”);
insert into day (day,code) values(“2021-05-03”,“1”);
insert into day (day,code) values(“2021-05-04”,“1”);
insert into day (day,code) values(“2021-05-05”,“1”);
insert into day (day,code) values(“2021-05-06”,“0”);
insert into day (day,code) values(“2021-05-07”,“0”);
insert into day (day,code) values(“2021-05-08”,“2”);
insert into day (day,code) values(“2021-05-09”,“3”);
insert into day (day,code) values(“2021-05-10”,“0”);
insert into day (day,code) values(“2021-05-11”,“0”);
insert into day (day,code) values(“2021-05-12”,“0”);
insert into day (day,code) values(“2021-05-13”,“0”);
insert into day (day,code) values(“2021-05-14”,“0”);
insert into day (day,code) values(“2021-05-15”,“3”);
insert into day (day,code) values(“2021-05-16”,“3”);
insert into day (day,code) values(“2021-05-17”,“0”);
insert into day (day,code) values(“2021-05-18”,“0”);
insert into day (day,code) values(“2021-05-19”,“0”);
insert into day (day,code) values(“2021-05-20”,“0”);
insert into day (day,code) values(“2021-05-21”,“0”);
insert into day (day,code) values(“2021-05-22”,“3”);
insert into day (day,code) values(“2021-05-23”,“3”);
insert into day (day,code) values(“2021-05-24”,“0”);
insert into day (day,code) values(“2021-05-25”,“0”);
insert into day (day,code) values(“2021-05-26”,“0”);
insert into day (day,code) values(“2021-05-27”,“0”);
insert into day (day,code) values(“2021-05-28”,“0”);
insert into day (day,code) values(“2021-05-29”,“3”);
insert into day (day,code) values(“2021-05-30”,“3”);
insert into day (day,code) values(“2021-05-31”,“0”);
insert into day (day,code) values(“2021-06-01”,“0”);
insert into day (day,code) values(“2021-06-02”,“0”);
insert into day (day,code) values(“2021-06-03”,“0”);
insert into day (day,code) values(“2021-06-04”,“0”);
insert into day (day,code) values(“2021-06-05”,“3”);
insert into day (day,code) values(“2021-06-06”,“3”);
insert into day (day,code) values(“2021-06-07”,“0”);
insert into day (day,code) values(“2021-06-08”,“0”);
insert into day (day,code) values(“2021-06-09”,“0”);
insert into day (day,code) values(“2021-06-10”,“0”);
insert into day (day,code) values(“2021-06-11”,“0”);
insert into day (day,code) values(“2021-06-12”,“1”);
insert into day (day,code) values(“2021-06-13”,“1”);
insert into day (day,code) values(“2021-06-14”,“1”);
insert into day (day,code) values(“2021-06-15”,“0”);
insert into day (day,code) values(“2021-06-16”,“0”);
insert into day (day,code) values(“2021-06-17”,“0”);
insert into day (day,code) values(“2021-06-18”,“0”);
insert into day (day,code) values(“2021-06-19”,“3”);
insert into day (day,code) values(“2021-06-20”,“3”);
insert into day (day,code) values(“2021-06-21”,“0”);
insert into day (day,code) values(“2021-06-22”,“0”);
insert into day (day,code) values(“2021-06-23”,“0”);
insert into day (day,code) values(“2021-06-24”,“0”);
insert into day (day,code) values(“2021-06-25”,“0”);
insert into day (day,code) values(“2021-06-26”,“3”);
insert into day (day,code) values(“2021-06-27”,“3”);
insert into day (day,code) values(“2021-06-28”,“0”);
insert into day (day,code) values(“2021-06-29”,“0”);
insert into day (day,code) values(“2021-06-30”,“0”);
insert into day (day,code) values(“2021-07-01”,“0”);
insert into day (day,code) values(“2021-07-02”,“0”);
insert into day (day,code) values(“2021-07-03”,“3”);
insert into day (day,code) values(“2021-07-04”,“3”);
insert into day (day,code) values(“2021-07-05”,“0”);
insert into day (day,code) values(“2021-07-06”,“0”);
insert into day (day,code) values(“2021-07-07”,“0”);
insert into day (day,code) values(“2021-07-08”,“0”);
insert into day (day,code) values(“2021-07-09”,“0”);
insert into day (day,code) values(“2021-07-10”,“3”);
insert into day (day,code) values(“2021-07-11”,“3”);
insert into day (day,code) values(“2021-07-12”,“0”);
insert into day (day,code) values(“2021-07-13”,“0”);
insert into day (day,code) values(“2021-07-14”,“0”);
insert into day (day,code) values(“2021-07-15”,“0”);
insert into day (day,code) values(“2021-07-16”,“0”);
insert into day (day,code) values(“2021-07-17”,“3”);
insert into day (day,code) values(“2021-07-18”,“3”);
insert into day (day,code) values(“2021-07-19”,“0”);
insert into day (day,code) values(“2021-07-20”,“0”);
insert into day (day,code) values(“2021-07-21”,“0”);
insert into day (day,code) values(“2021-07-22”,“0”);
insert into day (day,code) values(“2021-07-23”,“0”);
insert into day (day,code) values(“2021-07-24”,“3”);
insert into day (day,code) values(“2021-07-25”,“3”);
insert into day (day,code) values(“2021-07-26”,“0”);
insert into day (day,code) values(“2021-07-27”,“0”);
insert into day (day,code) values(“2021-07-28”,“0”);
insert into day (day,code) values(“2021-07-29”,“0”);
insert into day (day,code) values(“2021-07-30”,“0”);
insert into day (day,code) values(“2021-07-31”,“3”);
insert into day (day,code) values(“2021-08-01”,“3”);
insert into day (day,code) values(“2021-08-02”,“0”);
insert into day (day,code) values(“2021-08-03”,“0”);
insert into day (day,code) values(“2021-08-04”,“0”);
insert into day (day,code) values(“2021-08-05”,“0”);
insert into day (day,code) values(“2021-08-06”,“0”);
insert into day (day,code) values(“2021-08-07”,“3”);
insert into day (day,code) values(“2021-08-08”,“3”);
insert into day (day,code) values(“2021-08-09”,“0”);
insert into day (day,code) values(“2021-08-10”,“0”);
insert into day (day,code) values(“2021-08-11”,“0”);
insert into day (day,code) values(“2021-08-12”,“0”);
insert into day (day,code) values(“2021-08-13”,“0”);
insert into day (day,code) values(“2021-08-14”,“3”);
insert into day (day,code) values(“2021-08-15”,“3”);
insert into day (day,code) values(“2021-08-16”,“0”);
insert into day (day,code) values(“2021-08-17”,“0”);
insert into day (day,code) values(“2021-08-18”,“0”);
insert into day (day,code) values(“2021-08-19”,“0”);
insert into day (day,code) values(“2021-08-20”,“0”);
insert into day (day,code) values(“2021-08-21”,“3”);
insert into day (day,code) values(“2021-08-22”,“3”);
insert into day (day,code) values(“2021-08-23”,“0”);
insert into day (day,code) values(“2021-08-24”,“0”);
insert into day (day,code) values(“2021-08-25”,“0”);
insert into day (day,code) values(“2021-08-26”,“0”);
insert into day (day,code) values(“2021-08-27”,“0”);
insert into day (day,code) values(“2021-08-28”,“3”);
insert into day (day,code) values(“2021-08-29”,“3”);
insert into day (day,code) values(“2021-08-30”,“0”);
insert into day (day,code) values(“2021-08-31”,“0”);
insert into day (day,code) values(“2021-09-01”,“0”);
insert into day (day,code) values(“2021-09-02”,“0”);
insert into day (day,code) values(“2021-09-03”,“0”);
insert into day (day,code) values(“2021-09-04”,“3”);
insert into day (day,code) values(“2021-09-05”,“3”);
insert into day (day,code) values(“2021-09-06”,“0”);
insert into day (day,code) values(“2021-09-07”,“0”);
insert into day (day,code) values(“2021-09-08”,“0”);
insert into day (day,code) values(“2021-09-09”,“0”);
insert into day (day,code) values(“2021-09-10”,“0”);
insert into day (day,code) values(“2021-09-11”,“3”);
insert into day (day,code) values(“2021-09-12”,“3”);
insert into day (day,code) values(“2021-09-13”,“0”);
insert into day (day,code) values(“2021-09-14”,“0”);
insert into day (day,code) values(“2021-09-15”,“0”);
insert into day (day,code) values(“2021-09-16”,“0”);
insert into day (day,code) values(“2021-09-17”,“0”);
insert into day (day,code) values(“2021-09-18”,“2”);
insert into day (day,code) values(“2021-09-19”,“1”);
insert into day (day,code) values(“2021-09-20”,“1”);
insert into day (day,code) values(“2021-09-21”,“1”);
insert into day (day,code) values(“2021-09-22”,“0”);
insert into day (day,code) values(“2021-09-23”,“0”);
insert into day (day,code) values(“2021-09-24”,“0”);
insert into day (day,code) values(“2021-09-25”,“3”);
insert into day (day,code) values(“2021-09-26”,“2”);
insert into day (day,code) values(“2021-09-27”,“0”);
insert into day (day,code) values(“2021-09-28”,“0”);
insert into day (day,code) values(“2021-09-29”,“0”);
insert into day (day,code) values(“2021-09-30”,“0”);
insert into day (day,code) values(“2021-10-01”,“1”);
insert into day (day,code) values(“2021-10-02”,“1”);
insert into day (day,code) values(“2021-10-03”,“1”);
insert into day (day,code) values(“2021-10-04”,“1”);
insert into day (day,code) values(“2021-10-05”,“1”);
insert into day (day,code) values(“2021-10-06”,“1”);
insert into day (day,code) values(“2021-10-07”,“1”);
insert into day (day,code) values(“2021-10-08”,“0”);
insert into day (day,code) values(“2021-10-09”,“2”);
insert into day (day,code) values(“2021-10-10”,“3”);
insert into day (day,code) values(“2021-10-11”,“0”);
insert into day (day,code) values(“2021-10-12”,“0”);
insert into day (day,code) values(“2021-10-13”,“0”);
insert into day (day,code) values(“2021-10-14”,“0”);
insert into day (day,code) values(“2021-10-15”,“0”);
insert into day (day,code) values(“2021-10-16”,“3”);
insert into day (day,code) values(“2021-10-17”,“3”);
insert into day (day,code) values(“2021-10-18”,“0”);
insert into day (day,code) values(“2021-10-19”,“0”);
insert into day (day,code) values(“2021-10-20”,“0”);
insert into day (day,code) values(“2021-10-21”,“0”);
insert into day (day,code) values(“2021-10-22”,“0”);
insert into day (day,code) values(“2021-10-23”,“3”);
insert into day (day,code) values(“2021-10-24”,“3”);
insert into day (day,code) values(“2021-10-25”,“0”);
insert into day (day,code) values(“2021-10-26”,“0”);
insert into day (day,code) values(“2021-10-27”,“0”);
insert into day (day,code) values(“2021-10-28”,“0”);
insert into day (day,code) values(“2021-10-29”,“0”);
insert into day (day,code) values(“2021-10-30”,“3”);
insert into day (day,code) values(“2021-10-31”,“3”);
insert into day (day,code) values(“2021-11-01”,“0”);
insert into day (day,code) values(“2021-11-02”,“0”);
insert into day (day,code) values(“2021-11-03”,“0”);
insert into day (day,code) values(“2021-11-04”,“0”);
insert into day (day,code) values(“2021-11-05”,“0”);
insert into day (day,code) values(“2021-11-06”,“3”);
insert into day (day,code) values(“2021-11-07”,“3”);
insert into day (day,code) values(“2021-11-08”,“0”);
insert into day (day,code) values(“2021-11-09”,“0”);
insert into day (day,code) values(“2021-11-10”,“0”);
insert into day (day,code) values(“2021-11-11”,“0”);
insert into day (day,code) values(“2021-11-12”,“0”);
insert into day (day,code) values(“2021-11-13”,“3”);
insert into day (day,code) values(“2021-11-14”,“3”);
insert into day (day,code) values(“2021-11-15”,“0”);
insert into day (day,code) values(“2021-11-16”,“0”);
insert into day (day,code) values(“2021-11-17”,“0”);
insert into day (day,code) values(“2021-11-18”,“0”);
insert into day (day,code) values(“2021-11-19”,“0”);
insert into day (day,code) values(“2021-11-20”,“3”);
insert into day (day,code) values(“2021-11-21”,“3”);
insert into day (day,code) values(“2021-11-22”,“0”);
insert into day (day,code) values(“2021-11-23”,“0”);
insert into day (day,code) values(“2021-11-24”,“0”);
insert into day (day,code) values(“2021-11-25”,“0”);
insert into day (day,code) values(“2021-11-26”,“0”);
insert into day (day,code) values(“2021-11-27”,“3”);
insert into day (day,code) values(“2021-11-28”,“3”);
insert into day (day,code) values(“2021-11-29”,“0”);
insert into day (day,code) values(“2021-11-30”,“0”);
insert into day (day,code) values(“2021-12-01”,“0”);
insert into day (day,code) values(“2021-12-02”,“0”);
insert into day (day,code) values(“2021-12-03”,“0”);
insert into day (day,code) values(“2021-12-04”,“3”);
insert into day (day,code) values(“2021-12-05”,“3”);
insert into day (day,code) values(“2021-12-06”,“0”);
insert into day (day,code) values(“2021-12-07”,“0”);
insert into day (day,code) values(“2021-12-08”,“0”);
insert into day (day,code) values(“2021-12-09”,“0”);
insert into day (day,code) values(“2021-12-10”,“0”);
insert into day (day,code) values(“2021-12-11”,“3”);
insert into day (day,code) values(“2021-12-12”,“3”);
insert into day (day,code) values(“2021-12-13”,“0”);
insert into day (day,code) values(“2021-12-14”,“0”);
insert into day (day,code) values(“2021-12-15”,“0”);
insert into day (day,code) values(“2021-12-16”,“0”);
insert into day (day,code) values(“2021-12-17”,“0”);
insert into day (day,code) values(“2021-12-18”,“3”);
insert into day (day,code) values(“2021-12-19”,“3”);
insert into day (day,code) values(“2021-12-20”,“0”);
insert into day (day,code) values(“2021-12-21”,“0”);
insert into day (day,code) values(“2021-12-22”,“0”);
insert into day (day,code) values(“2021-12-23”,“0”);
insert into day (day,code) values(“2021-12-24”,“0”);
insert into day (day,code) values(“2021-12-25”,“3”);
insert into day (day,code) values(“2021-12-26”,“3”);
insert into day (day,code) values(“2021-12-27”,“0”);
insert into day (day,code) values(“2021-12-28”,“0”);
insert into day (day,code) values(“2021-12-29”,“0”);
insert into day (day,code) values(“2021-12-30”,“0”);
insert into day (day,code) values(“2021-12-31”,“0”);
3、写存储过程,自己创建表格
代码如下:
--创建表结构
CREATE TABLE `m_dim_day` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`DAY_ID` varchar(10) DEFAULT NULL,
`DAY_SHORT_DESC` varchar(10) DEFAULT NULL,
`DAY_LONG_DESC` varchar(50) DEFAULT NULL,
`WEEK_DESC` varchar(20) DEFAULT NULL,
`WEEK_ID` varchar(20) DEFAULT NULL,
`WEEK_LONG_DESC` varchar(50) DEFAULT NULL,
`MONTH_ID` varchar(20) DEFAULT NULL,
`MONTH_LONG_DESC` varchar(50) DEFAULT NULL,
`QUARTER_ID` varchar(20) DEFAULT NULL,
`QUARTER_LONG_DESC` varchar(20) DEFAULT NULL,
`YEAR_ID` varchar(20) DEFAULT NULL,
`YEAR_LONG_DESC` varchar(50) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=731 DEFAULT CHARSET=utf8;
写存储过程,代码如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `f_m_dim_day`(in yr VARCHAR(20))
begin
declare i int;
declare start_date varchar(20);
declare end_date varchar(20);
declare date_count int;
set i=0;
set start_date= concat(yr, '-01-01');
set end_date = concat(yr+1,'-01-01');
DELETE from m_dim_day where year_id = yr;
set date_count = datediff(end_date, start_date);
while i < date_count DO
INSERT into m_dim_day (DAY_ID,DAY_SHORT_DESC,DAY_LONG_DESC,WEEK_DESC,WEEK_ID,WEEK_LONG_DESC,MONTH_ID,MONTH_LONG_DESC,QUARTER_ID,QUARTER_LONG_DESC,YEAR_ID,YEAR_LONG_DESC)
SELECT
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m%d') DAY_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') DAY_SHORT_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') DAY_LONG_DESC,
case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))
when 1 then '星期日'
when 2 then '星期一'
when 3 then '星期二'
when 4 then '星期三'
when 5 then '星期四'
when 6 then '星期五' when 7 then '星期六' end WEEK_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%u') WEEK_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK_LONG_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') MONTH_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH_LONG_DESC,
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s'))) QUARTER_ID,
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER_LONG_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') YEAR_ID,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR_LONG_DESC
from dual;
set i=i+1;
set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');
end while;
end
调用:
call f_m_dim_day('2018')
结果如下:
第二部分 格式化日期
1、取一段开始时间和结束时间,按日期列出所有记录
select * from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select
7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select
7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select
7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select
7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select
7 union select 8 union select 9) t4) v where selected_date between '2020-02-10' and '2020-03-15'
第三部分 考勤明细表sql
代码如下:
select 部门,姓名,日期,签到时间,
签退时间,迟到时间,早退时间,
date_format(cast('2021-08-08 09:11:00'as time),'%H:%i') as sbz,
date_format(cast('2021-08-08 18:01:00'as time),'%H:%i') as xbz,
case when qj_lb <>'' then concat(qj_lb,'(',qj_start,'~',qj_end,')')
else (case when wc_date is not null then concat('外出','(',wc_start,'~',wc_end,')')
else (case when cc_date is not null then concat('出差','(',cc_date,')')
else (case when 公休日 <> '' then 公休日 else ''
end)
end)
end)
end 考勤异常原因说明
from (
SELECT p.*, ods_kq_jiejiari_t.note AS 公休日
FROM
(SELECT n.*, o.出差日期 as cc_date
FROM
(SELECT l.*, m.FD_WAICHUDATE AS wc_date, date_format(m.FD_KAISHITIME,'%Y-%m-%d %H:%i') AS wc_start, date_format(m.FD_JIESHUTIME,'%Y-%m-%d %H:%i') AS wc_end
FROM
(SELECT y.FD_ID, y.部门, y.账户名, y.姓名, y.备注, y.日期,
y.`考勤日期`, y.`签到时间`, y.`签退时间`, y.迟到时间, y.早退时间,
z.FD_QINGJIADATE AS 请假日期, z.FD_QINGJIALEIBIE AS 请假类别,date_format(z.FD_KAISHITIME,'%Y-%m-%d %H:%i') AS qj_start, date_format(z.FD_JIESHUTIME,'%Y-%m-%d %H:%i') AS qj_end,
case z.FD_QINGJIALEIBIE when '1' then '事假'
when '2' then '年假'
when '3' then '病假'
when '5' then '婚假'
when '6' then '产假'
when '7' then '陪产假'
when '8' then '工伤假'
when '9' then '其他'
else '' end qj_lb
FROM
(SELECT b.*, c.考勤日期, c.签到时间, c.签退时间, c.迟到时间, c.早退时间 FROM
(SELECT a.FD_ID, a.`备注`, a.`部门`, a.`账户名`, a.`姓名`, date AS 日期
FROM ods_kq_calendar
CROSS JOIN
(SELECT FD_ID, FD_NOTE AS 备注, FD_LOGIN_NAME AS 账户名, FD_NAME AS 姓名, FD_SUOZAIBUMEN AS 部门 FROM ods_kq_renyuan_t) a
ORDER BY a.`姓名`) b
LEFT JOIN
(SELECT g.SSN, x.考勤日期, x.签到时间, x.签退时间,
CASE
WHEN (x.签到时间 > '09:10:00' AND x.签到时间 <= '10:45:00') THEN (HOUR(x.签到时间) - 9) *60 + (MINUTE(x.签到时间) - 10)
ELSE ''
END 迟到时间,
CASE
WHEN (x.签退时间 < '18:00:00' AND x.签退时间 >= '17:30:00') THEN (60 - MINUTE(x.签退时间))
ELSE ''
END 早退时间
FROM
(SELECT t.USERID, t.考勤日期,
CASE
WHEN ((HOUR(MIN(t.签到时间)) >= 11) OR ((HOUR(MIN(t.签到时间)) = 10) AND (MINUTE(MIN(t.签到时间)) >= 45))) THEN ''
ELSE MIN(t.签到时间)
END 签到时间,
CASE
WHEN ((HOUR(MAX(t.签退时间)) >= 18) OR ((HOUR(MAX(t.签退时间)) = 17) AND (MINUTE(MAX(t.签退时间)) >= 30))) THEN MAX(t.签退时间)
ELSE ''
END 签退时间
FROM
(SELECT USERID, DATE(CHECKTIME) AS 考勤日期, TIME(CHECKTIME) AS 签到时间, TIME(CHECKTIME) AS 签退时间
FROM checkinout WHERE (DATE(CHECKTIME))) t
GROUP BY t.USERID, t.考勤日期) x
LEFT JOIN
(SELECT USERID, SSN FROM userinfo) g
ON x.USERID = g.USERID) c
ON b.账户名 = c.SSN AND b.日期 = c.考勤日期) y
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_QINGJIALEIBIE, FD_QINGJIADATE, FD_KAISHITIME, FD_JIESHUTIME FROM ods_kq_qjsq_t) z
ON y.FD_ID = z.FD_SHENQINGREN AND y.日期 = z.FD_QINGJIADATE) l
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_WAICHUDATE, FD_KAISHITIME, FD_JIESHUTIME FROM ods_kq_wcsq_t) m
ON l.FD_ID = m.FD_SHENQINGREN AND l.日期 = m.FD_WAICHUDATE) n
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_DATE AS 出差日期 FROM ods_kq_ccsq_t) o
ON n.FD_ID = o.FD_SHENQINGREN AND n.日期 = o.出差日期) p
LEFT JOIN ods_kq_jiejiari_t
ON p.日期 = ods_kq_jiejiari_t.jiejiaridate
ORDER BY p.部门, p.账户名, p.日期 ASC
)tmp
where 1=1
and 日期 between '${开始日期}' and '${结束日期}'
${if(len(部门) == 0,"","and 部门 = '" + 部门+ "'")}
sql解析:
select 部门,姓名,日期,签到时间,
签退时间,迟到时间,早退时间,
--上下班标准时间
date_format(cast('2021-08-08 09:11:00'as time),'%H:%i') as sbz,
date_format(cast('2021-08-08 18:01:00'as time),'%H:%i') as xbz,
case when qj_lb <>'' then concat(qj_lb,'(',qj_start,'~',qj_end,')')
else (case when wc_date is not null
then concat('外出','(',wc_start,'~',wc_end,')')
else (case when cc_date is not null then concat('出差','(',cc_date,')')
else (case when 公休日 <> '' then 公休日 else ''
end)
end)
end)
end 考勤异常原因说明
from (
--考勤明细内层综合宽表
SELECT p.*, ods_kq_jiejiari_t.note AS 公休日
FROM
(SELECT n.*, o.出差日期 as cc_date
FROM
(SELECT l.*, m.FD_WAICHUDATE AS wc_date,
date_format(m.FD_KAISHITIME,'%Y-%m-%d %H:%i') AS wc_start, date_format(m.FD_JIESHUTIME,'%Y-%m-%d %H:%i') AS wc_end
FROM
(SELECT y.FD_ID, y.部门, y.账户名, y.姓名, y.备注, y.日期,
y.`考勤日期`, y.`签到时间`, y.`签退时间`, y.迟到时间, y.早退时间,
z.FD_QINGJIADATE AS 请假日期, z.FD_QINGJIALEIBIE AS 请假类别,date_format(z.FD_KAISHITIME,'%Y-%m-%d %H:%i') AS qj_start, date_format(z.FD_JIESHUTIME,'%Y-%m-%d %H:%i') AS qj_end,
case z.FD_QINGJIALEIBIE when '1' then '事假'
when '2' then '年假'
when '3' then '病假'
when '5' then '婚假'
when '6' then '产假'
when '7' then '陪产假'
when '8' then '工伤假'
when '9' then '其他'
else '' end qj_lb
FROM
(SELECT b.*, c.考勤日期, c.签到时间, c.签退时间, c.迟到时间, c.早退时间 FROM
(SELECT a.FD_ID, a.`备注`, a.`部门`, a.`账户名`, a.`姓名`, date AS 日期
FROM ods_kq_calendar
--日期表与人员表关联
CROSS JOIN
(SELECT FD_ID, FD_NOTE AS 备注, FD_LOGIN_NAME AS 账户名,
FD_NAME AS 姓名, FD_SUOZAIBUMEN AS 部门 FROM ods_kq_renyuan_t) a
ORDER BY a.`姓名`) b
LEFT JOIN
(SELECT g.SSN, x.考勤日期, x.签到时间, x.签退时间,
--打卡时间判断
CASE
WHEN (x.签到时间 > '09:10:00' AND x.签到时间 <= '10:45:00')
THEN (HOUR(x.签到时间) - 9) *60 + (MINUTE(x.签到时间) - 10)
ELSE ''
END 迟到时间,
CASE
WHEN (x.签退时间 < '18:00:00' AND x.签退时间 >= '17:30:00')
THEN (60 - MINUTE(x.签退时间))
ELSE ''
END 早退时间
FROM
(SELECT t.USERID, t.考勤日期,
CASE
WHEN ((HOUR(MIN(t.签到时间)) >= 11)
OR ((HOUR(MIN(t.签到时间)) = 10) AND (MINUTE(MIN(t.签到时间)) >= 45))) THEN ''
ELSE MIN(t.签到时间)
END 签到时间,
CASE
WHEN ((HOUR(MAX(t.签退时间)) >= 18)
OR ((HOUR(MAX(t.签退时间)) = 17) AND (MINUTE(MAX(t.签退时间)) >= 30)))
THEN MAX(t.签退时间)
ELSE ''
END 签退时间
FROM
(SELECT USERID, DATE(CHECKTIME) AS 考勤日期, TIME(CHECKTIME) AS 签到时间,
TIME(CHECKTIME) AS 签退时间
FROM checkinout WHERE (DATE(CHECKTIME))) t
GROUP BY t.USERID, t.考勤日期) x
LEFT JOIN
(SELECT USERID, SSN FROM userinfo) g
ON x.USERID = g.USERID) c
ON b.账户名 = c.SSN AND b.日期 = c.考勤日期) y
--请假申请表
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_QINGJIALEIBIE, FD_QINGJIADATE,
FD_KAISHITIME, FD_JIESHUTIME FROM ods_kq_qjsq_t) z
ON y.FD_ID = z.FD_SHENQINGREN AND y.日期 = z.FD_QINGJIADATE) l
--外出申请表
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_WAICHUDATE, FD_KAISHITIME, FD_JIESHUTIME
FROM ods_kq_wcsq_t) m
ON l.FD_ID = m.FD_SHENQINGREN AND l.日期 = m.FD_WAICHUDATE) n
--出差申请表
LEFT JOIN
(SELECT FD_SHENQINGREN, FD_DATE AS 出差日期 FROM ods_kq_ccsq_t) o
ON n.FD_ID = o.FD_SHENQINGREN AND n.日期 = o.出差日期) p
LEFT JOIN ods_kq_jiejiari_t
ON p.日期 = ods_kq_jiejiari_t.jiejiaridate
ORDER BY p.部门, p.账户名, p.日期 ASC
)tmp
where 1=1
and 日期 between '${开始日期}' and '${结束日期}'
${if(len(部门) == 0,"","and 部门 = '" + 部门+ "'")}
总结需要考虑的几点特殊情况:
1、请假但仍旧打卡
2、上午上班时间之后的最晚打卡记录和下班前的最早打卡记录
3、实际出勤天数的计算规则
4、以及事后补请假出差单等等情况