database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41864 게시물 읽기
No. 41864
아래글(41858) 최초검사일 기준으로 표시되는 유효기간 수정 방법 문의 대한 추가 수정 문의
작성자
노진규(nojinkyu)
작성일
2021-08-03 16:14
조회수
2,112

내용설명

최초 검사일로부터 2018년 5월 1일을 기준으로 이전은 +3년, 이후는 +4년씩 년수를 더하는 쿼리입니다.

마농님께서 알려주신 방법대로 적용해서 작동하는데 성공했고,

다시 쿼리 수정을 할려고 하는데 잘 안되는군요.


검사일자에 따라 +3~4년씩 유효기간 년수가 더해지다가 중간에 변경검사가 있으면 변경검사의 년월일부터 다시 (초기화)계산되어 +3~4년이 더해지도록 수정해야 합니다.(이때도 2018년 5월 1일 전후 쿼리는 적용되어야 함)

 = 변경검사가 2018년 03월 13일이므로 (2018년 5월 1일 이전)  원하는 결과값은 2021년 03월 13일이 나와야 합니다.

변경검사시 INSKND의 값이 12입니다. (아래 표 노란색 셀)값이 12일 경우 날짜가 변경되는 것 까지는 성공했는데, 잘 안되는군요. 

그래서 아래 표에서 보는 것과 같이 원하는 결과값대로 나와야 합니다.


요약

검사일로부터 유효기간이 +3년씩 더해지다가 2018년 05월 01일 이후부터는 +4년씩 년수가 더해집니다.

하지만 중간에 변경검사가 있으면 변경검사의 년월일로 적용되어 유효기간이 변경검사일로부터 다시 계산되어야 합니다.


도움 요청 드립니다.

감사합니다.


아래 쿼리의 결과

  ROW_NUM RLTSEQ SUCNUM APCSEQ INSKND INSKND_1 MNFTNM SERIAL EQSTCD LGTSTD LGTCOR 검사일자 유효기간 변경검사부터다시날짜계산 원하는결과값 비고
1 5 11221 21F054 10008433 11 정기검사 NH 11231 10A201 100mm 검정색 2021년 04월 29일 2028년 07월 13일 (null) 2025년 03월 13일 변경검사 이후 +4년
2 4 80511 18C001 10006142 12 변경검사 NH 11231 10A201 100mm 검정색 2018년 03월 13일 2024년 07월 13일 2021년 03월 13일 2021년 03월 13일 변경검사의 년월일부터 다시 계산
3 3 72877 17F152 10005883 11 정기검사 NH 11231 10A201 50mm 검정색 2017년 10월 20일 2020년 07월 13일 (null) 2020년 07월 13일 +3년
4 2 42489 14F117 10003259 11 정기검사 NH 11231 10A201 50mm 검정색 2014년 10월 10일 2017년 07월 13일 (null) 2017년 07월 13일 +3년
5 1 11451 11B059 10000978 10 사용전검사 NH 11231 10A201 50mm 검정색 2011년 07월 13일 2014년 07월 13일 (null) 2014년 07월 13일 최초검사일


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

쿼리 내용

