记录一下考勤导出,需要的朋友可以借鉴,很多地方还不是很完善.
以下为导出的结果(都是假数据),后面很长没有截全
sql 表结构就不放了,可以做个参考
SELECT
substr(date,1,7) date,`wname`,`name`,Code,iDCardNumber,teamSysNo,teamName,
count(
case
when status = '正常' then date end
) normal,
count(
case
when status <> '正常'then date end
) notNormal,
day(LAST_DAY(now()))- count(status
) notCord,
count(
case
when startTime = endTime and startTime < '12:00:00' or startTime = '12:00:00' then date end
) upDutyCord,
count(
case
when startTime = endTime and endTime > '12:00:00' or endTime = '12:00:00' then date end
) downDutyCord,
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '01' THEN starttime END ),11,6) AS 'upDuty_1',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '01' THEN endtime END ),11,6) AS 'downDuty_1',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '01' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_1',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '02' THEN starttime END ),11,6) AS 'upDuty_2',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '02' THEN endtime END ),11,6) AS 'downDuty_2',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '02' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_2',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '03' THEN starttime END ),11,6) AS 'upDuty_3',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '03' THEN endtime END),11,6) AS 'downDuty_3',
ROUND(Sum( CASE WHEN Substring( starttime, 9, 2 ) = '03' THEN ( TIMESTAMPDIFF( MINUTE, starttime, endtime ) ) / 60 END ),1) AS 'time_3',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '04' THEN starttime END ),11,6) AS 'upDuty_4',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '04' THEN endtime END ),11,6) AS 'downDuty_4',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '04' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_4',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '05' THEN starttime END ),11,6) AS 'upDuty_5',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '05' THEN endtime END ),11,6) AS 'downDuty_5',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '05' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_5',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '06' THEN starttime END ),11,6) AS 'upDuty_6',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '06' THEN endtime END),11,6) AS 'downDuty_6',
ROUND(Sum( CASE WHEN Substring( starttime, 9, 2 ) = '06' THEN ( TIMESTAMPDIFF( MINUTE, starttime, endtime ) ) / 60 END ),1) AS 'time_6' ,
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '07' THEN starttime END ),11,6) AS 'upDuty_7',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '07' THEN endtime END ),11,6) AS 'downDuty_7',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '07' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_7',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '08' THEN starttime END ),11,6) AS 'upDuty_8',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '08' THEN endtime END ),11,6) AS 'downDuty_8',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '08' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_8',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '09' THEN starttime END ),11,6) AS 'upDuty_9',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '09' THEN endtime END),11,6) AS 'downDuty_9',
ROUND(Sum( CASE WHEN Substring( starttime, 9, 2 ) = '09' THEN ( TIMESTAMPDIFF( MINUTE, starttime, endtime ) ) / 60 END ),1) AS 'time_9',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '10' THEN starttime END ),11,6) AS 'upDuty_10',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '10' THEN endtime END ),11,6) AS 'downDuty_10',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '10' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_10',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '11' THEN starttime END ),11,6) AS 'upDuty_11',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '11' THEN endtime END ),11,6) AS 'downDuty_11',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '11' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_11',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '12' THEN starttime END ),11,6) AS 'upDuty_12',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '12' THEN endtime END),11,6) AS 'downDuty_12',
ROUND(Sum( CASE WHEN Substring( starttime, 9, 2 ) = '12' THEN ( TIMESTAMPDIFF( MINUTE, starttime, endtime ) ) / 60 END ),1) AS 'time_12' ,
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '13' THEN starttime END ),11,6) AS 'upDuty_13',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '13' THEN endtime END ),11,6) AS 'downDuty_13',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '13' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_13',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '14' THEN starttime END ),11,6) AS 'upDuty_14',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '14' THEN endtime END ),11,6) AS 'downDuty_14',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '14' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_14',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '15' THEN starttime END ),11,6) AS 'upDuty_15',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '15' THEN endtime END),11,6) AS 'downDuty_15',
ROUND(Sum( CASE WHEN Substring( starttime, 9, 2 ) = '15' THEN ( TIMESTAMPDIFF( MINUTE, starttime, endtime ) ) / 60 END ),1) AS 'time_15',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '16' THEN starttime END ),11,6) AS 'upDuty_16',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '16' THEN endtime END ),11,6) AS 'downDuty_16',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '16' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_16',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '17' THEN starttime END ),11,6) AS 'upDuty_17',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '17' THEN endtime END ),11,6) AS 'downDuty_17',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '17' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_17',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '18' THEN starttime END ),11,6) AS 'upDuty_18',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '18' THEN endtime END ),11,6) AS 'downDuty_18',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '18' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_18',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '19' THEN starttime END ),11,6) AS 'upDuty_19',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '19' THEN endtime END ),11,6) AS 'downDuty_19',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '19' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_19',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '20' THEN starttime END ),11,6) AS 'upDuty_20',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '20' THEN endtime END ),11,6) AS 'downDuty_20',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '20' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_20',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '21' THEN starttime END ),11,6) AS 'upDuty_21',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '21' THEN endtime END ),11,6) AS 'downDuty_21',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '21' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_21',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '22' THEN starttime END ),11,6) AS 'upDuty_22',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '22' THEN endtime END ),11,6) AS 'downDuty_22',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '22' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_22',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '23' THEN starttime END ),11,6) AS 'upDuty_23',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '23' THEN endtime END ),11,6) AS 'downDuty_23',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '23' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_23',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '24' THEN starttime END ),11,6) AS 'upDuty_24',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '24' THEN endtime END ),11,6) AS 'downDuty_24',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '24' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_24',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '25' THEN starttime END ),11,6) AS 'upDuty_25',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '25' THEN endtime END ),11,6) AS 'downDuty_25',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '25' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_25',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '26' THEN starttime END ),11,6) AS 'upDuty_26',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '26' THEN endtime END ),11,6) AS 'downDuty_26',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '26' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_26',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '27' THEN starttime END ),11,6) AS 'upDuty_27',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '27' THEN endtime END ),11,6) AS 'downDuty_27',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '27' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_27',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '28' THEN starttime END ),11,6) AS 'upDuty_28',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '28' THEN endtime END ),11,6) AS 'downDuty_28',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '28' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_28',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '29' THEN starttime END ),11,6) AS 'upDuty_29',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '29' THEN endtime END ),11,6) AS 'downDuty_29',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '29' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_29',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '30' THEN starttime END ),11,6) AS 'upDuty_30',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '30' THEN endtime END ),11,6) AS 'downDuty_30',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '30' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_30',
substr(Min( CASE WHEN Substring( starttime, 9, 2 ) = '31' THEN starttime END ),11,6) AS 'upDuty_31',
substr(Max( CASE WHEN Substring( starttime, 9, 2 ) = '31' THEN endtime END ),11,6) AS 'downDuty_31',
ROUND(Sum(CASE WHEN Substring( starttime, 9, 2 ) = '31' THEN (ceil(TIMESTAMPDIFF( MINUTE, starttime, endtime ))) / 60 END ),1) AS 'time_31'
FROM
(select
w.wName,
p.NAME,
a.code,
a.IDCardNumber iDCardNumber,
a.TeamSysNo teamSysNo,
a.TeamName teamName,
SUBSTR( a.Date, 1, 10 ) AS date,
min(a.date) AS startTime,
max(a.date) AS endTime,
CASE
WHEN min(
DATE_FORMAT( a.date, '%H:%i' )) = max(
DATE_FORMAT( a.date, '%H:%i' )) THEN
'上班或下班未打卡'
WHEN min(
DATE_FORMAT( a.date, '%H:%i:%S' )) > '8:00' THEN
'迟到'
WHEN max(
DATE_FORMAT( a.date, '%H:%i:%S' )) < '17:30' THEN
'早退' ELSE '正常'
END AS STATUS
FROM
attendance a
JOIN winfo w ON a.IDCardNumber = w.IDCardNumber
JOIN pinfo p ON a.`code` = p.`Code`
WHERE
MONTH ( a.date ) = month(LAST_DAY(now())) and a.isdel = 0
GROUP BY
a.IDCardNumber,
DAY ( a.date )
ORDER BY a.IDCardNumber)att
GROUP BY iDCardNumber;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
