현 A 테이블( C_CUSTINFO ) 고객사 코드, 이름

C_NAME, C_CODE 가 있습니다.

B(C_GOODS) 테이블엔 금액 정보(기타 여러가지)가 있습니다.

연결 필드 Y_CODE  금액 필드는 Y_AMT  입니다.

Y_DATE 는 등록날짜 DATETIME  으로 2014-01-01 형식으로 저장되어 있습니다.

저장된 정보가 있던 없던 모두 결과 값을 뿌려주기 위해서 LEFT OUTER JOIN 했습니다.

C(PAYMENT)  테이블엔

연결 필드 W_CODE 금액 필드는 AMT  입니다.

W_DATE 는 조회날짜 DATETIME  으로 2014-01-01 형식으로 저장되어 있습니다.

이것도 마찬가지로 정보가 있던 없던 값을 뿌려주기 위해서  LEFT OUTER JOIN 했습니다.

쿼리는 아래와 같습니다.

문제는 C(PAYMENT) 테이블 금액이  실제 금액 * 5  가 되어서 보여 집니다.

그래서 조인을 B 테이블을 빼고 해보면 정상적으로 나옵니다.

B 테이블,  C 테이블 정보를 모두 가져와야  하는데  * 5 로 금액이 나와서 문제 입니다.

SELECT MAX(A.c_name) c_name, A.c_code,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='1' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS JAN,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='2' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS FEB,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='3' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS MAR,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='4' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS APR,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='5' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS MAY,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='6' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS JUN,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='7' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS JUL,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='8' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS AUG,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='9' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS SEP,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='10' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS OCT,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='11' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS NOV,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,y_date)='12' and DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS DEC,
ISNULL(SUM(CASE WHEN DATEPART(yy,y_date)='2014' THEN y_Amt END),0) AS TOTAL,
-- 여기 아래 부분 금액 계산이 실제 금액 * 5 로 나옵니다.
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='1' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_JAN,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='2' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_FEB,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='3' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_MAR,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='4' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_APR,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='5' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_MAY,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='6' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_JUN,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='7' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_JUL,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='8' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_AUG,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='9' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_SEP,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='10' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_OCT,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='11' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_NOV,
ISNULL(SUM(CASE WHEN DATEPART(MONTH,w_date)='12' and DATEPART(yy,w_date)='2014' THEN Amt END),0) AS S_DEC,
(select wday from payment where DATEPART(MONTH,w_date)='1' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [1월],
(select wday from payment where DATEPART(MONTH,w_date)='2' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [2월],
(select wday from payment where DATEPART(MONTH,w_date)='3' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [3월],
(select wday from payment where DATEPART(MONTH,w_date)='4' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [4월],
(select wday from payment where DATEPART(MONTH,w_date)='5' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [5월],
(select wday from payment where DATEPART(MONTH,w_date)='6' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [6월],
(select wday from payment where DATEPART(MONTH,w_date)='7' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [7월],
(select wday from payment where DATEPART(MONTH,w_date)='8' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [8월],
(select wday from payment where DATEPART(MONTH,w_date)='9' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [9월],
(select wday from payment where DATEPART(MONTH,w_date)='10' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [10월],
(select wday from payment where DATEPART(MONTH,w_date)='11' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [11월],
(select wday from payment where DATEPART(MONTH,w_date)='12' and DATEPART(yy,w_date)='2014' and w_code=a.c_code) AS [12월]
FROM C_CUSTINFO A LEFT OUTER JOIN pay_goods B ON A.c_code = B.y_code
left outer join payment c on a.c_code = c.w_code
GROUP BY A.c_code