SELECT    ROW_NUMBER() OVER(ORDER BY RLTSEQ) ROW_NUM

          , NVL(A.RLTSEQ, '-')        AS RLTSEQ

          , NVL(A.SUCNUM, '-')      AS SUCNUM

          , NVL(A.APCSEQ, '-')      AS APCSEQ

          , NVL(A.INSKND, '-')      AS INSKND

          , NVL(GET_CODENAME('C405', A.INSKND), '-')      AS INSKND

          , NVL(A.MNFTNM, '-')      AS MNFTNM

          , NVL(A.SERIAL, '-')      AS SERIAL

          , NVL(A.EQSTCD, '-')      AS EQSTCD

          , NVL(GET_CODENAME('C408', A.LGTSTD), '-')      AS LGTSTD

          , NVL(A.LGTCOR, '-')      AS LGTCOR

          , A.INSDAT      AS 검사일자

          --, A.NXTDAT      AS 유효기간

          , TO_CHAR(ADD_MONTHS(dt, (rn * 3 + GREATEST(rn - x, 0)) * 12), 'yyyy"년" mm"월" dd"일"') AS 유효기간 --'yyyy"년" mm"월" dd"일"'

          --, TO_CHAR(ADD_MONTHS(dt1, (rn1 * 3 + GREATEST(rn1 - x1, 0)) * 12), 'YYYY-MM-DD') AS 유효기간3 --'yyyy"년" mm"월" dd"일"'

          ,변경검사부터다시날짜계산

 

  FROM    (

          SELECT    B.RLTSEQ                                AS RLTSEQ

                    , ROW_NUMBER() OVER(ORDER BY B.RLTSEQ) rn

                    , MIN(TO_DATE(A.INSDAT, 'yyyymmdd')) OVER() dt

                    , CEIL(MONTHS_BETWEEN(TO_DATE('20180501', 'yyyymmdd') , MIN(TO_DATE(A.INSDAT, 'yyyymmdd')) OVER()) / 36) x

                    , CASE WHEN C.INSKND = '12' THEN TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),36), 'yyyymmdd') END AS 변경검사부터다시날짜계산

                    , B.SUCNUM                              AS SUCNUM

                    , A.APCSEQ                              AS APCSEQ

                    , C.INSKND                              AS INSKND

                    , A.MNFTNM                              AS MNFTNM

                    , A.SERIAL                              AS SERIAL

                    , A.EQSTCD                               AS EQSTCD

                    , A.LGTSTD                              AS LGTSTD

                    , (SELECT    CODENM

                         FROM    COMCODET

                        WHERE    CODGRP = 'C208'

                          AND    CMCODE = A.LGTCOR)         AS LGTCOR

                    , TO_NUMBER(TO_CHAR(TO_DATE(A.INSDAT), 'YYYY')) || '년 '||

                      TO_NUMBER(TO_CHAR(TO_DATE(A.INSDAT), 'MM')) || '월 '||

                      TO_NUMBER(TO_CHAR(TO_DATE(A.INSDAT), 'DD')) || '일'  AS INSDAT

                 

                    , CASE WHEN TO_CHAR(TO_DATE(A.INSDAT), 'YYYY-MM-DD') >= '20180501' THEN

                      TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),48), 'YYYY') || '년 ' ||

                      TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),48), 'MM') || '월 ' ||

                      TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),48), 'DD') || '일 '

                      ELSE

                      TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),36), 'YYYY') || '년 ' ||

                      TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),36), 'MM') || '월 ' ||

                      TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),36), 'DD') || '일 '

                      END AS NXTDAT


           FROM    INSAPCDT A, INSRSLTT B, INSAPCMT C

           WHERE    A.LGTSEQ = B.LGTSEQ

             AND    A.APCSEQ = C.APCSEQ

             AND    B.INSRLT = '10'

             AND    A.SERIAL = '11231'

           UNION ALL

          SELECT    A.RLTSEQ       AS RLTSEQ

                    , ROW_NUMBER() OVER(ORDER BY A.RLTSEQ) rn

                    , MIN(TO_DATE(A.INSDAT, 'yyyymmdd')) OVER() dt

                    , CEIL(MONTHS_BETWEEN(TO_DATE('20180501', 'yyyymmdd') , MIN(TO_DATE(A.INSDAT, 'yyyymmdd')) OVER()) / 36) x

                    , CASE WHEN D.INSKND = '12' THEN TO_CHAR(ADD_MONTHS(TO_DATE(A.INSDAT),36), 'yyyymmdd') END AS 변경검사부터다시날짜계산

                    , A.SUCNUM     AS SUCNUM

                    , C.APCSEQ     AS APCSEQ

                    , D.INSKND     AS INSKND

                    , A.MNFTNM     AS MNFTNM

                    , A.SERIAL     AS SERIAL

                    , B.EQSTCD     AS EQSTCD

                    , A.STNDCD     AS STNDCD

                    , A.LGTCOR     AS LGTCOR

                    , A.INSDAT     AS INSDAT

                    , A.SUCDUR     AS NXTDAT

            FROM    OLDRSLTT A, INSRSLTT B, INSAPCDT C, INSAPCMT D

            WHERE   B.LGTSEQ = C.LGTSEQ AND C.APCSEQ = D.APCSEQ AND A.SERIAL = '11231'  AND A.RLTSEQ=B.RLTSEQ(+)

          ) A

