##########팀단위##########
SELECT
(SELECT NAME6 FROM APPLEJARA_PERSON BBBB WHERE NAME5=이름) AS "그룹",
(SELECT NAME2 FROM TEST.APPLEJARA_PERSON_DEPT ZZ WHERE NAME3=부서) AS "본부",
부서 AS "팀",
(SELECT NAME1 FROM TEST.APPLEJARA_PERSON_DEPT ZZ WHERE NAME3=부서) AS "순서",
COUNT(부서) AS "인원",
ROUND(AVG(초단위재직),0) "AVG(초단위)",
CONCAT(AVG(초단위재직) DIV 86400,'.',DATE_FORMAT(SEC_TO_TIME(AVG(초단위재직) % 86400), '%H'),':',DATE_FORMAT(SEC_TO_TIME(AVG(초단위재직) % 86400), '%i'),':',DATE_FORMAT(SEC_TO_TIME(AVG(초단위재직) % 86400), '%s')) AS "d.hh:mm:ss"
FROM
(
SELECT
부서, #추출
이름,
직위,
순서,
입사일,
재직일, #1년 365계산
재직일비율 AS "재직일비율",
야근시간 AS "야근시간",
초단위 AS "초단위", #원본
#SEC_TO_TIME(초단위/재직일비율) AS 초단위재직비율, #추출
#TIME_FORMAT(SEC_TO_TIME(초단위/재직일비율),'%H:%i:%s') AS 초단위재직비율, #추출
CONCAT(초단위/재직일비율 DIV 86400,'.',DATE_FORMAT(SEC_TO_TIME(초단위/재직일비율 % 86400), '%H'),':',DATE_FORMAT(SEC_TO_TIME(초단위/재직일비율 % 86400), '%i'),':',DATE_FORMAT(SEC_TO_TIME(초단위/재직일비율 % 86400), '%s'))
AS "초단위재직비율",
초단위/재직일비율 AS "초단위재직"
/*,초단위/재직일비율 DIV 86400
,DATE_FORMAT(SEC_TO_TIME(초단위/재직일비율 % 86400), '%H')
,DATE_FORMAT(SEC_TO_TIME(초단위/재직일비율 % 86400), '%i')
,DATE_FORMAT(SEC_TO_TIME(초단위/재직일비율 % 86400), '%s') */
FROM
(
SELECT
부서,
이름,
직위,
순서,
입사일,
#TIMESTAMPDIFF(DAY, 입사일, '202300401') AS "재직일",
CASE WHEN 입사일 < '20220401' THEN '365'
WHEN 입사일 >= '20220401' THEN TIMESTAMPDIFF(DAY, 입사일, '20230401')
END AS "재직일",
CASE WHEN 입사일 < '20220401' THEN '1.0000'
WHEN 입사일 >= '20220401' THEN TIMESTAMPDIFF(DAY, 입사일, '20230401')/365
END AS "재직일비율",
야근시간,
초단위
#초단위*0.9725,
#SEC_TO_TIME(초단위*0.9725)
FROM
(
SELECT
NAME2 AS "부서",
#NAME3 AS "사번",
NAME4 AS "이름",
NAME5 AS "직위",
(SELECT NAME1 FROM TEST.APPLEJARA_PERSON ZZ WHERE NAME5=XX.NAME4) AS "순서",
(SELECT NAME7 FROM TEST.APPLEJARA_PERSON ZZ WHERE NAME5=XX.NAME4) AS "입사일",
#SUBSTR(NAME6,1,10) AS "근무일",
#SUBSTR(NAME6,1,7) AS "근무일",
/*CASE DAYOFWEEK(NAME6)
WHEN '1' THEN '일'
WHEN '2' THEN '월'
WHEN '3' THEN '화'
WHEN '4' THEN '수'
WHEN '5' THEN '목'
WHEN '6' THEN '금'
WHEN '7' THEN '토'
END AS "요일",*/
#SUBSTR(NAME17,12,5) AS "LOG ON(퇴근)",
#CASE WHEN SUBSTR(NAME17,12,5) > '17:30' THEN '1' END AS "야근",
SEC_TO_TIME(SUM(TIME_TO_SEC((TIMEDIFF(SUBSTR(NAME17,12,8),'17:30:00'))))) AS "야근시간",
SUM(TIME_TO_SEC((TIMEDIFF(SUBSTR(NAME17,12,8),'17:30:00')))) AS "초단위"
#CASE WHEN TIMEDIFF(SUBSTR(NAME17,12,8),'17:30:00') > '00:00:00' THEN TIMEDIFF(SUBSTR(NAME17,12,8),'17:30:00') END AS "야근시간"
#,NAME19 AS "상태"
#,NAME20 AS "업무시간ON"
#,NAME21 AS "자리비움합계"
FROM TEST.APPLEJARA_CHECK_PCOFF XX
WHERE 1=1
AND NAME4 IN (SELECT NAME5 FROM TEST.APPLEJARA_PERSON)
#AND NAME2='마케팅팀'
#AND NAME4='홍길동'
#AND SUBSTR(NAME6,1,7) = '2022-04'
AND TIMEDIFF(SUBSTR(NAME17,12,8),'17:30:00') > '00:00:00'
AND SUBSTR(NAME17,12,5) !='05:59'
GROUP BY NAME3,NAME4,NAME5
ORDER BY CAST(순서 AS SIGNED) ASC
)ZZZZZ
)YYYYYY
)YYYYYYYY
GROUP BY 부서
ORDER BY CAST(순서 AS SIGNED) ASC
