{"id":9,"date":"2014-10-15T01:19:51","date_gmt":"2014-10-15T01:19:51","guid":{"rendered":"http:\/\/www.hadooh.com\/?p=9"},"modified":"2015-12-29T01:46:15","modified_gmt":"2015-12-29T01:46:15","slug":"%ed%9c%b4%ec%a7%80%ec%8b%9c%ea%b0%84%ec%9d%98-%ec%b0%a8%eb%a5%bc-%ea%b5%ac%ed%95%98%ea%b3%a0-%ea%b7%bc%ec%a1%b0%ec%8b%9c%ea%b0%84%eb%b3%84%eb%a1%9c-low%eb%a5%bc-%ec%83%9d%ec%84%b1%ed%95%98%ea%b8%b0","status":"publish","type":"post","link":"https:\/\/www.hadooh.com\/?p=9","title":{"rendered":"\ud734\uc9c0\uc2dc\uac04\uc758 \ucc28\ub97c \uad6c\ud558\uace0 \uadfc\uc870\uc2dc\uac04\ubcc4\ub85c LOW\ub97c \uc0dd\uc131\ud558\uae30"},"content":{"rendered":"

WITH PAU AS (
\nSELECT
\n PAU_STR_DTM
\n ,PAU_END_DTM
\n ,ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3) AS DIFF \/\/\ud734\uc9c0\uc2dc\uac04 \ucc28\uc774
\n ,CEIL(ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3)\/(7*60)) AS LINE \/\/\uc9c0\ub098\uac04 \uadfc\uc870\uac2f\uc218 \uad6c\ud558\uae30
\nFROM \ud734\uc9c0\uc2e4\uc801
\n)
\nSELECT *
\nFROM PAU
\nCONNECT BY LEVEL < LINE\n\n\n\ub354 \uc88b\uc740 \ucffc\ub9ac\ub97c \uad6c\ud588\ub2e4. \ubd84\uc11d\ud574\uc11c \ub0b4\uaec4\ub85c \ub9cc\ub4e4\uc790!!\n\nWITH TV_PAU_RSL AS\n(\n SELECT \/*+ MATERIALIZE *\/\n PAU.FAC_TP\n , PAU.PROC_TP\n , PAU.EQU_CD\n , TO_CHAR(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') PAU_STR_DTM\n , TO_CHAR(PAU.PAU_END_DTM, 'YYYYMMDDHH24MISS') PAU_END_DTM\n , TRUNC(PAU.PAU_STR_DTM, 'MI') AS PAU_STR_DTM2\n , TRUNC(PAU.PAU_END_DTM, 'MI') AS PAU_END_DTM2\n , TRUNC(PAU.PAU_END_DTM, 'MI') - TRUNC(PAU.PAU_STR_DTM, 'MI') AS CNT \n FROM\n TB_M30_PAU_RSL PAU\n WHERE\n PAU.FAC_TP = :FAC_TP\n AND PAU.PROC_TP = :PROC_TP\n AND TO_CHAR(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') = :PAU_STR_DTM\n AND PAU.PAU_END_DTM IS NOT NULL\n)\n, TV_DT_PAU_RSL_BASE AS\n(\n SELECT\n DISTINCT \n CASE WHEN ('060000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') \n AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM\n , TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '060000', 'YYYYMMDDHH24MISS'))\n ELSE CASE WHEN ('140000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') \n AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM\n , TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '140000', 'YYYYMMDDHH24MISS'))\n ELSE CASE WHEN ('220000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') \n AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM\n , TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '220000', 'YYYYMMDDHH24MISS'))\n ELSE PAU_S_DTM END END\n END AS PAU_S_DTM\n , CASE WHEN '060000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') \n AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '060000', 'YYYYMMDDHH24MISS')\n , PAU.PAU_E_DTM)\n ELSE CASE WHEN '140000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') \n AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '140000', 'YYYYMMDDHH24MISS')\n , PAU.PAU_E_DTM)\n ELSE CASE WHEN '220000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') \n AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '220000', 'YYYYMMDDHH24MISS')\n , PAU.PAU_E_DTM)\n ELSE PAU_E_DTM END END\n END AS PAU_E_DTM\n FROM (\n SELECT\n DECODE(CONNECT_BY_ISLEAF, 0, PAU.PAU_STR_DTM2 + ((LEVEL-1)\/24)\n , PAU.PAU_END_DTM2) AS PAU_S_DTM\n , LEAD(DECODE(CONNECT_BY_ISLEAF, 0, PAU.PAU_STR_DTM2 + ((LEVEL-1)\/24)\n , PAU.PAU_END_DTM2), 1, PAU.PAU_END_DTM2) OVER(ORDER BY LEVEL) AS PAU_E_DTM\n FROM\n TV_PAU_RSL PAU\n CONNECT BY LEVEL <= (PAU_END_DTM2 - PAU_STR_DTM2) * 24 + 2\n ) PAU\n , ( SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 2) DUMMY\n ORDER BY PAU_S_DTM\n)\n, TV_DT_PAU_RSL AS\n(\n SELECT\n CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '000000' \n AND '055959' THEN TO_CHAR(PAU_S_DTM-1, 'YYYYMMDD')\n ELSE TO_CHAR(PAU_S_DTM, 'YYYYMMDD')\n END AS PAU_PSV_DT,\n CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '060000' \n AND '135959' THEN 1\n ELSE CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '140000' \n AND '215959' THEN 2\n ELSE 3 END\n END AS WK_SHF\n , ROUND(SUM((PAU_E_DTM - PAU_S_DTM) * 24 * 60),1) AS PAU_HRP\n FROM \n TV_DT_PAU_RSL_BASE\n GROUP BY\n CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '000000' \n AND '055959' THEN TO_CHAR(PAU_S_DTM-1, 'YYYYMMDD')\n ELSE TO_CHAR(PAU_S_DTM, 'YYYYMMDD')\n END,\n CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '060000' \n AND '135959' THEN 1\n ELSE CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '140000' \n AND '215959' THEN 2\n ELSE 3 END\n END\n)\nSELECT\n PAU.FAC_TP\n , PAU.PROC_TP\n , PAU.EQU_CD\n , DT_PAU.PAU_PSV_DT\n , (\n SELECT\n NVL(MAX(X.SEQ), 0)\n FROM\n TB_M30_PAU_RSL_DT X\n WHERE\n X.FAC_TP = PAU.FAC_TP\n AND X.PROC_TP = PAU.PROC_TP\n AND X.PAU_PSV_DT = DT_PAU.PAU_PSV_DT\n ) + (ROW_NUMBER() OVER (ORDER BY 1 DESC)) AS SEQ\n , :ObjectType\n , :ObjectId\n , :ProgramId\n , :Timestamp\n , :ObjectType\n , :ObjectId\n , :ProgramId\n , :Timestamp\n , DT_PAU.PAU_HRP\n , TO_DATE(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') AS PAU_STR_DTM\n , DT_PAU.WK_SHF\n , (SELECT WK_CRW FROM TB_M20_WK_SHF_CRW CRW WHERE CRW.FAC_TP = PAU.FAC_TP AND CRW.WK_PSV_DT = DT_PAU.PAU_PSV_DT AND CRW.WK_SHF = DT_PAU.WK_SHF) AS WK_SHF\nFROM\n TV_PAU_RSL PAU\n , TV_DT_PAU_RSL DT_PAU\nWHERE\n DT_PAU.PAU_HRP > 0
\nORDER BY
\n DT_PAU.PAU_PSV_DT<\/p>\n","protected":false},"excerpt":{"rendered":"

WITH PAU AS ( SELECT PAU_STR_DTM ,PAU_END_DTM ,ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3) AS DIFF \/\/\ud734\uc9c0\uc2dc\uac04 \ucc28\uc774 ,CEIL(ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3)\/(7*60)) AS LINE \/\/\uc9c0\ub098\uac04 \uadfc\uc870\uac2f\uc218 \uad6c\ud558\uae30 FROM \ud734\uc9c0\uc2e4\uc801 ) SELECT * FROM PAU CONNECT BY LEVEL < LINE \ub354 \uc88b\uc740 \ucffc\ub9ac\ub97c \uad6c\ud588\ub2e4. \ubd84\uc11d\ud574\uc11c \ub0b4\uaec4\ub85c \ub9cc\ub4e4\uc790!! WITH TV_PAU_RSL AS ( SELECT \/*+ MATERIALIZE *\/ … Continue reading \ud734\uc9c0\uc2dc\uac04\uc758 \ucc28\ub97c \uad6c\ud558\uace0 \uadfc\uc870\uc2dc\uac04\ubcc4\ub85c LOW\ub97c \uc0dd\uc131\ud558\uae30<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/www.hadooh.com\/index.php?rest_route=\/wp\/v2\/posts\/9"}],"collection":[{"href":"https:\/\/www.hadooh.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hadooh.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hadooh.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hadooh.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=9"}],"version-history":[{"count":3,"href":"https:\/\/www.hadooh.com\/index.php?rest_route=\/wp\/v2\/posts\/9\/revisions"}],"predecessor-version":[{"id":61,"href":"https:\/\/www.hadooh.com\/index.php?rest_route=\/wp\/v2\/posts\/9\/revisions\/61"}],"wp:attachment":[{"href":"https:\/\/www.hadooh.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hadooh.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hadooh.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}