특정관리품목 List 쿼리문..

 
회사에서 개발(?)을 하면서 만든 쿼리문 입니다..

---

2개의 테이블에서 1년간의 제품 입,출고 수량을 가져오는 것인데..

우선 2개의 테이블의 구조가 다르고.. 1개의 테이블은 이리저리 손이 많이 가더군요..
SmallDate 였나.. -_-; 그래서.. 간단히 left() 함수도 안 먹히고..
그래서 convert(char, )를 이용하여.. 복잡한 변환과정을 거칩니다..

거기에 kind에 따라 -값을 줘야되는 상황이 발생하였습니다..
1개의 테이블은 입출고가 + - 로 대충 구분이 되는 반면에..
1개의 테이블은 입출고가 모두 + 이며.. kind값에 따라 사람이 인지해야 하더군요;;
하여간 복잡한 쿼리.. 흑..ㅠ

---

--전체
Select a.stock_code, sum(입고총계) as 입고총계, sum(출고총계) as 출고총계,
 sum(iqty1) as iqty1, sum(iqty2) as iqty2, sum(iqty3) as iqty3, sum(iqty4) as iqty4,
 sum(iqty5) as iqty5, sum(iqty6) as iqty6, sum(iqty7) as iqty7, sum(iqty8) as iqty8,
 sum(iqty9) as iqty9, sum(iqty10) as iqty10, sum(iqty11) as iqty11, sum(iqty12) as iqty12,
 sum(oqty1) as oqty1, sum(oqty2) as oqty2, sum(oqty3) as oqty3, sum(oqty4) as oqty4,
 sum(oqty5) as oqty5, sum(oqty6) as oqty6, sum(oqty7) as oqty7, sum(oqty8) as oqty8,
 sum(oqty9) as oqty9, sum(oqty10) as oqty10, sum(oqty11) as oqty11, sum(oqty12) as oqty12
