SQL 관련

부서별 건수

달콤한부자 2015. 3. 10. 10:51

SELECT
   ORG_NAME,
   SUM(DECODE(YEAR,'13',NVL(REPORT,0)))   R_13,
   SUM(DECODE(YEAR,'14',NVL(REPORT,0)))   R_14,
   SUM(DECODE(YEAR,'13',NVL(DOC_TYPE,0))) A_13,
   SUM(DECODE(YEAR,'14',NVL(DOC_TYPE,0))) A_14,
   SUM(DECODE(YEAR,'13',NVL(BEFORE,0)))   B_13,
   SUM(DECODE(YEAR,'14',NVL(BEFORE,0)))   B_14,
   SUM(DECODE(YEAR,'13',NVL(RENT,0)))     R_13,
   SUM(DECODE(YEAR,'14',NVL(RENT,0)))     R_14
FROM(
       SELECT 
          SUBSTR(OWNER_DEPT_DOC_NO,1,5) ORG_NAME,
          SUBSTR(OWNER_DEPT_DOC_NO,8,2) YEAR,
          COUNT(*) REPORT,
          NULL DOC_TYPE,
          NULL BEFORE,
          NULL RENT
       FROM S_SALE_PROMOTION
       WHERE SUBSTR(OWNER_DEPT_DOC_NO,8,2) IN ('13','14')
          AND SUBSTR(OWNER_DEPT_DOC_NO,1,2) ='SA'
       GROUP BY
          SUBSTR(OWNER_DEPT_DOC_NO,1,5),
          SUBSTR(OWNER_DEPT_DOC_NO,8,2)
       UNION ALL  
       SELECT 
          SUBSTR(OWNER_DEPT_DOC_NO,1,5) ORG_NAME,
          SUBSTR(OWNER_DEPT_DOC_NO,8,2) YEAR,
          NULL REPORT,
          COUNT(*) DOC_TYPE,
          NULL BEFORE,
          NULL RENT
       FROM S_SALE_PROMOTION
       WHERE DOC_TYPE_SUB ='AFTER'
          AND SUBSTR(OWNER_DEPT_DOC_NO,8,2) IN ('13','14')
          AND SUBSTR(OWNER_DEPT_DOC_NO,1,2) ='SA'
       GROUP BY
          SUBSTR(OWNER_DEPT_DOC_NO,1,5),
          SUBSTR(OWNER_DEPT_DOC_NO,8,2)
       UNION ALL
       SELECT
          SUBSTR(OWNER_DEPT_DOC_NO,1,5) ORG_NAME,
          SUBSTR(OWNER_DEPT_DOC_NO,8,2) YEAR,
          NULL REPORT,
          NULL DOC_TYPE,
          COUNT(*) BEFORE,
          NULL RENT
       FROM S_SALE_PROMOTION
       WHERE SUBSTR(OWNER_DEPT_DOC_NO,8,2) IN ('13','14')
          AND BEFORE_PAY_FLG ='Y'
          AND SUBSTR(OWNER_DEPT_DOC_NO,1,2) ='SA'
       GROUP BY
          SUBSTR(OWNER_DEPT_DOC_NO,1,5),
          SUBSTR(OWNER_DEPT_DOC_NO,8,2),
          BEFORE_PAY_FLG
       UNION ALL
       SELECT
          SUBSTR(OWNER_DEPT_DOC_NO,1,5) ORG_NAME,
          SUBSTR(OWNER_DEPT_DOC_NO,8,2) YEAR,
          NULL REPORT,
          NULL DOC_TYPE,
          NULL BEFORE,
          COUNT(*) RENT       
       FROM S_SALE_PROMOTION
       WHERE SUBSTR(OWNER_DEPT_DOC_NO,8,2) IN ('13','14')
          AND  CARD_RENT_PAY_FLG='Y'
          AND SUBSTR(OWNER_DEPT_DOC_NO,1,2) ='SA'
       GROUP BY
          SUBSTR(OWNER_DEPT_DOC_NO,1,5),
          SUBSTR(OWNER_DEPT_DOC_NO,8,2)
    )
GROUP BY
   ORG_NAME
        

저작자표시