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 41876 게시물 읽기
No. 41876
아래글(30820) 최초검사일 기준으로 표시되는 유효기간 수정 방법 문의 대한 추가 수정 문의 #3
작성자
노진규(nojinkyu)
작성일
2021-11-10 15:38
조회수
256

내용설명

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

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

쿼리에 기능을 추가해서 수정을 할려고 하는데 다시 알려주신대로 적용해도 잘 안되는군요.

아래 쿼리는 현재 적용 사용중인 완전한 쿼리인데, 여기서 무엇을 어떻게 변경해야 하는지 모르겠습니다.

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

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

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

변경검사시 INSKND의 값이 12입니다. (아래 표 노란색 셀)값이 12일 경우 처음 알려주신거 날짜가 변경되는 것 까지는 성공했는데, 기능을 추가하는것은 잘 안되는군요. 


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

UNION 전후를 같이 적용한 이유는 알려주신대로 UNION 후에만 적용하니까 오류가 발생해서입니다. UNION 전후에 같이 적용하니까 원하는대로 날짜변경까지는 잘 됩니다.

 = 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다.


요약

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

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

감사힙니다.


아래 쿼리의 결과

현재쿼리의 결과                            
순번 ROW_NUM RLTSEQ SUCNUM APCSEQ INSKND INSKND_1 MNFTNM MNFTNO EQSTCD LGTSTD LGTCOR 검사일자 유효기간    
1 5 11221 21F054 10008433 11 정기검사 엔지니어링 NM-1125018 11L2500018 100mm 검정색 2021년 04월 29일 2028년 07월 13일    
2 4 80511 18C001 10006142 12 변경검사 엔지니어링 NM-1125018 11L2500018 100mm 검정색 2018년 03월 13일 2024년 07월 13일    
3 3 72877 17F152 10005883 11 정기검사 엔지니어링 NM-1125018 11L2500018 50mm 검정색 2017년 10월 20일 2020년 07월 13일    
4 2 42489 14F117 10003259 11 정기검사 엔지니어링 NM-1125018 11L2500018 50mm 검정색 2014년 10월 10일 2017년 07월 13일    
5 1 11451 11B059 10000978 10 사용전검사 엔지니어링 NM-1125018 11L2500018 50mm 검정색 2011년 07월 13일 2014년 07월 13일    
                               
원하는결과                            
순번 ROW_NUM RLTSEQ SUCNUM APCSEQ INSKND INSKND_1 MNFTNM MNFTNO EQSTCD LGTSTD LGTCOR 검사일자 유효기간 원하는결과값 비고
1 5 11221 21F054 10008433 11 정기검사 엔지니어링 NM-1125018 11L2500018 100mm 검정색 2021년 04월 29일 2028년 07월 13일 2025년 03월 13일 변경검사 이후 +4년
2 4 80511 18C001 10006142 12 변경검사 엔지니어링 NM-1125018 11L2500018 100mm 검정색 2018년 03월 13일 2024년 07월 13일 2021년 03월 13일 변경검사의 년월일부터 다시 계산
3 3 72877 17F152 10005883 11 정기검사 엔지니어링 NM-1125018 11L2500018 50mm 검정색 2017년 10월 20일 2020년 07월 13일 2020년 07월 13일 +3년
4 2 42489 14F117 10003259 11 정기검사 엔지니어링 NM-1125018 11L2500018 50mm 검정색 2014년 10월 10일 2017년 07월 13일 2017년 07월 13일 +3년
5 1 11451 11B059 10000978 10 사용전검사 엔지니어링 NM-1125018 11L2500018 50mm 검정색 2011년 07월 13일 2014년 07월 13일 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.MNFTNO, '-')      AS MNFTNO

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

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

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

          , A.INSDAT                AS 검사일자

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

