SELECT a.eno, to_char(b.dt, 'YYYY-MM-DD') AS hirdate, a.INTIME FROM (SELECT ENO,TO_CHAR(WORKIN_DATE,'YYYY-MM-DD') AS dt,TO_CHAR(WORKIN_TIME,'HH24:MI') AS INTIME FROM TB_WORKINOUT w LEFT JOIN (SELECT TRUNC(SYSDATE, 'MM')+ LEVEL - 1 AS dt FROM DUAL CONNECT BY LEVEL <= (TRUNC(LAST_DAY(sysdate) - TRUNC(SYSDATE, 'MM')+1))) C ON (W.WORKIN_DATE = C.dt) where w.eno='H123' ORDER BY ENO, WORKIN_DATE) a, (SELECT TRUNC(SYSDATE, 'MM')+ LEVEL - 1 AS dt FROM DUAL CONNECT BY LEVEL <= (TRUNC(LAST_DAY(sysdate) - TRUNC(SYSDATE, 'MM')+1))) b WHERE a.dt(+) = b.dt ORDER BY a.eno, b.dt;
현재 이런식으로 코드를 작성하였는데 결과 값을 유저마다 30개의 데이터를 뽑고 싶은데, 날짜로 조인을 하다보니 유저마다 30개의 값이 나오지 않습니다.ㅠㅠ
도저히 생각이 나지 않아 질문드립니다. ㅠㅠ
SELECT b.eno , a.hirdate , b.intime FROM (SELECT TO_CHAR(TRUNC(sysdate, 'mm') + LEVEL - 1, 'yyyy-mm-dd') hirdate FROM dual CONNECT BY LEVEL <= TRUNC(LAST_DAY(sysdate)) - TRUNC(sysdate, 'mm') + 1 ) a LEFT OUTER JOIN (SELECT eno , TO_CHAR(workin_date, 'yyyy-mm-dd') hirdate , TO_CHAR(workin_time, 'hh24:mi') intime FROM tb_workinout WHERE workin_date >= TRUNC(sysdate, 'mm') AND workin_date <= TRUNC(LAST_DAY(sysdate)) -- AND eno = 'H123' ) b PARTITION BY (b.eno) ON a.hirdate = b.hirdate ; -- http://gurubee.net/lecture/2204