꿈꾸는 개발자, DBA 커뮤니티 구루비

전주 값을 가져오기

by 잭키올 [2017.07.24 10:32:10]

11.PNG (47,199Bytes)

SELECT '20170701' AS CALD_DT, '201726' AS YYYY_WK_CD, 'SAT' AS DOW_CD, '7' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170702' AS CALD_DT, '201727' AS YYYY_WK_CD, 'SUN' AS DOW_CD, '1' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170703' AS CALD_DT, '201727' AS YYYY_WK_CD, 'MON' AS DOW_CD, '2' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170704' AS CALD_DT, '201727' AS YYYY_WK_CD, 'TUE' AS DOW_CD, '3' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170705' AS CALD_DT, '201727' AS YYYY_WK_CD, 'WED' AS DOW_CD, '4' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170706' AS CALD_DT, '201727' AS YYYY_WK_CD, 'THU' AS DOW_CD, '5' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170707' AS CALD_DT, '201727' AS YYYY_WK_CD, 'FRI' AS DOW_CD, '6' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170708' AS CALD_DT, '201727' AS YYYY_WK_CD, 'SAT' AS DOW_CD, '7' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170709' AS CALD_DT, '201728' AS YYYY_WK_CD, 'SUN' AS DOW_CD, '1' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170710' AS CALD_DT, '201728' AS YYYY_WK_CD, 'MON' AS DOW_CD, '2' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170711' AS CALD_DT, '201728' AS YYYY_WK_CD, 'TUE' AS DOW_CD, '3' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170712' AS CALD_DT, '201728' AS YYYY_WK_CD, 'WED' AS DOW_CD, '4' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170713' AS CALD_DT, '201728' AS YYYY_WK_CD, 'THU' AS DOW_CD, '5' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170714' AS CALD_DT, '201728' AS YYYY_WK_CD, 'FRI' AS DOW_CD, '6' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170715' AS CALD_DT, '201728' AS YYYY_WK_CD, 'SAT' AS DOW_CD, '7' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170716' AS CALD_DT, '201729' AS YYYY_WK_CD, 'SUN' AS DOW_CD, '1' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170717' AS CALD_DT, '201729' AS YYYY_WK_CD, 'MON' AS DOW_CD, '2' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170718' AS CALD_DT, '201729' AS YYYY_WK_CD, 'TUE' AS DOW_CD, '3' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170719' AS CALD_DT, '201729' AS YYYY_WK_CD, 'WED' AS DOW_CD, '4' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170720' AS CALD_DT, '201729' AS YYYY_WK_CD, 'THU' AS DOW_CD, '5' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170721' AS CALD_DT, '201729' AS YYYY_WK_CD, 'FRI' AS DOW_CD, '6' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170722' AS CALD_DT, '201729' AS YYYY_WK_CD, 'SAT' AS DOW_CD, '7' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170723' AS CALD_DT, '201730' AS YYYY_WK_CD, 'SUN' AS DOW_CD, '1' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170724' AS CALD_DT, '201730' AS YYYY_WK_CD, 'MON' AS DOW_CD, '2' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170725' AS CALD_DT, '201730' AS YYYY_WK_CD, 'TUE' AS DOW_CD, '3' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170726' AS CALD_DT, '201730' AS YYYY_WK_CD, 'WED' AS DOW_CD, '4' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170727' AS CALD_DT, '201730' AS YYYY_WK_CD, 'THU' AS DOW_CD, '5' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170728' AS CALD_DT, '201730' AS YYYY_WK_CD, 'FRI' AS DOW_CD, '6' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170729' AS CALD_DT, '201730' AS YYYY_WK_CD, 'SAT' AS DOW_CD, '7' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170730' AS CALD_DT, '201731' AS YYYY_WK_CD, 'SUN' AS DOW_CD, '1' AS DOW_SEQ FROM DUAL UNION ALL
SELECT '20170731' AS CALD_DT, '201731' AS YYYY_WK_CD, 'MON' AS DOW_CD, '2' AS DOW_SEQ FROM DUAL

버전 정보의 아이디 채번 값은 이렇습니다.
채번 룰 : 모듈_YYYYWK_SEQ
EX) SP_201730_001

VRSN_ID 가 전주의 Confirm 된 정보를 가져오려고 합니다.
데이터베이스 자체 함수를 이용하여 구하는 방법이 있을까요? ( 있다면, 쿼리 부탁드립니다 )

* 추가 질문
날짜 테이블을 사용해서 계속 조인하여 사용하는 방법이 좋을까요? 자체 함수 이용하는 것이 좋을까요?
(서브쿼리가 은근히 길어질꺼 같아 피할려고 생각 중이라서 고민이 되네요)

by 마농 [2017.07.24 11:07:41]

입력 대비 또는 원본 대비 원하는 결과표를 보여 주세요.
주차의 기준은 어찌 되나요?
오늘날짜 기준으로 주차를 구하는 계산식이 존재하나요?

by 잭키올 [2017.07.24 11:17:13]

원하는 결과는
당일 일자 기준(2017년 7월 24일)으로 전주 버전의 최종 확정 버전을 구하려고 합니다.
주차는 Partial Week 을 사용하지 않고 일요일부터 월요일을 한주로 룰을 잡았습니다.

원본 대비 원하는 결과 이미지 저장하여 다시 업로드 하였습니다.
감사합니다.

by 마농 [2017.07.24 11:20:00]

년도가 겹치는 구간에서의 주차 산정 기준이 어찌 되는지?
따로 달력 테이블에 주차가 이미 지정이 되어 있는지?
달력 사용하지 않고 주차를 구하는 공식이 존재하는지?

by 잭키올 [2017.07.24 11:23:31]

달력 테이블이 따로 존재하여 주차가 이미 지정되어 있습니다.

쿼리 첨부하였습니다.

by 마농 [2017.07.24 11:30:08]

SELECT b.*
  FROM 달력 a
     , 버전 b
 WHERE a.cald_dt = TO_CHAR(sysdate - 7, 'yyyymmdd')
   AND b.vrsn_id LIKE 'SP_' || a.yyyy_wk_cd || '%'
   AND b.prgs_sta_xd = 'CONFIRM'
;

by 잭키올 [2017.07.24 13:08:22]

감사합니다.
테이블을 사용하자니 불필요해보이는 SUB 쿼리가 많이 나오는거 같아
질문을 올렸는데.. 역시나 간결하군요..
ㅜㅜ
AND b.vrsn_id LIKE 'SP_' || a.yyyy_wk_cd || '%'
이 구문 생각을 못했네요..
감사합니다 (__)
좋은 하루 되세요!

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변