est_dt값이 추정일 로 cls_prc 종가값을 뿌리는데요 저는 이전 최근 est_dt값(즉 est_dt_bf)를 기준으로하는 cls_prc를 뿌리고 싶어요 (est_dt_bf_cls_prc)

어렵죠 대충 요게 현재쿼리 ... 너무 이해하시기 힘드실거에요 ㅠ

SELECT b.gicode,
       b.itemabbrnm,
       b.mkt_gb,
       a.cd_nm AS est_inst_nm,
       b.est_dt,
       b.est_dt_bf,
       b.recom_big_cd,
       b.recom_big_cd_bf,
       b.est_dt_prc_cr,
       b.recom_nm,
       b.recom_nm_bf,
       b.target_prc_high,
       b.target_prc_high_bf,
       b.eps,
       b.eps_bf,
       b.cls_prc_bf,
       b.cls_prc_cr,
       b.cls_prc_af,
       b.cls_prc_bf_y_u,
       b.cls_prc_cr_u,
       b.cls_prc_af_u
  FROM fnc_grp_cd_detail a,
       (SELECT   /*+ ORDERED USE_HASH(A B) USE_NL(C D)*/
                 a.gicode,
                 a.itemabbrnm,
                 a.mkt_gb,
                 b.est_inst_cd,
                 b.est_dt,
                 b.recom_big_cd,
                 b.recom_nm,
                 b.target_prc_high,
                 b.eps,
                 b.est_dt_bf,
                 b.recom_big_cd_bf,
                 b.recom_nm_bf,
                 b.target_prc_high_bf,
                 b.eps_bf,
                 sum(decode(c.trd_dt,b.est_dt_bf,c.cls_prc)) as est_dt_prc_cr,
                 SUM (DECODE (c.trd_dt, b.trd_dt_bf, c.cls_prc)) AS cls_prc_bf,
                 SUM (DECODE (c.trd_dt, b.est_dt, c.cls_prc)) AS cls_prc_cr,
                 SUM (DECODE (c.trd_dt, b.trd_dt_af, c.cls_prc)) AS cls_prc_af,
                 SUM (DECODE (d.trd_dt, b.trd_dt_bf, d.cls_prc)) AS cls_prc_bf_y_u,
                 SUM (DECODE (d.trd_dt, b.est_dt, d.cls_prc)) AS cls_prc_cr_u,
                 SUM (DECODE (d.trd_dt, b.trd_dt_af, d.cls_prc)) AS cls_prc_af_u
            FROM fns_j_mast a,
                 (SELECT a.*,
                         (SELECT /*+ INDEX_DESC (X FNC_CALENDAR_PK) */
                                 MIN (x.trd_dt)
                            FROM fnc_calendar x
                           WHERE x.trd_dt < a.est_dt
                             AND open_gb_stock = '0'
                             AND ROWNUM <= 2) AS trd_dt_bf,
                         (SELECT /*+ INDEX (X FNC_CALENDAR_PK) */
                                 MAX (x.trd_dt)
                            FROM fnc_calendar x
                           WHERE x.trd_dt > a.est_dt
                             AND open_gb_stock = '0'
                             AND ROWNUM <= 2) AS trd_dt_af
                    FROM (SELECT a.*,
                                 LAG (a.est_dt) OVER (PARTITION BY a.gicode, a.est_inst_cd ORDER BY a.est_dt) AS est_dt_bf,
                                 LAG (a.recom_big_cd) OVER (PARTITION BY a.gicode, a.est_inst_cd ORDER BY a.est_dt) AS recom_big_cd_bf,
                                 LAG (a.recom_nm) OVER (PARTITION BY a.gicode, a.est_inst_cd ORDER BY a.est_dt) AS recom_nm_bf,
                                 LAG (a.target_prc_high) OVER (PARTITION BY a.gicode, a.est_inst_cd ORDER BY a.est_dt) AS target_prc_high_bf,
                                 LAG (a.eps) OVER (PARTITION BY a.gicode, a.est_inst_cd ORDER BY a.est_dt) AS eps_bf
                            FROM (SELECT a.gicode,
                                         a.est_inst_cd,
                                         a.est_dt,
                                         a.recom_big_cd,
                                         a.recom_nm,
                                         a.target_prc_high,
                                         b.eps
                                    FROM fnf_opinion_mast a,
                                         (SELECT   a.gicode,
                                                   a.est_inst_cd,
                                                   a.est_dt,
                                                   SUM (eps) AS eps
                                              FROM (SELECT a.gicode,
                                                           a.est_inst_cd,
                                                           a.est_dt,
                                                           a.eps,
                                                           ROW_NUMBER () OVER (PARTITION BY a.gicode, a.est_inst_cd, a.est_dt ORDER BY a.gs_ym) AS rn
                                                      FROM fnf_mast_n a
                                                     WHERE a.report_gb = 'A'
                                                        AND a.gicode = 'A005930'
                                                       AND a.est_dt BETWEEN '20070325' AND '20090326'
                                                       AND a.gs_gb IN ('1', '2', '3', '4')) a
                                             WHERE a.rn <= 4
                                          GROUP BY a.gicode,
                                                   a.est_inst_cd,
                                                   a.est_dt
                                            HAVING COUNT (*) = 4) b
                                   WHERE 1 = 1
                                      AND a.gicode = 'A005930'
                                     AND a.est_dt BETWEEN '20070325' AND '20090326'
                                     AND b.gicode(+) = a.gicode
                                     AND b.est_inst_cd(+) = a.est_inst_cd
                                     AND b.est_dt(+) = a.est_dt) a) a
                   WHERE ROWNUM > 0) b,
                 fns_jd c,
                 fns_ud d
           WHERE a.gicode LIKE 'A%0'
             AND a.mkt_gb IN ('1', '2')
             AND a.use_yn = 'Y'
             AND a.stk_gb = '701'
             AND b.gicode = a.gicode
             AND c.gicode = b.gicode
             AND c.trd_dt IN (b.trd_dt_bf, b.est_dt, b.trd_dt_af)
             AND d.u_cd = DECODE (a.mkt_gb, 1, 'I.001', 2, 'I.201')
             AND d.trd_dt = c.trd_dt
        GROUP BY a.gicode,
                 a.itemabbrnm,
                 a.mkt_gb,
                 b.est_inst_cd,
                 b.est_dt,
                 b.recom_big_cd,
                 b.recom_nm,
                 b.target_prc_high,
                 b.eps,
                 b.est_dt_bf,
                 b.recom_big_cd_bf,
                 b.recom_nm_bf,
                 b.target_prc_high_bf,
                 b.eps_bf,
                 b.trd_dt_bf,
                 b.trd_dt_af) b
 WHERE a.grp_cd = 'B190'
   AND a.cd = b.est_inst_cd;