ORDER BY A.RLTSEQ DESC;


 

이 글에 대한 댓글이 총 2건 있습니다.

지난번 질문에 답변을 달았었는데요.

지난번에도 UNION 사용에 대한 의문은 있었지만.

일단은 결과표만 보고 그걸 토대로 답변을 달았었습니다.

제가 드린 해법을 적용한 쿼리를 보면 잘못 적용한게 아닌가 의심이 되네요.

UNION 이후에 적용이 되어야 할 듯 한데?

UNION 이전에 적용을 하셨네요?

이게 맞는 적용인지 의문입니다.


다시 보니 결과표가 이상하네요.

rltseq 로 정렬되어 나와야 하는게 그렇지 않네요?

마농(manon94)님이 2021-08-03 16:56에 작성한 댓글입니다.
이 댓글은 2021-08-03 17:25에 마지막으로 수정되었습니다.
1. rltseq 정렬이 이상하여, 예시 자료 임의 변경하였습니다. 11221 -> 91221
2. 쿼리 해법은 union 이전이 아닌 union 이후 결과집합에 적용합니다.
3. 원본 쿼리에 불필요 부분 삭제 필요(예 : insdat 가공 부분, NVL 처리 부분)

WITH t AS
(
SELECT 91221 rltseq, '11' insknd, '11231' serial, '20210429' insdat FROM dual
UNION ALL SELECT 80511, '12', '11231', '20180313' FROM dual
UNION ALL SELECT 72877, '11', '11231', '20171020' FROM dual
UNION ALL SELECT 42489, '11', '11231', '20141010' FROM dual
UNION ALL SELECT 11451, '10', '11231', '20110713' FROM dual
)
SELECT row_num
     , rltseq
     , insknd
     , serial
     , TO_CHAR(insdat, 'yyyy"년" mm"월" dd"일"') insdat
     , TO_CHAR(
       ADD_MONTHS(dt, (rn * 3 + GREATEST(rn - x, 0)) * 12)
       , 'yyyy"년" mm"월" dd"일"') nxtdat
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY rltseq) row_num
             , ROW_NUMBER() OVER(PARTITION BY grp ORDER BY rltseq) rn
             , rltseq
             , insknd
             , serial
             , TO_DATE(insdat, 'yyyymmdd') insdat
             , MIN(TO_DATE(insdat, 'yyyymmdd')) OVER(PARTITION BY grp) dt
             , CEIL(MONTHS_BETWEEN(TO_DATE('20180501', 'yyyymmdd')
               , MIN(TO_DATE(insdat, 'yyyymmdd')) OVER(PARTITION BY grp))
               / 36) x
          FROM (SELECT rltseq, insknd, serial, insdat
                     , COUNT(CASE WHEN insknd IN ('10', '12') THEN 1 END)
                       OVER(ORDER BY rltseq) grp
                  FROM t
                 WHERE serial = '11231'
                )
        )
 ORDER BY rltseq DESC
;

 
마농(manon94)님이 2021-08-04 13:59에 작성한 댓글입니다.
이 댓글은 2021-08-04 14:01에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41868서브쿼리 결과 값이 왜 where 절 안에서 왜 안 먹을까요??? [4]
봉달이
2021-09-07
2157
41866조건값의 일부분만 가진 행 표시하기 [2]
궁금
2021-08-09
2257
41865그룹별 순번 만들기... [1]
궁금이
2021-08-04
2274
41864아래글(41858) 최초검사일 기준으로 표시되는 유효기간 수정 방법 문의 대한 추가 수정 문의 [2]
노진규
2021-08-03
2112
41863WITH 임시테이블로 여러 count 조회시 질문드려요.
비건
2021-07-31
2179
41862count 여러개 조회할때 질문드립니다.
비건
2021-07-31
2128
41859접속 모듈 OraPgm
궁금이
2021-07-23
1994
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다