작성하신 쿼리를 올려주세요.

우리집아찌님이 말씀하신데로 테이블명 컬럼명만 바꿔서 올립니다

( 데이터및 결과화면이 필요하시면 다시 정리해서 올리겠습니다 )

0.아래의쿼리는 나름 LAST_VALUE를 써서 해본겁니다.

  - WITH T1 까지가 1차적인 ROLLUP이고 밑의 T2 부터는 ROLLUP한데이터를 가공하기위한거니 신경안쓰셔도됩니다.

1. 기간조회는 2017-07-03 ~ 2017-07-30 을입력하면 화면달력에서 주차를

2017년 27주 ~ 2017년 30주로 검색이됩니다.

3개의 테이블입니다

TARGET_TABLE (메인테이블)

WEEK_T(입력날짜가 어느주에 속하는지 (년/월/주)로 가져오는 기준테이블)

LN_T( 라인테이블입니다)

--------------
TARGET_TABLE
--------------
F_CD
P_CD
YY
MM
WK
MD
LN
T_INI
T_TG
T_RS
BR
T_RT
RD

--------------
WEEK_T
--------------
YY
MM
WK
START_DT
END_DT
D_FLAG
U_FLAG
--------------
LN_T
--------------
LN_CD
LN_NM

요청하신쿼리는 아래와같습니다

