데이터베이스 질문과 답변
직원이 2명이 있습니다. (A와B)
A 직원은 2020년 1월 1일 ~ 1월 20일까지의 출/퇴근 기록이 존재합니다.(데이터 수 : 20rows)
B 직원은 2020년 1월 10일 ~ 1월 25일까지의 출/퇴근 기록이 존재합니다.(데이터 수 : 16rows)
직원은 수는 변경될 수 있습니다.
A와 B의 출근하지 않은 일자에 날짜를 채워주고 싶습니다.
(결과물)
일자 직원
2020.01.01 A
2020.01.02 A
생략
2020.01.31 A
2020.01.01 B
2020.01.02 B
생략
2020.01.31 B
(총 rows 수는 62개입니다.)
가능할까요?
by 우리집아찌
[2020.02.11 08:19:52]
샘플 데이터와 스키마를 올려주세요
by 마농
[2020.02.11 08:52:52]
WITH t AS
(
SELECT 'A' empno, '20200101' dt FROM dual
UNION ALL SELECT 'A', '20200102' FROM dual
UNION ALL SELECT 'A', '20200103' FROM dual
UNION ALL SELECT 'A', '20200104' FROM dual
UNION ALL SELECT 'A', '20200105' FROM dual
UNION ALL SELECT 'A', '20200106' FROM dual
UNION ALL SELECT 'A', '20200107' FROM dual
UNION ALL SELECT 'A', '20200108' FROM dual
UNION ALL SELECT 'A', '20200109' FROM dual
UNION ALL SELECT 'A', '20200110' FROM dual
UNION ALL SELECT 'A', '20200111' FROM dual
UNION ALL SELECT 'A', '20200112' FROM dual
UNION ALL SELECT 'A', '20200113' FROM dual
UNION ALL SELECT 'A', '20200114' FROM dual
UNION ALL SELECT 'A', '20200115' FROM dual
UNION ALL SELECT 'A', '20200116' FROM dual
UNION ALL SELECT 'A', '20200117' FROM dual
UNION ALL SELECT 'A', '20200118' FROM dual
UNION ALL SELECT 'A', '20200119' FROM dual
UNION ALL SELECT 'A', '20200120' FROM dual
UNION ALL SELECT 'B', '20200110' FROM dual
UNION ALL SELECT 'B', '20200111' FROM dual
UNION ALL SELECT 'B', '20200112' FROM dual
UNION ALL SELECT 'B', '20200113' FROM dual
UNION ALL SELECT 'B', '20200114' FROM dual
UNION ALL SELECT 'B', '20200115' FROM dual
UNION ALL SELECT 'B', '20200116' FROM dual
UNION ALL SELECT 'B', '20200117' FROM dual
UNION ALL SELECT 'B', '20200118' FROM dual
UNION ALL SELECT 'B', '20200119' FROM dual
UNION ALL SELECT 'B', '20200120' FROM dual
UNION ALL SELECT 'B', '20200121' FROM dual
UNION ALL SELECT 'B', '20200122' FROM dual
UNION ALL SELECT 'B', '20200123' FROM dual
UNION ALL SELECT 'B', '20200124' FROM dual
UNION ALL SELECT 'B', '20200125' FROM dual
)
SELECT b.empno
, a.dt
, NVL2(b.dt, '출근', '') gb
FROM (SELECT TO_CHAR(dt + LEVEL - 1, 'yyyymmdd') dt
FROM (SELECT TO_DATE('202001', 'yyyymm') dt FROM dual)
CONNECT BY LEVEL <= LAST_DAY(dt) - dt + 1
) a
LEFT OUTER JOIN t b
PARTITION BY (b.empno)
ON a.dt = b.dt
;
by OraTaDo
[2020.02.13 10:43:53]
by 마농님 정말 감사합니다.^^
) 버튼을 클릭하여 작성 하시면 됩니다.