데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

오라클 쿼리문 SQL SERVER로 바꾸려면??

작성자

헤매이다

작성일

2016-07-18 18:33

조회수

7,595

WITH p AS
(
-- 1. 생산 수량
SELECT mechcod
     , mechseq
     , mechnam
     , COALESCE(mm, gb1, gb2) mm
     , CASE gb2 WHEN 'ave' THEN ROUND(SUM(pm)/12., 2) ELSE SUM(pm) END pm
FROM (SELECT m.mechcod
             , m.mechseq
             , m.mechnam
             , SUBSTRING(p.prodate, 5, 2) AS mm
             , tot_num AS pm
             , 'tot' gb1
             , 'ave' gb2
          FROM praotr01 p
         INNER JOIN prfams01 m
            ON m.mechcod = p.mechcod
         WHERE p.prodate BETWEEN '20150101' AND '20151231'
           AND m.use_yn = 'Y'
           AND p.tot_num > 0
        ) p
GROUP BY GROUPING SETS ( (mechcod, mechseq, mechnam, mm )
                        , (mechcod, mechseq, mechnam, gb1)
                        , (mechcod, mechseq, mechnam, gb2)
                        )
)
, b AS
(
-- 2. 불량 수량
SELECT mechcod
     , mechseq
     , mechnam
     , COALESCE(mm, gb1, gb2) mm
     , CASE gb2 WHEN 'ave' THEN ROUND(SUM(bm)/12., 2) ELSE SUM(bm) END bm
FROM (SELECT m.mechcod
             , m.mechseq
             , m.mechnam
             , SUBSTRING(prodate, 5,2) AS mm
             , p.bad_qty AS bm
             , 'tot' gb1
             , 'ave' gb2
          FROM praotr04 p
         INNER JOIN prfams01 m
            ON m.mechcod = p.mechcod
         WHERE p.prodate BETWEEN '20150101' AND '20151231'
           AND m.use_yn = 'Y'
           AND p.bad_qty > 0
        ) b
GROUP BY GROUPING SETS ( (mechcod, mechseq, mechnam, mm )
                        , (mechcod, mechseq, mechnam, gb1)
                        , (mechcod, mechseq, mechnam, gb2)
                        )
)
, n AS
(
-- 5. 달성 목표
SELECT m01,m02,m03,m04,m05,m06,m07,m08,m09,m10,m11,m12
     , (m01+m02+m03+m04+m05+m06+m07+m08+m09+m10+m11+m12)    AS tot
     , (m01+m02+m03+m04+m05+m06+m07+m08+m09+m10+m11+m12)/12 AS ave
FROM pranms01
WHERE m_year = '2015'
   AND mgubun = 'AB'
)
SELECT mechcod
     , mechseq
     , mechnam
     , CASE gb WHEN 'pm' THEN 1
               WHEN 'bm' THEN 2
               WHEN 'pp' THEN 3
               WHEN 'rm' THEN 4
                END AS seq
     , CASE gb WHEN 'pm' THEN ' 1. 생산 수량'
               WHEN 'bm' THEN ' 2. 불량 수량'
               WHEN 'pp' THEN ' 3. 불량 PPM'
               WHEN 'rm' THEN ' 4. 달성율(%)'
                END AS clause
     , "01", "02", "03", "04", "05", "06"
     , "07", "08", "09", "10", "11", "12"
     , "tot", "ave"
FROM (SELECT mechcod
             , mechseq
             , mechnam
             , mm
             , CAST(pm AS DECIMAL(18, 2)) pm
             , CAST(bm AS DECIMAL(18, 2)) bm
             , CAST(1000000. * bm / NULLIF(pm, 0) AS DECIMAL(18, 2)) pp
             , CAST(1000000. * bm / NULLIF(pm, 0)
                            / NULLIF(nm, 0) * 100 AS DECIMAL(18, 2)) rm
          FROM (SELECT NVL(p.mechcod, b.mechcod) mechcod
                     , NVL(p.mechseq, b.mechseq) mechseq
                     , NVL(p.mechnam, b.mechnam) mechnam
                     , NVL(p.mm, b.mm) mm
                     , p.pm
                     , b.bm
                     , CASE NVL(p.mm, b.mm) WHEN '01' THEN n.m01
                                            WHEN '02' THEN n.m02
                                            WHEN '03' THEN n.m03
                                            WHEN '04' THEN n.m04
                                            WHEN '05' THEN n.m05
                                            WHEN '06' THEN n.m06
                                            WHEN '07' THEN n.m07
                                            WHEN '08' THEN n.m08
                                            WHEN '09' THEN n.m09
                                            WHEN '10' THEN n.m10
                                            WHEN '11' THEN n.m11
                                            WHEN '12' THEN n.m12
                                            WHEN 'tot' THEN n.tot
                                            WHEN 'ave' THEN n.ave
                                             END AS nm
                  FROM p
                  FULL OUTER JOIN b
                    ON p.mechcod = b.mechcod
                   AND p.mm      = b.mm
                 CROSS JOIN n
                ) m
        ) a
UNPIVOT (v FOR gb IN (pm, bm, pp, rm)) a
PIVOT (MIN(v) FOR mm IN ( "01", "02", "03", "04", "05", "06"
                         , "07", "08", "09", "10", "11", "12"
                         , "tot", "ave"
                         ) ) a
ORDER BY mechseq, seq
;