WITH m_data AS ( SELECT '001' 관리번호, '1000000' 청구금액, '1234567' 계좌, '20211005' 입금예정일, '202110' 청구년월, '1.5' 연체요율 FROM DUAL UNION ALL SELECT '002' 관리번호, '3000000' 청구금액, '1234568' 계좌, '20211005' 입금예정일, '202110' 청구년월, '1.5' 연체요율 FROM DUAL ), d_data AS ( SELECT '1234567' 계좌, '100000' 입금액, '20211010' 입금일시 FROM DUAL UNION ALL SELECT '1234567' 계좌, '300000' 입금액, '20211015' 입금일시 FROM DUAL UNION ALL SELECT '1234568' 계좌, '3000000' 입금액, '20211005' 입금일시 FROM DUAL ) SELECT * FROM m_data a ,d_data b WHERE a.계좌 = b.계좌 AND b.입금일시 >= a.입금예정일 AND b.입금일시 <= TO_CHAR(LAST_DAY('20211001'),'YYYYMMDD')
우선 예시로 위와 같은 데이터가 있으면
입금예정일이 지나면 연체료가 생겨야 하는데 002처럼 입금예정일에 맞게 입금을 하면 데이터가 안나와야 하고
001처럼 입금을 나눠서 하면 입금은 2번 했는데 데이터가 3개가 나와야 하는데 어떻게 해야할까요?? 조언 부탁드립니다...
관리번호 | 청구액 | 미수금 | 연체기간FROM | 연체기간TO | 연체일수 | 연체료 |
001 | 1000000 | 1000000 | 20211005 | 20211010 | 5 | 미수금 * 연체일수 * 연체요율 / 해당월에 전체일수(31) |
001 | 1000000 | 900000 | 20211010 | 20211015 | 5 | 미수금 * 연체일수 * 연체요율 / 해당월에 전체일수(31) |
001 | 1000000 | 600000 | 20211015 | 20211031 | 16 | 미수금 * 연체일수 * 연체요율 / 해당월에 전체일수(31) |
WITH m_data AS ( SELECT '001' 관리번호, '1000000' 청구금액, '1234567' 계좌, '20211005' 입금예정일, '202110' 청구년월, '1.5' 연체요율 FROM DUAL UNION ALL SELECT '002' 관리번호, '3000000' 청구금액, '1234568' 계좌, '20211005' 입금예정일, '202110' 청구년월, '1.5' 연체요율 FROM DUAL UNION ALL SELECT '003' 관리번호, '2000000' 청구금액, '1234570' 계좌, '20211205' 입금예정일, '202112' 청구년월, '1.5' 연체요율 FROM DUAL ), d_data AS ( SELECT '1234567' 계좌, '100000' 입금액, '20211010' 입금일시 FROM DUAL UNION ALL SELECT '1234567' 계좌, '300000' 입금액, '20211015' 입금일시 FROM DUAL UNION ALL SELECT '1234568' 계좌, '3000000' 입금액, '20211005' 입금일시 FROM DUAL UNION ALL SELECT '1234570' 계좌, '1200000' 입금액, '20211212' 입금일시 FROM DUAL ) SELECT 관리번호 , ( 청구금액 - SUM(입금액) OVER( PARTITION BY 관리번호 ORDER BY 입금일시 ) ) 미수금 , 연체기간FROM , 연체기간TO , 연체일수 , TRUNC( ( 청구금액 - SUM(입금액) OVER( PARTITION BY 관리번호 ORDER BY 입금일시 )) * 연체일수 * 연체요율 / 전체일수 , 0) 연체료 FROM ( SELECT A.관리번호 , B.입금일시 입금일시 , MIN(A.청구금액) 청구금액 , MIN(A.연체요율) 연체요율 , TO_NUMBER(TO_CHAR(LAST_DAY(MIN(A.입금예정일)), 'DD')) 전체일수 , NVL( LAG(MIN(B.입금액)) OVER(PARTITION BY A.관리번호 ORDER BY B.입금일시), 0) 입금액 , NVL( LAG(B.입금일시) OVER(PARTITION BY A.관리번호 ORDER BY B.입금일시), MIN(A.입금예정일)) 연체기간FROM , NVL( B.입금일시 , TO_CHAR(LAST_DAY(MIN(A.입금예정일)), 'YYYYMMDD') ) 연체기간TO , NVL( B.입금일시 , TO_CHAR(LAST_DAY(MIN(A.입금예정일)), 'YYYYMMDD') ) - NVL( LAG(B.입금일시) OVER(PARTITION BY A.관리번호 ORDER BY B.입금일시), MIN(A.입금예정일)) 연체일수 FROM m_data A, d_data B WHERE A.계좌 = B.계좌 AND B.입금일시 > A.입금예정일 AND b.입금일시 <= TO_CHAR(LAST_DAY(A.입금예정일),'YYYYMMDD') GROUP BY ROLLUP( A.관리번호, b.입금일시 ) ) AB WHERE 관리번호 IS NOT NULL
일단, 간단하게 만들어 봤는데.
1달 이상 연체할 경우엔 달별로 나누어 연체료를 계산해야 할 것 같네요.
일단, 이 쿼리는 달별로 나누는 부분이 적용되지 않은 쿼리입니다.
WITH m_data AS ( SELECT '001' 관리번호, 1000000 청구금액, '1234567' 계좌, '20211005' 입금예정일, '202110' 청구년월, 1.5 연체요율 FROM dual UNION ALL SELECT '002', 3000000, '1234568', '20211005', '202110', 1.5 FROM dual ) , d_data AS ( SELECT '1234567' 계좌, 100000 입금액, '20211010' 입금일시 FROM dual UNION ALL SELECT '1234567', 300000, '20211015' FROM dual UNION ALL SELECT '1234568', 3000000, '20211005' FROM dual ) SELECT 관리번호 , 청구금액 , 계좌 , 입금예정일 , 청구년월 , 연체요율 , 입금액 , 미수금 , 연체기간FROM , 연체기간TO , TO_DATE(연체기간TO, 'yyyymmdd') - TO_DATE(연체기간FROM, 'yyyymmdd') 연체일 , TRUNC( 미수금 * 연체요율 / 100 * (TO_DATE(연체기간TO, 'yyyymmdd') - TO_DATE(연체기간FROM, 'yyyymmdd')) / TO_CHAR(LAST_DAY(TO_DATE(연체기간TO, 'yyyymmdd')), 'dd') ) 연체료 FROM (SELECT a.관리번호 , a.청구금액 , a.계좌 , a.입금예정일 , a.청구년월 , a.연체요율 , b.입금액 , a.청구금액 - SUM(b.입금액) OVER(PARTITION BY a.관리번호 ORDER BY b.입금일시) + b.입금액 미수금 , LAG(b.입금일시, 1, a.입금예정일) OVER(PARTITION BY a.관리번호 ORDER BY b.입금일시) 연체기간FROM , b.입금일시 연체기간TO FROM m_data a , d_data b WHERE a.계좌 = b.계좌 AND b.입금일시 >= a.입금예정일 AND b.입금일시 <= TO_CHAR(LAST_DAY(TO_DATE('20211001', 'yyyymmdd')), 'yyyymmdd') ) ;
질문을 다시 자세히 보니
연체료는 청구월의 말일까지만 계산하면 되는 걸로 보이네요.
WITH m_data AS ( SELECT '001' 관리번호, 1000000 청구금액, '1234567' 계좌, '20211005' 입금예정일, '202110' 청구년월, 1.5 연체요율 FROM dual UNION ALL SELECT '002', 3000000, '1234568', '20211005', '202110', 1.5 FROM dual UNION ALL SELECT '003', 3000000, '1234569', '20211005', '202110', 1.5 FROM dual ) , d_data AS ( SELECT '1234567' 계좌, 100000 입금액, '20211010' 입금일시 FROM dual UNION ALL SELECT '1234567', 300000, '20211015' FROM dual UNION ALL SELECT '1234568', 3000000, '20211005' FROM dual UNION ALL SELECT '1234569', 2000000, '20211031' FROM dual ) SELECT 관리번호 , 청구금액 , 계좌 , TO_CHAR(입금예정일, 'yyyymmdd') 입금예정일 , 청구년월 , 연체요율 , 입금액 , 청구금액 - SUM(입금액) OVER(PARTITION BY 관리번호 ORDER BY 입금일) + 입금액 미수금 , TO_CHAR(LAG(입금일, 1, 입금예정일) OVER(PARTITION BY 관리번호 ORDER BY 입금일), 'yyyymmdd') 연체기간FROM , TO_CHAR(입금일, 'yyyymmdd') 연체기간TO , 입금일 - LAG(입금일, 1, 입금예정일) OVER(PARTITION BY 관리번호 ORDER BY 입금일) 연체일 , TRUNC( (청구금액 - SUM(입금액) OVER(PARTITION BY 관리번호 ORDER BY 입금일) + 입금액) * 연체요율 / 100 * (입금일 - LAG(입금일, 1, 입금예정일) OVER(PARTITION BY 관리번호 ORDER BY 입금일)) / TO_CHAR(LAST_DAY(입금일), 'dd') ) 연체료 FROM (SELECT a.관리번호 , a.청구금액 , a.계좌 , TO_DATE(a.입금예정일, 'yyyymmdd') 입금예정일 , a.청구년월 , a.연체요율 , NVL(TO_DATE(b.입금일시, 'yyyymmdd'), LAST_DAY(TO_DATE(a.청구년월, 'yyyymm'))) 입금일 , DECODE(GROUPING(b.입금일시), 0, SUM(b.입금액), 0) 입금액 FROM m_data a , d_data b WHERE a.계좌 = b.계좌 AND a.청구년월 = '202110' AND b.입금일시 >= a.입금예정일 AND b.입금일시 <= TO_CHAR(LAST_DAY(TO_DATE(a.청구년월, 'yyyymm')), 'yyyymmdd') GROUP BY a.관리번호, a.청구금액, a.계좌, a.입금예정일, a.청구년월, a.연체요율 , ROLLUP (b.입금일시) HAVING GROUPING(b.입금일시) = 0 OR ( MAX(b.입금일시) != TO_CHAR(LAST_DAY(TO_DATE(a.청구년월, 'yyyymm')), 'yyyymmdd') AND SUM(b.입금액) != a.청구금액 ) ) ;