FROM    (SELECT    B.RLTSEQ                             AS RLTSEQ

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

                    , B.SUCNUM                            AS SUCNUM

                    , A.APCSEQ                            AS APCSEQ

                    , C.INSKND                            AS INSKND

                    , A.MNFTNM                            AS MNFTNM

                    , A.MNFTNO                            AS MNFTNO

                    , 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

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

                    , CEIL(MONTHS_BETWEEN(TO_DATE('20180501', 'yyyymmdd') 

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

           FROM    INSAPCDT A, INSRSLTT B, INSAPCMT C

           WHERE    A.LGTSEQ = B.LGTSEQ

             AND    A.APCSEQ = C.APCSEQ

             AND    B.INSRLT = '10'

             AND    A.MNFTNO = 'NM-1125018'

           UNION ALL

          SELECT    A.RLTSEQ       AS RLTSEQ

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

                    , A.SUCNUM     AS SUCNUM

                    , C.APCSEQ     AS APCSEQ

                    , D.INSKND     AS INSKND

                    , A.MNFTNM     AS MNFTNM

                    , A.MNFTNO     AS MNFTNO

                    , B.EQSTCD     AS EQSTCD

                    , A.STNDCD     AS STNDCD

                    , A.LGTCOR     AS LGTCOR

                    , A.INSDAT     AS INSDAT

                    , A.SUCDUR     AS NXTDAT

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

                    , CEIL(MONTHS_BETWEEN(TO_DATE('20180501', 'yyyymmdd') 

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

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

            WHERE   B.LGTSEQ = C.LGTSEQ AND C.APCSEQ = D.APCSEQ AND A.MNFTNO = 'NM-1125018'  AND A.RLTSEQ=B.RLTSEQ(+)

          ) A

 ORDER BY A.RLTSEQ DESC;


 

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

이미 지난번 댓글에서 언급 했었습니다.

제가 드린 해법에 대한 적용 위치가 틀렸습니다.

UNION 이전에 적용하는게 아니라 UNION 이후에 적용을 해야 합니다.

UNION 쿼리 각각의 rn 을 구하고 있는데

UNION 이후에 rn 을 새로 구해야 합니다.


그리고 제가 알려드린 방법과 많이 다르네요.

또한,

쿼리는 RLTSEQ 로 정렬하고 있는데 올려주신 결과는 RLTSEQ 로 정렬되어 있지 않네요?

마농(manon94)님이 2021-11-11 15:15에 작성한 댓글입니다.
이 댓글은 2021-11-11 15:30에 마지막으로 수정되었습니다.
SELECT rltseq
     , sucnum
     , apcseq
     , insknd
     , mnftnm
     , mnftno
     , eqstcd
     , lgtstd
     , lgtcor
     , 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
             , sucnum
             , apcseq
             , insknd
             , mnftnm
             , mnftno
             , eqstcd
             , lgtstd
             , lgtcor
             , insdat
             , MIN(TO_DATE(insdat, 'yyyymmdd')) OVER(PARTITION BY grp) dt
             , CEIL(MONTHS_BETWEEN( TO_DATE('20180501', 'yyyymmdd')
                                  , MIN(insdat) OVER(PARTITION BY grp)
                                  ) / 36) x
          FROM (SELECT rltseq
                     , sucnum
                     , apcseq
                     , get_codename('C405', insknd) insknd
                     , mnftnm
                     , mnftno
                     , eqstcd
                     , get_codename('C408', lgtstd) lgtstd
                     , get_codename('C208', lgtcor) lgtcor
                     , TO_DATE(insdat, 'yyyymmdd') insdat
                     , COUNT(CASE WHEN insknd IN ('10', '12') THEN 1 END)
                       OVER(ORDER BY rltseq) grp
                  FROM (SELECT b.rltseq
                             , b.sucnum
                             , a.apcseq
                             , c.insknd
                             , a.mnftnm
                             , a.mnftno
                             , a.eqstcd
                             , a.lgtstd
                             , a.lgtcor
                             , a.insdat
                          FROM insapcdt a
                             , insrsltt b
                             , insapcmt c
                         WHERE a.lgtseq = b.lgtseq
                           AND a.apcseq = c.apcseq
                           AND b.insrlt = '10'
                           AND a.mnftno = 'NM-1125018'
                         UNION ALL
                        SELECT a.rltseq
                             , a.sucnum
                             , c.apcseq
                             , d.insknd
                             , a.mnftnm
                             , a.mnftno
                             , b.eqstcd
                             , a.stndcd
                             , a.lgtcor
                             , a.insdat
                          FROM oldrsltt a
                             , insrsltt b
                             , insapcdt c
                             , insapcmt d
                         WHERE b.lgtseq = c.lgtseq
                           AND c.apcseq = d.apcseq
                           AND a.mnftno = 'NM-1125018'
                           AND a.rltseq = b.rltseq
                        ) a
                ) a
        ) a
 ORDER BY rltseq DESC
;
마농(manon94)님이 2021-11-11 17:24에 작성한 댓글입니다.
이 댓글은 2021-11-11 17:31에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41879oracle 설치 toad는 되는데 golden이 안되네요
김민유
2021-11-25
53
41878우분투에서 오라클 연동 강의나 자료 없을까요?
c린이
2021-11-22
96
41877중복제거 최신날짜 하나의 데이타만 [2]
뽀뽀뽀
2021-11-19
147
41876아래글(30820) 최초검사일 기준으로 표시되는 유효기간 수정 방법 문의 대한 추가 수정 문의 #3 [2]
노진규
2021-11-10
256
41875아카이브 생성량 조회 쿼리 질문이 있습니다.
신은정
2021-11-05
287
41874아래 구문이 이해가 안됩니다. [1]
박주호
2021-10-26
454
41872오라클 접속이 안됩니다. [6]
휘바람
2021-10-19
551
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.096초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다