From (
 -- 입고 계
 Select stock_code, sum(stock_qty) as 입고총계, 출고총계 = 0,
 -- 수불총계1 = 0, 수불총계2 = 0,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/01' then stock_qty else 0 end) as iqty1,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/02' then stock_qty else 0 end) as iqty2,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/03' then stock_qty else 0 end) as iqty3,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/04' then stock_qty else 0 end) as iqty4,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/05' then stock_qty else 0 end) as iqty5,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/06' then stock_qty else 0 end) as iqty6,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/07' then stock_qty else 0 end) as iqty7,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/08' then stock_qty else 0 end) as iqty8,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/09' then stock_qty else 0 end) as iqty9,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/10' then stock_qty else 0 end) as iqty10,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/11' then stock_qty else 0 end) as iqty11,
  sum(case when left(convert(char, stock_date, 111), 7) = '2008/12' then stock_qty else 0 end) as iqty12,
  oqty1 = 0, oqty2 = 0, oqty3 = 0, oqty4 = 0,
  oqty5 = 0, oqty6 = 0, oqty7 = 0, oqty8 = 0,
  oqty9 = 0, oqty10 = 0, oqty11 = 0, oqty12 = 0
 From Store_Stock
 Where Stock_Code in (Select distinct 제품코드
    From ACC_특정관리)
  and (stock_date between '2008/01/01' and '2008/12/31')
  and stock_kind in ('52', '58', '60')
 Group by stock_code


 Union All


 -- 전체 출고 계
 Select stock_code, 입고총계 = 0, sum(수불총계) + sum(판매총계) as 수불총계,
  iqty1 = 0, iqty2 = 0, iqty3 = 0, iqty4 = 0,
  iqty5 = 0, iqty6 = 0, iqty7 = 0, iqty8 = 0,
  iqty9 = 0, iqty10 = 0, iqty11 = 0, iqty12 = 0,
  sum(oqty1) as oqty1, sum(oqty2) as oqty2, sum(oqty3) as oqty3, sum(oqty4) as oqty4,
  sum(oqty5) as oqty5, sum(oqty6) as oqty6, sum(oqty7) as oqty7, sum(oqty8) as oqty8,
  sum(oqty9) as oqty9, sum(oqty10) as oqty10, sum(oqty11) as oqty11, sum(oqty12) as oqty12
 From (

  -- 수불 부분
  Select stock_code, (수불총계1 - 수불총계2) as 수불총계, 판매총계 = 0,
   (o1qty1 - o2qty1) as oqty1, (o1qty2 - o2qty2) as oqty2, (o1qty3 - o2qty3) as oqty3, (o1qty4 - o2qty4) as oqty4,
   (o1qty5 - o2qty5) as oqty5, (o1qty6 - o2qty6) as oqty6, (o1qty7 - o2qty7) as oqty7, (o1qty8 - o2qty8) as oqty8,
   (o1qty9 - o2qty9) as oqty9, (o1qty10 - o2qty10) as oqty10, (o1qty11 - o2qty11) as oqty11, (o1qty12 - o2qty12) as oqty12
 
  From
  (
   Select stock_code, sum(수불총계1) as 수불총계1,
    sum(o1qty1) as o1qty1,   sum(o1qty2) as o1qty2,   sum(o1qty3) as o1qty3, sum(o1qty4) as o1qty4,
    sum(o1qty5) as o1qty5,   sum(o1qty6) as o1qty6,   sum(o1qty7) as o1qty7, sum(o1qty8) as o1qty8,
    sum(o1qty9) as o1qty9,   sum(o1qty10) as o1qty10,   sum(o1qty11) as o1qty11, sum(o1qty12) as o1qty12,
    sum(수불총계2) as 수불총계2,
    sum(o2qty1) as o2qty1, sum(o2qty2) as o2qty2, sum(o2qty3) as o2qty3, sum(o2qty4) as o2qty4,
    sum(o2qty5) as o2qty5, sum(o2qty6) as o2qty6, sum(o2qty7) as o2qty7, sum(o2qty8) as o2qty8,
    sum(o2qty9) as o2qty9, sum(o2qty10) as o2qty10, sum(o2qty11) as o2qty11, sum(o2qty12) as o2qty12
   From (
    -- 수불 출고
    Select stock_code, sum(stock_qty) as 수불총계1,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/01' then stock_qty else 0 end) as o1qty1,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/02' then stock_qty else 0 end) as o1qty2,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/03' then stock_qty else 0 end) as o1qty3,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/04' then stock_qty else 0 end) as o1qty4,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/05' then stock_qty else 0 end) as o1qty5,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/06' then stock_qty else 0 end) as o1qty6,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/07' then stock_qty else 0 end) as o1qty7,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/08' then stock_qty else 0 end) as o1qty8,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/09' then stock_qty else 0 end) as o1qty9,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/10' then stock_qty else 0 end) as o1qty10,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/11' then stock_qty else 0 end) as o1qty11,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/12' then stock_qty else 0 end) as o1qty12,
     수불총계2 = 0, o2qty1 = 0, o2qty2 = 0, o2qty3 = 0, o2qty4 = 0, o2qty5 = 0, o2qty6 = 0,
      o2qty7 = 0, o2qty8 = 0, o2qty9 = 0, o2qty10 = 0, o2qty11 = 0, o2qty12 = 0
    From Store_Stock
    Where Stock_Code in (Select distinct 제품코드
       From ACC_특정관리)
     and (stock_date between '2008/01/01' and '2008/12/31')
     and stock_kind in ('30', '31', '32', '33', '34', '35', '36', '37', '38', '40', '49')
    Group by stock_code
   
    Union all
   
    -- 수불 - 출고
    Select stock_code,
     수불총계1 = 0,  o1qty1 = 0, o1qty2 = 0, o1qty3 = 0, o1qty4 = 0, o1qty5 = 0, o1qty6 = 0,
      o1qty7 = 0,  o1qty8 = 0, o1qty9 = 0, o1qty10 = 0, o1qty11 = 0, o1qty12 = 0,
      sum(stock_qty) as 수불총계2,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/01' then stock_qty else 0 end) as o2qty1,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/02' then stock_qty else 0 end) as o2qty2,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/03' then stock_qty else 0 end) as o2qty3,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/04' then stock_qty else 0 end) as o2qty4,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/05' then stock_qty else 0 end) as o2qty5,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/06' then stock_qty else 0 end) as o2qty6,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/07' then stock_qty else 0 end) as o2qty7,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/08' then stock_qty else 0 end) as o2qty8,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/09' then stock_qty else 0 end) as o2qty9,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/10' then stock_qty else 0 end) as o2qty10,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/11' then stock_qty else 0 end) as o2qty11,
     sum(case when left(convert(char, stock_date, 111), 7) = '2008/12' then stock_qty else 0 end) as o2qty12
    From Store_Stock
    Where Stock_Code in (Select distinct 제품코드
       From ACC_특정관리)
     and (stock_date between '2008/01/01' and '2008/12/31')
     and stock_kind in ('50', '51', '53', '54', '55', '56', '57')
    Group by stock_code
   ) a
   Group by stock_code
  ) a
 
  Union ALL
 
  -- 판매
  Select stock_code, 수불총계 = 0,
   sum(case when stock_kind in ('10', '19', '20') then stock_qty else 0 end) as 판매총계,
   sum(case when left(tax_date, 7) = '2008/01' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty1,
   sum(case when left(tax_date, 7) = '2008/02' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty2,
   sum(case when left(tax_date, 7) = '2008/03' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty3,
   sum(case when left(tax_date, 7) = '2008/04' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty4,
   sum(case when left(tax_date, 7) = '2008/05' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty5,
   sum(case when left(tax_date, 7) = '2008/06' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty6,
   sum(case when left(tax_date, 7) = '2008/07' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty7,
   sum(case when left(tax_date, 7) = '2008/08' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty8,
   sum(case when left(tax_date, 7) = '2008/09' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty9,
   sum(case when left(tax_date, 7) = '2008/10' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty10,
   sum(case when left(tax_date, 7) = '2008/11' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty11,
   sum(case when left(tax_date, 7) = '2008/12' and stock_kind in ('10', '19', '20') then stock_qty else 0 end) as oqty12
 
  From Product_Stock
  Where Stock_Code in (Select distinct 제품코드
     From ACC_특정관리)
   and (stock_date between '2007/10/01' and '2009/03/31')
   and (tax_date between '2008/01/01' and '2008/12/31')
   and stock_kind in ('10', '19', '20')
  Group by stock_code
 ) a
 Group by Stock_code
) a
Group by stock_code

---

으흠.. 조언 부탁드립니다.. ^^;

위의 값들은 예시.. 이예요..ㅋㅋ