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,
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(TTM, 0, '-', ROUND(TLL*3.6/TTM, 0))) AS ARTERYSPD,
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T00, 0, '-', ROUND(L00*3.6/T00, 0))) AS C00,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T01, 0, '-', ROUND(L01*3.6/T01, 0))) AS C01,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T02, 0, '-', ROUND(L02*3.6/T02, 0))) AS C02,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T03, 0, '-', ROUND(L03*3.6/T03, 0))) AS C03,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T04, 0, '-', ROUND(L04*3.6/T04, 0))) AS C04,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T05, 0, '-', ROUND(L05*3.6/T05, 0))) AS C05,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T06, 0, '-', ROUND(L06*3.6/T06, 0))) AS C06,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T07, 0, '-', ROUND(L07*3.6/T07, 0))) AS C07,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T08, 0, '-', ROUND(L08*3.6/T08, 0))) AS C08,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T09, 0, '-', ROUND(L09*3.6/T09, 0))) AS C09,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T10, 0, '-', ROUND(L10*3.6/T10, 0))) AS C10,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T11, 0, '-', ROUND(L11*3.6/T11, 0))) AS C11,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T12, 0, '-', ROUND(L12*3.6/T12, 0))) AS C12,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T13, 0, '-', ROUND(L13*3.6/T13, 0))) AS C13,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T14, 0, '-', ROUND(L14*3.6/T14, 0))) AS C14,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T15, 0, '-', ROUND(L15*3.6/T15, 0))) AS C15,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T16, 0, '-', ROUND(L16*3.6/T16, 0))) AS C16,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T17, 0, '-', ROUND(L17*3.6/T17, 0))) AS C17,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T18, 0, '-', ROUND(L18*3.6/T18, 0))) AS C18,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T19, 0, '-', ROUND(L19*3.6/T19, 0))) AS C19,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T20, 0, '-', ROUND(L20*3.6/T20, 0))) AS C20,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T21, 0, '-', ROUND(L21*3.6/T21, 0))) AS C21,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T22, 0, '-', ROUND(L22*3.6/T22, 0))) AS C22,      
       DECODE(LX.ATRD_ID, NULL, '-', DECODE(T23, 0, '-', ROUND(L23*3.6/T23, 0))) AS C23  
  FROM MV_ATRD_LINK LX,
       (SELECT /*+ INDEX(A PK_LINK_HH_STAT) */
               B.ATRD_ID, B.IFSC_ID, B.LINK_ID,
               SUM(DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG)) TLL, 
               SUM(DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH  )) TTM, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '00', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T00, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '00', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L00, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '01', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T01, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '01', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L01, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '02', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T02, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '02', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L02, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '03', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T03, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '03', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L03, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '04', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T04, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '04', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L04, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '05', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T05, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '05', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L05, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '06', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T06, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '06', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L06, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '07', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T07, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '07', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L07, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '08', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T08, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '08', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L08, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '09', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T09, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '09', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L09, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '10', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T10, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '10', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L10, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '11', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T11, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '11', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L11, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '12', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T12, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '12', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L12, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '13', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T13, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '13', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L13, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '14', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T14, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '14', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L14, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '15', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T15, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '15', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L15, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '16', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T16, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '16', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L16, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '17', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T17, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '17', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L17, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '18', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T18, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '18', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L18, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '19', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T19, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '19', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L19, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '20', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T20, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '20', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L20, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '21', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T21, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '21', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L21, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '22', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T22, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '22', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L22, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '23', DECODE(A.TRVL_HH, NULL, 0, A.TRVL_HH),   0)) AS T23, 
               SUM(DECODE(SUBSTR(A.STAT_DT, 9, 2), '23', DECODE(A.TRVL_HH, NULL, 0, B.LINK_LENG), 0)) AS L23  
          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