휴지시간의 차를 구하고 근조시간별로 LOW를 생성하기

WITH PAU AS (
SELECT
PAU_STR_DTM
,PAU_END_DTM
,ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3) AS DIFF //휴지시간 차이
,CEIL(ROUND(((PAU_END_DTM-PAU_STR_DTM) * 24 * 60), 3)/(7*60)) AS LINE //지나간 근조갯수 구하기
FROM 휴지실적
)
SELECT *
FROM PAU
CONNECT BY LEVEL < LINE 더 좋은 쿼리를 구했다. 분석해서 내껄로 만들자!! WITH TV_PAU_RSL AS ( SELECT /*+ MATERIALIZE */ PAU.FAC_TP , PAU.PROC_TP , PAU.EQU_CD , TO_CHAR(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') PAU_STR_DTM , TO_CHAR(PAU.PAU_END_DTM, 'YYYYMMDDHH24MISS') PAU_END_DTM , TRUNC(PAU.PAU_STR_DTM, 'MI') AS PAU_STR_DTM2 , TRUNC(PAU.PAU_END_DTM, 'MI') AS PAU_END_DTM2 , TRUNC(PAU.PAU_END_DTM, 'MI') - TRUNC(PAU.PAU_STR_DTM, 'MI') AS CNT FROM TB_M30_PAU_RSL PAU WHERE PAU.FAC_TP = :FAC_TP AND PAU.PROC_TP = :PROC_TP AND TO_CHAR(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') = :PAU_STR_DTM AND PAU.PAU_END_DTM IS NOT NULL ) , TV_DT_PAU_RSL_BASE AS ( SELECT DISTINCT CASE WHEN ('060000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM , TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '060000', 'YYYYMMDDHH24MISS')) ELSE CASE WHEN ('140000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM , TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '140000', 'YYYYMMDDHH24MISS')) ELSE CASE WHEN ('220000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') AND TO_CHAR(PAU_E_DTM, 'HH24MISS')) THEN DECODE(DUMMY.LVL, 1, PAU.PAU_S_DTM , TO_DATE(TO_CHAR(PAU.PAU_E_DTM, 'YYYYMMDD') || '220000', 'YYYYMMDDHH24MISS')) ELSE PAU_S_DTM END END END AS PAU_S_DTM , CASE WHEN '060000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '060000', 'YYYYMMDDHH24MISS') , PAU.PAU_E_DTM) ELSE CASE WHEN '140000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '140000', 'YYYYMMDDHH24MISS') , PAU.PAU_E_DTM) ELSE CASE WHEN '220000' BETWEEN TO_CHAR(PAU_S_DTM, 'HH24MISS') AND TO_CHAR(PAU_E_DTM, 'HH24MISS') THEN DECODE(DUMMY.LVL, 1, TO_DATE(TO_CHAR(PAU.PAU_S_DTM, 'YYYYMMDD') || '220000', 'YYYYMMDDHH24MISS') , PAU.PAU_E_DTM) ELSE PAU_E_DTM END END END AS PAU_E_DTM FROM ( SELECT DECODE(CONNECT_BY_ISLEAF, 0, PAU.PAU_STR_DTM2 + ((LEVEL-1)/24) , PAU.PAU_END_DTM2) AS PAU_S_DTM , LEAD(DECODE(CONNECT_BY_ISLEAF, 0, PAU.PAU_STR_DTM2 + ((LEVEL-1)/24) , PAU.PAU_END_DTM2), 1, PAU.PAU_END_DTM2) OVER(ORDER BY LEVEL) AS PAU_E_DTM FROM TV_PAU_RSL PAU CONNECT BY LEVEL <= (PAU_END_DTM2 - PAU_STR_DTM2) * 24 + 2 ) PAU , ( SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 2) DUMMY ORDER BY PAU_S_DTM ) , TV_DT_PAU_RSL AS ( SELECT CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '000000' AND '055959' THEN TO_CHAR(PAU_S_DTM-1, 'YYYYMMDD') ELSE TO_CHAR(PAU_S_DTM, 'YYYYMMDD') END AS PAU_PSV_DT, CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '060000' AND '135959' THEN 1 ELSE CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '140000' AND '215959' THEN 2 ELSE 3 END END AS WK_SHF , ROUND(SUM((PAU_E_DTM - PAU_S_DTM) * 24 * 60),1) AS PAU_HRP FROM TV_DT_PAU_RSL_BASE GROUP BY CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '000000' AND '055959' THEN TO_CHAR(PAU_S_DTM-1, 'YYYYMMDD') ELSE TO_CHAR(PAU_S_DTM, 'YYYYMMDD') END, CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '060000' AND '135959' THEN 1 ELSE CASE WHEN TO_CHAR(PAU_S_DTM, 'HH24MISS') BETWEEN '140000' AND '215959' THEN 2 ELSE 3 END END ) SELECT PAU.FAC_TP , PAU.PROC_TP , PAU.EQU_CD , DT_PAU.PAU_PSV_DT , ( SELECT NVL(MAX(X.SEQ), 0) FROM TB_M30_PAU_RSL_DT X WHERE X.FAC_TP = PAU.FAC_TP AND X.PROC_TP = PAU.PROC_TP AND X.PAU_PSV_DT = DT_PAU.PAU_PSV_DT ) + (ROW_NUMBER() OVER (ORDER BY 1 DESC)) AS SEQ , :ObjectType , :ObjectId , :ProgramId , :Timestamp , :ObjectType , :ObjectId , :ProgramId , :Timestamp , DT_PAU.PAU_HRP , TO_DATE(PAU.PAU_STR_DTM, 'YYYYMMDDHH24MISS') AS PAU_STR_DTM , DT_PAU.WK_SHF , (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 FROM TV_PAU_RSL PAU , TV_DT_PAU_RSL DT_PAU WHERE DT_PAU.PAU_HRP > 0
ORDER BY
DT_PAU.PAU_PSV_DT

Leave a Reply

Your email address will not be published. Required fields are marked *