WITH CBIR_ADP_CHLR_AMT_CODEBOOK AS ( SELECT ROWNUM AS HLR_NO ,DECODE(ROWNUM, 1, 300000 -- 첫째 30만원 , 2, 500000 -- 둘째 50만원 , 3, 700000 -- 셋째 70만원 , 700000 -- 셋째이상 70만원 ) AS CHLR_NO_AMT FROM DUAL CONNECT BY LEVEL <= 100 -- 100명까지... ) SELECT A.* ,( SELECT SUM(CHLR_NO_AMT) FROM CBIR_ADP_CHLR_AMT_CODEBOOK Z WHERE CHLR_NO BETWEEN A.CHAR_CNT - A.CBIR_ADP_CHLR_CNT + 1 AND A.CHLR_CNT ) AS CBIR_ADP_CHLR_TAX_DUTN_AMT FROM ( SELECT 2 AS CHLR_CNT /* 자녀수_세액공제 */ ,2 AS CBIR_ADP_CHLR_CNT /* 출산입양자녀수 */ FROM DUAL ) A
일단은... 이렇게 해봤어요... 엑셀에서 맞춰보니 맞는듯 헌디...
WITH t AS ( SELECT 2 chlr_cnt -- 자녀수 , 2 chlr_cnt_6 -- 6세이하자녀수 , 2 cbir_adp_chlr_cnt -- 출산입양자녀수 , 1 cbir_adp_chlr_seq -- 출산입양자녀-순서 FROM dual UNION ALL SELECT 3, 2, 1, 3 FROM dual UNION ALL SELECT 4, 4, 3, 2 FROM dual ) SELECT chlr_cnt , chlr_cnt_6 , cbir_adp_chlr_cnt , cbir_adp_chlr_seq , chlr_cnt * 15 + GREATEST(chlr_cnt - 2, 0) * 15 v1 -- 자녀-세액공제 , GREATEST(chlr_cnt_6 - 1, 0) * 15 v2 -- 6세이하자녀-세액공제 , cbir_adp_chlr_cnt * 70 - CASE WHEN cbir_adp_chlr_seq = 1 THEN 40 ELSE 0 END -- 첫째(70-40=30) - CASE WHEN cbir_adp_chlr_seq = 2 THEN 20 WHEN cbir_adp_chlr_seq = 1 AND cbir_adp_chlr_cnt > 1 THEN 20 ELSE 0 END -- 둘째(70-20=50) AS v3 -- 출산입양자녀-세액공제 FROM t ;