초보개발자 쿼리 조언 부탁 드립니다 0 5 239

by 야비 [SQL Query] oracle [2022.01.14 16:07:33]


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)

 

by 뉴비디비 [2022.01.15 18:51:59]
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 

 


by 마농 [2022.01.17 09:03:25]

1. 관리번호에 대한 1회용 전용계좌번호 인가요?
- 그게 아니라면? 계좌번호로 조인하면 안되고,
- b 테이블에 관리번호가 있어서 관리번호로 조인해야 합니다.
2. b.입금일시 >= a.입금예정일 조건을 주고 있는데.
- 이렇게 하면 입금예정일 이전에 일부만 입금한 정보가 누락되게 됩니다.
3, LAST_DAY('20211001') 는 잘못된 사용법입니다.
- 명확하게 형변환을 해주셔야 합니다.
- LAST_DAY(TO_DATE('20211001', 'yyyymmdd'))


by 마농 [2022.01.17 09:53:58]

일단, 간단하게 만들어 봤는데.
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')
        )
;

 


by 마농 [2022.01.17 15:12:13]

질문을 다시 자세히 보니
연체료는 청구월의 말일까지만 계산하면 되는 걸로 보이네요.
 

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.청구금액 )
        )
;

 


by 야비 [2022.01.18 11:17:24]

답변 주셔서 정말 감사합니다!

DB는 정말 제가 모르는것 투성이라는 생각이 들어서 오늘부터 책이라도 하나 사서 공부해봐야겠습니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입