SELECT MAX(LINK_ID)     LINK_ID,     MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG, 
       MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM)   STAT_HM, 
       MAX(DECODE(STAT_YM, '201403', TFVL,      0)) TFVL_A,     MAX(DECODE(STAT_YM, '201403', SPED,    0)) SPED_A, 
       MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A, 
       MAX(DECODE(STAT_YM, '201404', TFVL,      0)) TFVL_B,     MAX(DECODE(STAT_YM, '201404', SPED,    0)) SPED_B, 
       MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B 
  FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG, 
               B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH 
          FROM (SELECT T2.LINK_ID, T2.F_NODE, T2.T_NODE, T2.LINK_LENG, T3.NODE_NAME||'-'||T4.NODE_NAME SECT_NAME, T2.SECT_GRAD_CD 
                  FROM TB_LINK T2, TB_NODE T3, TB_NODE T4 
                 WHERE T2.LINK_ID = '3850428200' 
                   AND T2.F_NODE = T3.NODE_ID 
                   AND T2.T_NODE = T4.NODE_ID 
               ) A, 
               (SELECT STAT_YM, LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH 
                  FROM TB_LINK_15M_PTRN -- 
                 WHERE LINK_ID = '3850428200' 
                   AND STAT_YM IN( '201403', '201404' ) 
                   AND STAT_HM BETWEEN '2200' AND '2359' 
                   AND DAY_TYPE_CD = 'DTW7' 
               ) B 
         WHERE A.LINK_ID = B.LINK_ID 
      ) 
 GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM 
 ORDER BY STAT_HM

SELECT MAX(LINK_ID)     LINK_ID,     MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG,     
       MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM)   STAT_HM,     
       MAX(DECODE(STAT_YM, '201403', TFVL,      0)) TFVL_A,     MAX(DECODE(STAT_YM, '201403', SPED,    0)) SPED_A,     
       MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A,     
       MAX(DECODE(STAT_YM, '201404', TFVL,      0)) TFVL_B,     MAX(DECODE(STAT_YM, '201404', SPED,    0)) SPED_B,     
       MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B     
  FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,     
               B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH     
          FROM (SELECT T2.IFSC_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD     
                  FROM TB_IFSC T2
                 WHERE T2.IFSC_ID = '3850428200'     
               ) A,     
               (SELECT STAT_YM, IFSC_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH     
                  FROM	TB_IFSC_15M_PTRN --    
                 WHERE IFSC_ID = '3850428200'     
                   AND STAT_YM IN( '201403', '201404' )     
                   AND STAT_HM BETWEEN '2200' AND '2359'     
                   AND DAY_TYPE_CD = 'DTW7'     
               ) B     
         WHERE A.LINK_ID = B.LINK_ID     
       )     
 GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM     
 ORDER BY STAT_HM


SELECT MAX(LINK_ID)     LINK_ID,     MAX(SECT_NAME) LINK_NAME, MAX(LINK_LENG) LINK_LENG, 
       MAX(DAY_TYPE_CD) DAY_TYPE_CD, MAX(STAT_HM)   STAT_HM, 
       MAX(DECODE(STAT_YM, '201403', TFVL,      0)) TFVL_A,     MAX(DECODE(STAT_YM, '201403', SPED,    0)) SPED_A, 
       MAX(DECODE(STAT_YM, '201403', OCPY_RATE, 0)) OCPYRATE_A, MAX(DECODE(STAT_YM, '201403', TRVL_HH, 0)) TRVLHH_A, 
       MAX(DECODE(STAT_YM, '201404', TFVL,      0)) TFVL_B,     MAX(DECODE(STAT_YM, '201404', SPED,    0)) SPED_B, 
       MAX(DECODE(STAT_YM, '201404', OCPY_RATE, 0)) OCPYRATE_B, MAX(DECODE(STAT_YM, '201404', TRVL_HH, 0)) TRVLHH_B 
  FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG, 
               B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH 
          FROM (SELECT T2.ROAD_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD 
                  FROM TB_ROAD T2 
                 WHERE T2.ROAD_ID = '3850428200' 
               ) A, 
               (SELECT STAT_YM, ROAD_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH 
                  FROM TB_ROAD_15M_PTRN --
                 WHERE ROAD_ID = '3850428200' 
                   AND STAT_YM IN( '201403', '201404' ) 
                   AND STAT_HM BETWEEN '2200' AND '2359'   
                   AND DAY_TYPE_CD = 'DTW7' 
               ) B 
         WHERE A.LINK_ID = B.LINK_ID 
       ) 
 GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM 
 ORDER BY STAT_HM

