ITS_OP/SRC/SQL/ATRD_2.sql

SELECT LX.ATRD_ID, LX.DRCT_CD, LX.DRCT_CD_NM, LX.DRCT_NM, LX.ATRD_NM, LX.ATRD_LENG,
       LX.IFSC_NM, LX.IFSC_STRT_NM, LX.IFSC_END_NM, LX.IFSC_LENG, 
       LX.LINK_ID, LX.LINK_STRT_NM, LX.LINK_END_NM, LX.LINK_LENG,
       LY.TTM AS ARTERYSPD,
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T00, NULL, '-', T00)) AS C00,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T01, NULL, '-', T01)) AS C01,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T02, NULL, '-', T02)) AS C02,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T03, NULL, '-', T03)) AS C03,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T04, NULL, '-', T04)) AS C04,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T05, NULL, '-', T05)) AS C05,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T06, NULL, '-', T06)) AS C06,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T07, NULL, '-', T07)) AS C07,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T08, NULL, '-', T08)) AS C08,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T09, NULL, '-', T09)) AS C09,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T10, NULL, '-', T10)) AS C10,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T11, NULL, '-', T11)) AS C11,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T12, NULL, '-', T12)) AS C12,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T13, NULL, '-', T13)) AS C13,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T14, NULL, '-', T14)) AS C14,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T15, NULL, '-', T15)) AS C15,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T16, NULL, '-', T16)) AS C16,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T17, NULL, '-', T17)) AS C17,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T18, NULL, '-', T18)) AS C18,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T19, NULL, '-', T19)) AS C19,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T20, NULL, '-', T20)) AS C20,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T21, NULL, '-', T21)) AS C21,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T22, NULL, '-', T22)) AS C22,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T23, NULL, '-', T23)) AS C23  
  FROM MV_ATRD_LINK LX,
       (SELECT /*+ INDEX(A PK_LINK_HH_STAT) */
               B.ATRD_ID, B.IFSC_ID, B.LINK_ID,
               ROUND(AVG(DECODE(A.SPED, NULL, 0, A.SPED)), 0) TTM, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '00', A.SPED, NULL)), 0) AS T00, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '01', A.SPED, NULL)), 0) AS T01, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '02', A.SPED, NULL)), 0) AS T02, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '03', A.SPED, NULL)), 0) AS T03, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '04', A.SPED, NULL)), 0) AS T04, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '05', A.SPED, NULL)), 0) AS T05, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '06', A.SPED, NULL)), 0) AS T06, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '07', A.SPED, NULL)), 0) AS T07, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '08', A.SPED, NULL)), 0) AS T08, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '09', A.SPED, NULL)), 0) AS T09, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '10', A.SPED, NULL)), 0) AS T10, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '11', A.SPED, NULL)), 0) AS T11, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '12', A.SPED, NULL)), 0) AS T12, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '13', A.SPED, NULL)), 0) AS T13, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '14', A.SPED, NULL)), 0) AS T14, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '15', A.SPED, NULL)), 0) AS T15, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '16', A.SPED, NULL)), 0) AS T16, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '17', A.SPED, NULL)), 0) AS T17, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '18', A.SPED, NULL)), 0) AS T18, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '19', A.SPED, NULL)), 0) AS T19, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '20', A.SPED, NULL)), 0) AS T20, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '21', A.SPED, NULL)), 0) AS T21, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '22', A.SPED, NULL)), 0) AS T22, 
               ROUND(AVG(DECODE(SUBSTR(A.STAT_DT, 9, 2), '23', A.SPED, NULL)), 0) AS T23
          FROM TB_LINK_HH_STAT A,
               MV_ATRD_LINK    B
         WHERE 1=1
           AND A.STAT_DT BETWEEN '20190501000000' AND '20190631235959'
           AND A.DAY_TYPE_CD IN ('DTW1', 'DTW2', 'DTW3', 'DTW4', 'DTW5', 'DTW6', 'DTW7')
           AND A.LINK_ID(+) = B.LINK_ID
           --AND B.ATRD_ID IN ('ATRD000100', 'ATRD000101')
         GROUP BY ROLLUP (B.ATRD_ID, B.IFSC_ID, B.LINK_ID)
        ) LY
  WHERE LX.ATRD_ID = LY.ATRD_ID
    AND LX.LINK_ID = LY.LINK_ID
  ORDER BY LX.ATRD_ID, LX.DRCT_CD, LX.LINK_SEQ