위의 예시 데이터가 어떤 테이블의 데이터인지요?

쿼리상에 들어가있는 3가지 테이블들의

샘플 데이터도 같이 주시면 좋을듯하네요.

tbl_sumsale

tbl_metrial

tbl_house

tbl_sumsale = 입출고테이블

tbl_metrial =  상품테이블

tbl_house = 매장테이블

tbl_sumsale 데이터

met_id       house_id      kind_id     inout_day       in_qty          out_qty

7013          001                 00           20150101           0                    5

7013          001                 00           20151231           0                    5

7013          001                 00           20160101           0                   5

이렇게 테이터가 있습니다

참고만 하세요..

---쿼리시작---

with tbl_sumsale(met_id,house_id,kind_id,inout_day,in_qty,out_qty) As
(
      Select '7013','001','00','20150101',0,5 Union All
      Select '7013','001','00','20151231',0,5 Union All
      Select '7013','001','00','20160101',0,5
)
,tbl_metrial(met_id,met_name,sale_price,p_chk) As
(
      Select '7013','테스트',1000,'Y'
)
,tbl_house(house_id,house_name) As
(
      Select '001','테스트지점'
)
                 
      Select A.met_id
            ,LEFT(A.inout_day,4) As dd_tt
            ,B.met_name
            ,B.sale_price
            ,A.house_id
            ,C.house_name
            ,SUM(A.out_qty)  As QTY
        From tbl_sumsale A Inner Join tbl_metrial B On A.met_id = B.met_id
                           Inner Join tbl_house C On A.house_id = C.house_id
       Where A.inout_day like '%' +'2015' +'%'                                                               
         And B.p_chk ='Y'
    Group By A.met_id
            ,Substring(A.inout_day, 1,4)
            ,B.met_name
            ,B.sale_price
            ,A.house_id
            ,C.house_name
    Order By A.met_id Asc
            ,A.house_id Asc                                                                
---쿼리끝---

참고로 그 값이 그리 나왔던 이유는

QTY 를 Sub Query 로 작성하셨는데

거기 내부에 일자조건이 빠져있었습니다..

   (select case when sum(out_qty) is null then 0 else sum(out_qty) end      
      from tbl_sumsale A5
      where A5. kind_id ='00'
         and A5. house_id = A. house_id

         and A5.inout_day = A.inout_day     -- 기존 쿼리에서 요 Line만 추가해도 됨..
         and A5. met_id = A. met_id  ) as QTY

날짜 검색 조건이 left(A. inout_day, 4 ) 인데   A5. inout_day = A. inout_day 로 작성하니깐 안됩니다...

그렇게 해서 실행하니  다음처럼 에러가 발생 하네요..

서버: 메시지 8624, 수준 16, 상태 21, 줄 1
내부 SQL Server 오류입니다.

혹시 버전문제인가요...server 2000 인데

2000이라고 오류날 구문은 아닙니다..

혹시 with 구문 그대로 사용하신거면 오류가 나실거구요 -,-;;;

2가지 경우를 다 입력할께요..

1) 작성자 님이 사용한 쿼리 수정

---쿼리시작---

     Select Distinct(A.met_id)
           ,substring(A.inout_day, 1,4) As dd_tt
           ,B.met_name
           ,B.sale_price
           ,A.house_id
           ,C.house_name
           ,(
                Select
                       (
                          Case When Sum(out_qty) Is Null
                                  Then 0
                               Else Sum(out_qty)
                          End      
                       )
                  From tbl_sumsale A5
                 Where A5.kind_id ='00'
                   And A5.house_id = A.house_id
                   And A5.met_id = A.met_id 
                   And Substring(A5.inout_day, 1,4) = SubString(A.inout_day,1,4)
            ) 
                         As QTY     
        From tbl_sumsale A
            ,tbl_metrial B
            ,tbl_house C
       Where A.met_id = B.met_id
         And A.house_id = C.house_id
         And A.inout_day like '%' +'2015' +'%'
         And B.p_chk ='Y'
    Group By A.met_id
            ,Substring(A.inout_day, 1,4)
            ,B.met_name
            ,B.sale_price
            ,A.house_id
            ,C.house_name
    Order By A.met_id asc
            ,A.house_id asc
---쿼리끝---

2) 제가 작성했던 쿼리

---쿼리시작---

      Select A.met_id
            ,SubString(A.inout_day,1,4) As dd_tt
            ,B.met_name
            ,B.sale_price
            ,A.house_id
            ,C.house_name
            ,SUM(A.out_qty)  As QTY
        From tbl_sumsale A Inner Join tbl_metrial B On A.met_id = B.met_id
                           Inner Join tbl_house C On A.house_id = C.house_id
       Where A.inout_day like '%' +'2015' +'%'                                                               
         And B.p_chk ='Y'
    Group By A.met_id
            ,Substring(A.inout_day, 1,4)
            ,B.met_name
            ,B.sale_price
            ,A.house_id
            ,C.house_name
    Order By A.met_id Asc
            ,A.house_id Asc                        

---쿼리끝---