L83

Kids Gulp 7 Trillion Calories Per Year

Kids from the ages of 2 to 19, consume about seven trillion calories in sugar-sweetened beverages per year, according to Steve Gortmaker of the Harvard School of Public Health.

He spoke at the Obesity Society Annual Scientific Meeting in San Antonio on September 23rd.

Seven trillion is a lot of calories in sugar-sweetened beverages.

At, for example, 50 cents per can, it's about $24 billion a year.

All those dollars and sugary calories are stoking the childhood obesity epidemic.

Currently, in the U.S., about 17 percent of children and adolescents are obese—that's more than 12.5 million kids.

And new research in the British Medical Journal suggests that obese children will have much higher risk factors for cardiovascular disease as adults.

Even as kids, their hearts are changing shape to look like those of adults at risk for heart disease.

But the good news is that simply cutting out about 64 calories a day from kids' diets could start to level out the steep rise in childhood obesity.

That's equivalent to less than half a can of most non-diet sodas.

转载于:https://www.cnblogs.com/huangbaobaoi/p/8742714.html

" 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,反馈完整的回来
09-05
再结合以下代码,解释是如何在推理时去掉batchnormalization的:# # For licensing see accompanying LICENSE file. # Copyright (C) 2024 Apple Inc. All Rights Reserved. # from typing import Union, Tuple import copy import torch import torch.nn as nn import torch.nn.functional as F __all__ = ["MobileOneBlock", "reparameterize_model"] class SEBlock(nn.Module): """Squeeze and Excite module. Pytorch implementation of `Squeeze-and-Excitation Networks` - https://arxiv.org/pdf/1709.01507.pdf """ def __init__(self, in_channels: int, rd_ratio: float = 0.0625) -> None: """Construct a Squeeze and Excite Module. Args: in_channels: Number of input channels. rd_ratio: Input channel reduction ratio. """ super(SEBlock, self).__init__() self.reduce = nn.Conv2d( in_channels=in_channels, out_channels=int(in_channels * rd_ratio), kernel_size=1, stride=1, bias=True, ) self.expand = nn.Conv2d( in_channels=int(in_channels * rd_ratio), out_channels=in_channels, kernel_size=1, stride=1, bias=True, ) def forward(self, inputs: torch.Tensor) -> torch.Tensor: """Apply forward pass.""" b, c, h, w = inputs.size() x = F.avg_pool2d(inputs, kernel_size=[h, w]) x = self.reduce(x) x = F.relu(x) x = self.expand(x) x = torch.sigmoid(x) x = x.view(-1, c, 1, 1) return inputs * x class MobileOneBlock(nn.Module): """MobileOne building block. This block has a multi-branched architecture at train-time and plain-CNN style architecture at inference time For more details, please refer to our paper: `An Improved One millisecond Mobile Backbone` - https://arxiv.org/pdf/2206.04040.pdf """ def __init__( self, in_channels: int, out_channels: int, kernel_size: int, stride: int = 1, padding: int = 0, dilation: int = 1, groups: int = 1, inference_mode: bool = False, use_se: bool = False, use_act: bool = True, use_scale_branch: bool = True, num_conv_branches: int = 1, activation: nn.Module = nn.GELU(), ) -> None: """Construct a MobileOneBlock module. Args: in_channels: Number of channels in the input. out_channels: Number of channels produced by the block. kernel_size: Size of the convolution kernel. stride: Stride size. padding: Zero-padding size. dilation: Kernel dilation factor. groups: Group number. inference_mode: If True, instantiates model in inference mode. use_se: Whether to use SE-ReLU activations. use_act: Whether to use activation. Default: ``True`` use_scale_branch: Whether to use scale branch. Default: ``True`` num_conv_branches: Number of linear conv branches. """ super(MobileOneBlock, self).__init__() self.inference_mode = inference_mode self.groups = groups self.stride = stride self.padding = padding self.dilation = dilation self.kernel_size = kernel_size self.in_channels = in_channels self.out_channels = out_channels self.num_conv_branches = num_conv_branches # Check if SE-ReLU is requested if use_se: self.se = SEBlock(out_channels) else: self.se = nn.Identity() if use_act: self.activation = activation else: self.activation = nn.Identity() if inference_mode: self.reparam_conv = nn.Conv2d( in_channels=in_channels, out_channels=out_channels, kernel_size=kernel_size, stride=stride, padding=padding, dilation=dilation, groups=groups, bias=True, ) else: # Re-parameterizable skip connection self.rbr_skip = ( nn.BatchNorm2d(num_features=in_channels) if out_channels == in_channels and stride == 1 else None ) # Re-parameterizable conv branches if num_conv_branches > 0: rbr_conv = list() for _ in range(self.num_conv_branches): rbr_conv.append( self._conv_bn(kernel_size=kernel_size, padding=padding) ) self.rbr_conv = nn.ModuleList(rbr_conv) else: self.rbr_conv = None # Re-parameterizable scale branch self.rbr_scale = None if not isinstance(kernel_size, int): kernel_size = kernel_size[0] if (kernel_size > 1) and use_scale_branch: self.rbr_scale = self._conv_bn(kernel_size=1, padding=0) def forward(self, x: torch.Tensor) -> torch.Tensor: """Apply forward pass.""" # Inference mode forward pass. if self.inference_mode: return self.activation(self.se(self.reparam_conv(x))) # Multi-branched train-time forward pass. # Skip branch output identity_out = 0 if self.rbr_skip is not None: identity_out = self.rbr_skip(x) # Scale branch output scale_out = 0 if self.rbr_scale is not None: scale_out = self.rbr_scale(x) # Other branches out = scale_out + identity_out if self.rbr_conv is not None: for ix in range(self.num_conv_branches): out += self.rbr_conv[ix](x) return self.activation(self.se(out)) def reparameterize(self): """Following works like `RepVGG: Making VGG-style ConvNets Great Again` - https://arxiv.org/pdf/2101.03697.pdf. We re-parameterize multi-branched architecture used at training time to obtain a plain CNN-like structure for inference. """ if self.inference_mode: return kernel, bias = self._get_kernel_bias() self.reparam_conv = nn.Conv2d( in_channels=self.in_channels, out_channels=self.out_channels, kernel_size=self.kernel_size, stride=self.stride, padding=self.padding, dilation=self.dilation, groups=self.groups, bias=True, ) self.reparam_conv.weight.data = kernel self.reparam_conv.bias.data = bias # Delete un-used branches for para in self.parameters(): para.detach_() self.__delattr__("rbr_conv") self.__delattr__("rbr_scale") if hasattr(self, "rbr_skip"): self.__delattr__("rbr_skip") self.inference_mode = True def _get_kernel_bias(self) -> Tuple[torch.Tensor, torch.Tensor]: """Method to obtain re-parameterized kernel and bias. Reference: https://github.com/DingXiaoH/RepVGG/blob/main/repvgg.py#L83 Returns: Tuple of (kernel, bias) after fusing branches. """ # get weights and bias of scale branch kernel_scale = 0 bias_scale = 0 if self.rbr_scale is not None: kernel_scale, bias_scale = self._fuse_bn_tensor(self.rbr_scale) # Pad scale branch kernel to match conv branch kernel size. pad = self.kernel_size // 2 kernel_scale = torch.nn.functional.pad(kernel_scale, [pad, pad, pad, pad]) # get weights and bias of skip branch kernel_identity = 0 bias_identity = 0 if self.rbr_skip is not None: kernel_identity, bias_identity = self._fuse_bn_tensor(self.rbr_skip) # get weights and bias of conv branches kernel_conv = 0 bias_conv = 0 if self.rbr_conv is not None: for ix in range(self.num_conv_branches): _kernel, _bias = self._fuse_bn_tensor(self.rbr_conv[ix]) kernel_conv += _kernel bias_conv += _bias kernel_final = kernel_conv + kernel_scale + kernel_identity bias_final = bias_conv + bias_scale + bias_identity return kernel_final, bias_final def _fuse_bn_tensor( self, branch: Union[nn.Sequential, nn.BatchNorm2d] ) -> Tuple[torch.Tensor, torch.Tensor]: """Method to fuse batchnorm layer with preceeding conv layer. Reference: https://github.com/DingXiaoH/RepVGG/blob/main/repvgg.py#L95 Args: branch: Sequence of ops to be fused. Returns: Tuple of (kernel, bias) after fusing batchnorm. """ if isinstance(branch, nn.Sequential): kernel = branch.conv.weight running_mean = branch.bn.running_mean running_var = branch.bn.running_var gamma = branch.bn.weight beta = branch.bn.bias eps = branch.bn.eps else: assert isinstance(branch, nn.BatchNorm2d) if not hasattr(self, "id_tensor"): input_dim = self.in_channels // self.groups kernel_size = self.kernel_size if isinstance(self.kernel_size, int): kernel_size = (self.kernel_size, self.kernel_size) kernel_value = torch.zeros( (self.in_channels, input_dim, kernel_size[0], kernel_size[1]), dtype=branch.weight.dtype, device=branch.weight.device, ) for i in range(self.in_channels): kernel_value[ i, i % input_dim, kernel_size[0] // 2, kernel_size[1] // 2 ] = 1 self.id_tensor = kernel_value kernel = self.id_tensor running_mean = branch.running_mean running_var = branch.running_var gamma = branch.weight beta = branch.bias eps = branch.eps std = (running_var + eps).sqrt() t = (gamma / std).reshape(-1, 1, 1, 1) return kernel * t, beta - running_mean * gamma / std def _conv_bn(self, kernel_size: int, padding: int) -> nn.Sequential: """Helper method to construct conv-batchnorm layers. Args: kernel_size: Size of the convolution kernel. padding: Zero-padding size. Returns: Conv-BN module. """ mod_list = nn.Sequential() mod_list.add_module( "conv", nn.Conv2d( in_channels=self.in_channels, out_channels=self.out_channels, kernel_size=kernel_size, stride=self.stride, padding=padding, groups=self.groups, bias=False, ), ) mod_list.add_module("bn", nn.BatchNorm2d(num_features=self.out_channels)) return mod_list def reparameterize_model(model: torch.nn.Module) -> nn.Module: """Method returns a model where a multi-branched structure used in training is re-parameterized into a single branch for inference. Args: model: MobileOne model in train mode. Returns: MobileOne model in inference mode. """ # Avoid editing original graph model = copy.deepcopy(model) for module in model.modules(): if hasattr(module, "reparameterize"): module.reparameterize() return model
08-30
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值