/* LAST_VALUE 이용한값처리 */
WITH T AS (
  SELECT A.MD
        ,A.LN
        ,DECODE( B.IDX,1,'GUBUN1',2,'GUBUN2',3,'GUBUN3',4,'GUBUN4') GUBUN
        ,SUM( DECODE( A.YY||A.WK, '201727', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201727"
        ,SUM( DECODE( A.YY||A.WK, '201728', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201728"
        ,SUM( DECODE( A.YY||A.WK, '201729', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201729"
        ,SUM( DECODE( A.YY||A.WK, '201730', DECODE( B.IDX, 1, A.T_TG, 2, A.T_RS, 3, A.BR, 4, A.T_RT,0), 0  )) "201730"
        ,SUM( CASE WHEN A.YY||A.WK IN (SELECT YY || LPAD(WK,2,0) 
                                         FROM WEEK_T 
                                        WHERE START_DT <= '20170730'
                                          AND END_DT >= '20170703'
                                          AND D_FLAG = 'N' AND U_FLAG = 'Y' AND D_FLAG = 'N' AND U_FLAG = 'Y'
                                      ) THEN DECODE(B.IDX,1,A.T_TG,2,A.T_RS,3,A.BR,4,A.T_RT,0)
                   ELSE 0
               END) TOT
        ,B.IDX
  FROM (
        SELECT NVL(A.MD,B.MD) MD
              ,NVL(A.LN,B.LN) LN
              ,NVL(A.YY,B.YY) YY
              ,NVL(A.MM,B.MM) MM
              ,NVL(A.WK,B.WK) WK
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.T_TG,B.T_TG),0,NULL,NVL(A.T_TG,B.T_TG)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) T_TG
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.T_RS,B.T_RS),0,NULL,NVL(A.T_RS,B.T_RS)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) T_RS
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.BR,B.BR),0,NULL,NVL(A.BR,B.BR)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) BR
              ,NVL(TO_NUMBER(LAST_VALUE(DECODE(NVL(A.T_RT,B.T_RT),0,NULL,NVL(A.T_RT,B.T_RT)) IGNORE NULLS) OVER (PARTITION BY A.F_CD,A.P_CD,A.MD,A.LN ORDER BY A.YY,A.MM,A.WK)),0) T_RT
--              ,NVL(A.T_TG,B.T_TG) T_TG
--              ,NVL(A.T_RS,B.T_RS) T_RS
--              ,NVL(A.BR,B.BR) BR
--              ,NVL(A.T_RT,B.T_RT) T_RT
          FROM (
                SELECT A.MD
                      ,A.LN
                      ,NVL(A.YY,B.YY) YY
                      ,NVL(A.MM,B.MM) MM
                      ,NVL(A.WK,B.WK) WK
                      ,DENSE_RANK() OVER (ORDER BY A.MD,A.LN) DR
                      ,A.T_TG
                      ,A.T_RS
                      ,A.BR
                      ,A.T_RT
                  FROM (SELECT YY YY 
                              ,LPAD(MM,2,0) MM
                              ,LPAD(WK,2,0) WK 
                          FROM WK_T 
                         WHERE START_DT <= '20170730'
                           AND END_DT >= '20170703' 
                           AND D_FLAG = 'N' AND U_FLAG = 'Y' 
                         ORDER BY YY, MM, WK
                        ) B
                       LEFT OUTER JOIN
                        TARGET_TABLE A
                       PARTITION BY (A.MD, A.LN ) ON (
                        A.YY = B.YY
                    AND A.MM = B.MM
                    AND A.WK = B.WK
                        )
                  WHERE 1=1
                    AND A.LN <> '9999'
                  ORDER BY
                        A.MD
                       ,A.LN
                       ,NVL(A.YY,B.YY)
                       ,NVL(A.MM,B.MM)
                       ,NVL(A.WK,B.WK)
               ) A
            FULL OUTER JOIN
               (SELECT * FROM TARGET_TABLE
                 WHERE LN <> '9999'
               )B
            ON 1=1
           AND A.MD       = B.MD
           AND A.LN       = B.LN
           AND A.YY       = B.YY
           AND A.MM       = B.MM
           AND A.WK       = B.WK
         ORDER BY
               1,2,3,4,5 
       )A
      ,(SELECT LEVEL IDX FROM DUAL CONNECT BY LEVEL<=4)B
 WHERE 1=1
 GROUP BY
       A.MD
      ,A.LN
      ,B.IDX
 ORDER BY
       A.MD
      ,A.LN
      ,B.IDX
)
/* 1차적인 rollup값을 구한다 */
SELECT MD,LN,GUBUN
            ,CASE WHEN "201727" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201727" END "201727"
            ,CASE WHEN "201728" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201728" END "201728"
            ,CASE WHEN "201729" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201729" END "201729"
            ,CASE WHEN "201730" = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE "201730" END "201730"
            ,CASE WHEN TOT = 0 AND GR>0 AND IDX = 2 THEN NULL ELSE TOT END TOT
      ,GR,IDX,GR1
  FROM (
        SELECT DECODE(1, GROUPING(A.MD), 'Total', A.MD) MD
              ,DECODE(1, GROUPING(A.MD), '', GROUPING(A.LN), 'Sub Total', A.LN) LN
              ,A.GUBUN
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201727"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201727"),0) ELSE 0 END "201727"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201728"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201728"),0) ELSE 0 END "201728"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201729"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201729"),0) ELSE 0 END "201729"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM("201730"),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM("201730"),0) ELSE 0 END "201730"
              ,CASE WHEN MAX(A.IDX) IN (1,2) THEN ROUND(SUM(TOT),1) WHEN MAX(A.IDX) IN (3,4) THEN ROUND(SUM(TOT),0) ELSE 0 END TOT
              ,TO_CHAR(DENSE_RANK() OVER (ORDER BY A.MD NULLS FIRST
                                         )-1) GR
              ,MAX(A.IDX) IDX
              ,NVL(A.LN,'9999') GR1
          FROM T A
         GROUP BY ROLLUP(A.MD, (A.LN)), GUBUN
        HAVING NOT( MAX(A.IDX) IN (1,3,4) AND GROUPING(A.MD) = 0 AND GROUPING(A.LN) = 0 )
         ORDER BY A.MD NULLS FIRST
                , A.LN  NULLS FIRST
                , MAX(A.IDX)
    )
;

쿼리가 너무 길고 복잡하네요.
테이블도 몇개 안되고 사용되는 항목도 많지 않은데..이렇게까지 복잡할 이유가 없네요.
기간 조건 주는 방법도 너무 복잡하구요.
target_table 자료 좀 보여주세요. 표형태로.
전체 건수는 어느정도, 월별 건수는?
조회 기간 이전의 0 이 아닌 자료를 찾는 방법을 고민해야 합니다.
조회 기간보다 이전 자료를 더 가져와야지만 last_value 를 적용할 수 있겟지요?
0 인 자료가 연속되는 기간은 최대 어느 정도?
이 기간 정도만 더 가져오면 될지? 아니면 처음부터 다 가져와야 할지 등등..고민이 필요합니다.

-- 기간 검색 조건 주는 법
-- 시작일과 종료일을 교차 비교
SELECT *
  FROM week_t
 WHERE start_dt <= '20170730'
   AND   end_dt >= '20170703'
;