데이터베이스 질문과 답변
WITH TEMP AS
(
SELECT A.COMP_CD,
SUBSTR(A.OUT_DT,0,6) AS YYMM,
B.USER_ID,
SUM(A.SUPPLY_AMT) AS SUM_SUPPLY_AMT
FROM SD_DEMAND A JOIN LM_USER B
ON A.COMP_CD = B.COMP_CD
AND A.SALES_ID = B.USER_ID
WHERE A.COMP_CD ='2300'
AND A.OUT_DT LIKE TO_CHAR(SYSDATE,'YYYYMM') || '%'
GROUP BY A.COMP_CD, SUBSTR(A.OUT_DT,0,6),B.USER_ID
UNION ALL
SELECT A.COMP_CD,
SUBSTR(A.OUT_DT,0,6) AS YYMM,
B.USER_ID,
SUM(A.SUPPLY_AMT) AS SUM_SUPPLY_AMT
FROM SD_DEMAND A JOIN LM_USER B
ON A.COMP_CD = B.COMP_CD
AND A.SALES_ID = B.USER_ID
WHERE A.COMP_CD ='2300'
AND A.OUT_DT LIKE TO_CHAR(SYSDATE-365,'YYYYMM') || '%'
GROUP BY A.COMP_CD, SUBSTR(A.OUT_DT,0,6),B.USER_ID
)
SELECT A.COMP_CD
, '당월' AS 처리
, A.YYMM
, A.USER_ID
, (SELECT USER_NM FROM LM_USER WHERE COMP_CD = A.COMP_CD AND USER_ID = A.USER_ID) AS USER_NM
, B.SUM_AIM_AMT AS 당월목표
, A.SUM_SUPPLY_AMT AS 당월실적
, ROUND((NVL(A.SUM_SUPPLY_AMT/NULLIF(B.SUM_AIM_AMT,0),1) )*100,1) AS 달성
FROM TEMP A
INNER JOIN(
SELECT A.COMP_CD,
A.YYMM,
B.USER_ID,
SUM(A.AIM_AMT) AS SUM_AIM_AMT
FROM IF_GET_SALE_PLAN A JOIN LM_USER B
ON A.COMP_CD = B.COMP_CD
AND UPPER(A.SALES_CD) = UPPER(B.USER_ID)
WHERE A.COMP_CD ='2300' -- 변수
AND A.YYMM = TO_CHAR(SYSDATE,'YYYYMM')
AND A.AIM_TP ='2'
GROUP BY A.COMP_CD, A.YYMM,B.USER_ID
UNION ALL
SELECT A.COMP_CD,
A.YYMM,
B.USER_ID,
SUM(A.AIM_AMT) AS SUM_AIM_AMT
FROM IF_GET_SALE_PLAN A JOIN LM_USER B
ON A.COMP_CD = B.COMP_CD
AND UPPER(A.SALES_CD) = UPPER(B.USER_ID)
WHERE A.COMP_CD ='2300' -- 변수
AND A.YYMM = TO_CHAR(SYSDATE-365,'YYYYMM')
AND A.AIM_TP ='2'
GROUP BY A.COMP_CD, A.YYMM,B.USER_ID
) B
ON A.COMP_CD = B.COMP_CD
AND A.YYMM = B.YYMM
AND A.USER_ID = B.USER_ID;
위에 쿼리를 아래 사진 이미지처럼 결과를 조회 할 수 있도록 쿼리를 수정하고 싶은데 어떻게 하면 될지 문의드립니다.
by 마농
[2024.08.09 09:40:56]
1. 조건중에
- AND UPPER(a.sales_cd) = UPPER(b.user_id)
- 조인 조건에 UPPER 쓴 이유가 있나요? 빼면 결과가 달라지는지?
2. 이름을 옆으로 나열하는 것은
- 정적쿼리에서는 이름의 수와 값을 알고 있는 상태에서만 가능합니다.
- 동적으로 구현하려면 동적쿼리로 만들어야 합니다.
3. 전년도 실적 조회에서
- 365 일을 빼는 방식은 윤년(366일)의 마지막날(12월31일)에 잘못된 데이터 조회됩니다.
by 통쓰
[2024.08.09 09:54:47]
엇 UPPER 는 필요없습니다. 똑같아요. 그래서 조금 아래와 같이 쿼리를 바꿨는데요!
이런식으로 동적쿼리로 만들면될까요 혹시 개선해야할부분 있으면 부탁드립니다!!
WITH t AS ( SELECT A.COMP_CD , SUBSTR(A.OUT_DT,0,6) AS YYMM , B.USER_ID , SUM(A.SUPPLY_AMT) AS SUM_SUPPLY_AMT , 1 AS SHIFT FROM SD_DEMAND A JOIN LM_USER B ON A.COMP_CD = B.COMP_CD AND A.SALES_ID = B.USER_ID WHERE A.COMP_CD ='2300' AND A.OUT_DT LIKE TO_CHAR(SYSDATE,'YYYYMM') || '%' GROUP BY A.COMP_CD, SUBSTR(A.OUT_DT,0,6),B.USER_ID UNION ALL SELECT A.COMP_CD , SUBSTR(A.OUT_DT,0,6) AS YYMM , B.USER_ID , SUM(A.SUPPLY_AMT) AS SUM_SUPPLY_AMT , 2 AS SHIFT FROM SD_DEMAND A JOIN LM_USER B ON A.COMP_CD = B.COMP_CD AND A.SALES_ID = B.USER_ID WHERE A.COMP_CD ='2300' AND A.OUT_DT LIKE TO_CHAR(ADD_MONTHS( SYSDATE,-12),'YYYYMM') || '%' GROUP BY A.COMP_CD, SUBSTR(A.OUT_DT,0,6),B.USER_ID ) SELECT * FROM (SELECT USER_ID , SHIFT , SUM_SUPPLY_AMT FROM t ) PIVOT (MIN(SUM_SUPPLY_AMT) FOR SHIFT IN (1 SHIFT_1, 2 SHIFT_2)) ;
by 마농
[2024.08.09 10:30:39]
WITH tmp AS
(
SELECT NVL(sales_id, 'tot') user_id
, SUM(a) || '' a
, SUM(b) || '' b
, ROUND(SUM(b) / SUM(a) * 100 , 2) || '%' c
, SUM(d) || '' d
, ROUND(SUM(b) / SUM(d) * 100 - 100, 2) || '%' e
FROM (SELECT sales_id
, SUM(aim_amt) a
, null b
, null d
FROM if_get_sale_plan
WHERE comp_cd = '2300'
AND aim_tp = '2'
AND yymm = TO_CHAR(sysdate, 'yyyymm')
GROUP BY sales_id
UNION ALL
SELECT sales_id
, null a
, SUM(DECODE(SUBSTR(out_dt, 1, 6), TO_CHAR(sysdate, 'yyyymm'), supply_amt, null)) b
, SUM(DECODE(SUBSTR(out_dt, 1, 6), TO_CHAR(sysdate, 'yyyymm'), null, supply_amt)) d
FROM sd_demand
WHERE comp_cd = '2300'
AND ( out_dt LIKE TO_CHAR(sysdate, 'yyyymm') || '%' OR
out_dt LIKE TO_CHAR(ADD_MONTHS(sysdate, -12), 'yyyymm') || '%' )
GROUP BY sales_id
)
GROUP BY ROLLUP(sales_id)
)
SELECT *
FROM tmp
UNPIVOT (v FOR 구분 IN ( a AS '목표'
, b AS '실적'
, c AS '달성률'
, d AS '전년'
, e AS '신장률'
) )
PIVOT (MIN(v) FOR user_id IN ( 'SJ230001' 김수태
, 'SJ230002' 김종현
, 'SJ230003' 최진연
, ... -- 이 부분을 동적쿼리로 구현
, 'tot' 합계
) )
ORDER BY INSTR('목표,실적,달성,전년,신장', 구분)
;
by 통쓰
[2024.08.09 11:18:36]
ㅠㅠㅠ마농님 너무 감사합니다... 열심히 노력해봐야겠네요...
) 버튼을 클릭하여 작성 하시면 됩니다.