안녕하세요. 시간에 쫓기다 보니 어떻게 해야 할지 생각도 안나네요..ㅠㅠ
다른 부분을 해결을 어느정도 했는데...이부분을 할 수가 없네요.
with TEST AS (
SELECT 'A1000901' AS CNO,'20170925' AS NTC_DT, '101' AS SEQ, '0' AS PRCPL_AMT, '130' AS INT_AMT, '201704' AS TARGET_YM, '300' AS NTC_TYPE_CD FROM DUAL
UNION ALL
SELECT 'A1000901' AS CNO,'20170925' AS NTC_DT, '102' AS SEQ, '0' AS PRCPL_AMT, '110' AS INT_AMT, '201705' AS TARGET_YM, '300' AS NTC_TYPE_CD FROM DUAL
UNION ALL
SELECT 'A1000901' AS CNO,'20170925' AS NTC_DT, '103' AS SEQ, '0' AS PRCPL_AMT, '300' AS INT_AMT, '201708' AS TARGET_YM, '300' AS NTC_TYPE_CD FROM DUAL
UNION ALL
SELECT 'A1000901' AS CNO,'20170925' AS NTC_DT, '104' AS SEQ, '109200' AS PRCPL_AMT, '109200' AS INT_AMT, '201709' AS TARGET_YM, '100' AS NTC_TYPE_CD FROM DUAL
)
SELECT CNO, NTC_DT, SEQ, PRCPL_AMT, INT_AMT, TARGET_YM, NTC_TYPE_CD
FROM TEST;
==> 결과를 내고 싶은 결과는 /.. 2개 테이블(HAPSAN_T, HAPSAN_DETAIL) 에 SELECT를 해서 INSERT를 할 것인데요..
== MAX(TARGET_YM)
== 최종월고지에 INT_AMT 140원이하 2건을 합산하여
[HAPSAN_T]
CNO NTC_DT SEQ_NO PRCPL_AMT INT_AMT CMT TARGET_YM
A1000901 20170925 103 0 300 일반 201708
A1000901 20170925 104 109200 109440 합산2건 201709
[HAPSAN_DETAIL]
CNO NTC_DT SEQ_NO PRCPL_AMT INT_AMT TARGET_YM NTC_TYPE_CD
A1000901 20170925 101 0 130 201704 300
A1000901 20170925 102 0 110 201705 300
A1000901 20170925 104 109200 109200 201709 100
도와주세요.. 쿼리는 어떻게 하면 진심 실력이 늘까요?
|