" SELECT * FROM(SELECT AA08.*, BB08.STOP_ELASED_TIME08 FROM "
" (SELECT AA0202.*, BB0202.STOP_ELASED_TIME0202 FROM "
" (SELECT AA0201.*, BB0201.STOP_ELASED_TIME0201 FROM "
" (SELECT AA12.*, BB12.STOP_ELASED_TIME12 FROM "
" (SELECT AA16.*, BB16.STOP_ELASED_TIME16 FROM "
" (SELECT AA07.*, BB07.STOP_ELASED_TIME07 FROM "
" (SELECT AA15.*, BB15.STOP_ELASED_TIME15 FROM "
" (SELECT AA04.*, BB04.STOP_ELASED_TIME04 FROM "
" (SELECT AA05.*, BB05.STOP_ELASED_TIME05 FROM "
" (SELECT AA06.*, BB06.STOP_ELASED_TIME06 FROM "
" (SELECT AA0302.*, BB0302.STOP_ELASED_TIME0302 FROM "
" (SELECT AA0301.*, BB0301.STOP_ELASED_TIME0301 FROM "
" (SELECT AA0102.*, BB0102.STOP_ELASED_TIME0102 FROM "
" (SELECT AA0101.*, BB0101.STOP_ELASED_TIME0101 FROM "
" (SELECT AA14.*, BB14.STOP_ELASED_TIME14 FROM "
" (SELECT AA.*, BB.STOP_ELASED_TIME FROM(SELECT * FROM(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, STOP_REASON, "
" SUM(ROUND(CASE WHEN STOP_ELASED_TIME = ' ' THEN '0' ELSE STOP_ELASED_TIME END / 60, 1)) STOP_ELASED_TIME FROM TMMCR42A WHERE PROD_DATE "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP, STOP_REASON ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) pivot(sum(STOP_ELASED_TIME) for STOP_REASON "
" in('05' JHJX, '06' JJB, '07' JHHG, '08' SY, '18' JHSJQT, '10' LSJX, '16' JHHGZG, '17' JHHZCG, '01' DL, '02' DD, '03' DMQ, '04' MK, '11' HZ, '19' WBSJQT, '12' DS, '13' DDQ, "
" '14' DZQ, '15' XC)) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', STOP_REASON, SUM(ROUND(CASE WHEN STOP_ELASED_TIME = ' ' THEN '0' ELSE STOP_ELASED_TIME END / 60, 1)) STOP_ELASED_TIME FROM TMMCR42A WHERE PROD_DATE "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE, STOP_REASON ORDER BY UNIT_CODE) "
" pivot(sum(STOP_ELASED_TIME) for STOP_REASON in('05' JHJX, '06' JJB, '07' JHHG, '08' SY, '18' JHSJQT, '10' LSJX, '16' JHHGZG, '17' JHHZCG, '01' DL, '02' DD, '03' DMQ, '04' MK, '11' HZ, "
" '19' WBSJQT, '12' DS, '13' DDQ, '14' DZQ, '15' XC)) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', STOP_REASON, SUM(ROUND(CASE WHEN STOP_ELASED_TIME = ' ' THEN '0' ELSE STOP_ELASED_TIME END / 60, 1)) STOP_ELASED_TIME FROM TMMCR42A WHERE PROD_DATE "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, STOP_REASON ORDER BY UNIT_CODE) pivot(sum(STOP_ELASED_TIME) for STOP_REASON in('05' JHJX, '06' JJB, '07' JHHG, '08' SY, '18' JHSJQT, "
" '10' LSJX, '16' JHHGZG, '17' JHHZCG, '01' DL, '02' DD, '03' DMQ, '04' MK, '11' HZ, '19' WBSJQT, '12' DS, '13' DDQ, '14' DZQ, '15' XC)) "
" UNION ALL "
" SELECT * FROM(SELECT A.UNIT_CODE, A.SZLJ, A.STOP_REASON, (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '3BSZLJ' SZLJ, STOP_REASON, "
" SUM(ROUND(CASE WHEN STOP_ELASED_TIME = ' ' THEN '0' ELSE STOP_ELASED_TIME END / 60, 1)) STOP_ELASED_TIME FROM TMMCR42A WHERE PROD_DATE "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE, STOP_REASON ORDER BY UNIT_CODE) A "
" LEFT JOIN(SELECT UNIT_CODE, 'SZLJ' SZLJ, STOP_REASON, SUM(ROUND(CASE WHEN STOP_ELASED_TIME = ' ' THEN '0' ELSE STOP_ELASED_TIME END / 60, 1)) STOP_ELASED_TIME FROM TMMCR42A WHERE PROD_DATE "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE, STOP_REASON ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) pivot(sum(STOP_ELASED_TIME) for STOP_REASON in('05' JHJX, '06' JJB, '07' JHHG, '08' SY, '18' JHSJQT, "
" '10' LSJX, '16' JHHGZG, '17' JHHZCG, '01' DL, '02' DD, '03' DMQ, '04' MK, '11' HZ, '19' WBSJQT, '12' DS, '13' DDQ, '14' DZQ, '15' XC))) ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) AA "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A "
" LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) BB ON AA.UNIT_CODE = BB.UNIT_CODE AND AA.PROD_SHIFT_GROUP = BB.PROD_SHIFT_GROUP) AA14 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME14 FROM TMMCR42A WHERE STOP_TYPE = '01' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '01' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '01' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '01' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '01' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB14 ON AA14.UNIT_CODE = BB14.UNIT_CODE AND AA14.PROD_SHIFT_GROUP = BB14.PROD_SHIFT_GROUP) AA0101 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME0101 FROM TMMCR42A WHERE STOP_TYPE = '16' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '16' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '16' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '16' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '16' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE)BB0101 ON AA0101.UNIT_CODE = BB0101.UNIT_CODE AND AA0101.PROD_SHIFT_GROUP = BB0101.PROD_SHIFT_GROUP) AA0102 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME0102 FROM TMMCR42A WHERE STOP_TYPE = '17' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '17' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '17' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '17' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '17' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB0102 ON AA0102.UNIT_CODE = BB0102.UNIT_CODE AND AA0102.PROD_SHIFT_GROUP = BB0102.PROD_SHIFT_GROUP) AA0301 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME0301 FROM TMMCR42A WHERE STOP_TYPE = '20' "
" AND SUBSTR(STOP_START_TIME, 0, 8) BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP "
" ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '20' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '20' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '20' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '20' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB0301 ON AA0301.UNIT_CODE = BB0301.UNIT_CODE AND AA0301.PROD_SHIFT_GROUP = BB0301.PROD_SHIFT_GROUP) AA0302 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME0302 FROM TMMCR42A WHERE STOP_TYPE = '21' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '21' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '21' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '21' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '21' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB0302 ON AA0302.UNIT_CODE = BB0302.UNIT_CODE AND AA0302.PROD_SHIFT_GROUP = BB0302.PROD_SHIFT_GROUP) AA06 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME06 FROM TMMCR42A WHERE STOP_TYPE = '04' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '04' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '04' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '04' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '04' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB06 ON AA06.UNIT_CODE = BB06.UNIT_CODE AND AA06.PROD_SHIFT_GROUP = BB06.PROD_SHIFT_GROUP) AA05 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME05 FROM TMMCR42A WHERE STOP_TYPE = '05' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '05' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '05' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '05' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '05' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB05 ON AA05.UNIT_CODE = BB05.UNIT_CODE AND AA05.PROD_SHIFT_GROUP = BB05.PROD_SHIFT_GROUP) AA04 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME04 FROM TMMCR42A WHERE STOP_TYPE = '06' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '06' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '06' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '06' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '06' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB04 ON AA04.UNIT_CODE = BB04.UNIT_CODE AND AA04.PROD_SHIFT_GROUP = BB04.PROD_SHIFT_GROUP) AA15 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME15 FROM TMMCR42A WHERE STOP_TYPE = '07' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '07' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '07' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '07' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '07' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB15 ON AA15.UNIT_CODE = BB15.UNIT_CODE AND AA15.PROD_SHIFT_GROUP = BB15.PROD_SHIFT_GROUP) AA07 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME07 FROM TMMCR42A WHERE STOP_TYPE = '08' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '08' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '08' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '08' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '08' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB07 ON AA07.UNIT_CODE = BB07.UNIT_CODE AND AA07.PROD_SHIFT_GROUP = BB07.PROD_SHIFT_GROUP) AA16 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME16 FROM TMMCR42A WHERE STOP_TYPE = '09' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '09' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '09' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '09' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '09' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB16 ON AA16.UNIT_CODE = BB16.UNIT_CODE AND AA16.PROD_SHIFT_GROUP = BB16.PROD_SHIFT_GROUP) AA12 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME12 FROM TMMCR42A WHERE STOP_TYPE = '10' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '10' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '10' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '10' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '10' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB12 ON AA12.UNIT_CODE = BB12.UNIT_CODE AND AA12.PROD_SHIFT_GROUP = BB12.PROD_SHIFT_GROUP) AA0201 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME0201 FROM TMMCR42A WHERE STOP_TYPE = '18' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '18' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '18' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '18' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '18' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB0201 ON AA0201.UNIT_CODE = BB0201.UNIT_CODE AND AA0201.PROD_SHIFT_GROUP = BB0201.PROD_SHIFT_GROUP) AA0202 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME0202 FROM TMMCR42A WHERE STOP_TYPE = '19' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '19' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '19' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '19' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '19' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB0202 ON AA0202.UNIT_CODE = BB0202.UNIT_CODE AND AA0202.PROD_SHIFT_GROUP = BB0202.PROD_SHIFT_GROUP) AA08 "
" "
" "
" "
" "
" LEFT JOIN(SELECT * FROM(SELECT UNIT_CODE, PROD_SHIFT_GROUP, ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME08 FROM TMMCR42A WHERE STOP_TYPE = '13' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE, PROD_SHIFT_GROUP ORDER BY UNIT_CODE, PROD_SHIFT_GROUP) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '1YLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '13' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(last_day(add_months(to_date(@op_date_from, 'yyyy/mm/dd'), -1)), 'yyyymmdd') AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT * FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '13' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to GROUP BY UNIT_CODE ORDER BY UNIT_CODE) "
" UNION ALL SELECT A.UNIT_CODE, '3BSZLJ', (A.STOP_ELASED_TIME - B.STOP_ELASED_TIME) STOP_ELASED_TIME FROM(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME "
" FROM TMMCR42A WHERE STOP_TYPE = '13' AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN @op_date_from AND @op_date_to "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) A LEFT JOIN(SELECT UNIT_CODE, '2ZLJ', ROUND(SUM(STOP_ELASED_TIME) / 60, 1) STOP_ELASED_TIME FROM TMMCR42A WHERE STOP_TYPE = '13' "
" AND SUBSTR(STOP_START_TIME, 0, 8) "
" BETWEEN to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 7, 'yyyymmdd') "
" AND to_char(to_date(@op_date_from, 'yyyy/mm/dd') + (2 - to_char(to_date(@op_date_from, 'yyyy/mm/dd'), 'd')) - 1, 'yyyymmdd') "
" GROUP BY UNIT_CODE ORDER BY UNIT_CODE) B ON A.UNIT_CODE = B.UNIT_CODE) BB08 ON AA08.UNIT_CODE = BB08.UNIT_CODE AND AA08.PROD_SHIFT_GROUP = BB08.PROD_SHIFT_GROUP) "
"WHERE UNIT_CODE IN ('L11','L41','L43','L51','L61','L62','L63','L71','L72','L81','L82','L83','L84','L85','L86','L87','L91','L93','L94','L95','L96','L97','L98','L9A','L9B','LA1','LA2','LA3','LA4','LA9','LAA') "
;帮我在不影响原逻辑的情况下优化这段SQL,反馈完整的回来