안녕하세요..!
질문이 바뀌어서 다시 올리겠습니다 ..
select substr(hr_date,1.6) hr_date,
,emp_no,
birthday,
name
from student
where substr(hr_date,1.6)>='201901'
and name='홍길동'
hr_date | emp_no | birthday | name |
201901 | 111222 | 19900901 | 홍길동 |
201905 | 111222 | 19900901 | 홍길동 |
201907 | 111333 | 19900901 | 홍길동 |
이렇게 select한 값을 ...
hr_date | emp_no | birthday | name |
201901 | 111222 | 19900901 | 홍길동 |
201902 | 111222 | 19900901 | 홍길동 |
201903 | 111222 | 19900901 | 홍길동 |
201904 | 111222 | 19900901 | 홍길동 |
201905 | 111222 | 19900901 | 홍길동 |
201907 | 111333 | 19900901 | 홍길동 |
201908 | 111333 | 19900901 | 홍길동 |
201909 | 111333 | 19900901 | 홍길동 |
이렇게 월별 누적데이터로 select하고 싶습니다...
ADD_MONTHS함수를 써보았는데..잘안되네요 ㅠㅠ
감사합니다..
-- WHERE SUBSTR(HR_DATE,1,6) >= '201901' <= BETWEEN으로 쓰는경우는 없나요? -- 가능하면 원본 데이터로 주세요. WITH T AS ( SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL ), COPY_T AS ( SELECT TO_CHAR(ADD_MONTHS( MIN_DT , LEVEL - 1 ),'YYYYMM') DT FROM (SELECT SYSDATE CURR_DT , MIN(TO_DATE(hr_date,'YYYYMM')) MIN_DT FROM T ) CONNECT BY LEVEL <= MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) SELECT B.DT , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.DT) emp_no , LAST_VALUE(A.birthday IGNORE NULLS) OVER(ORDER BY B.DT) birthday , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.DT) name FROM T A , COPY_T B WHERE A.hr_date(+) = B.DT
답변감사합니다..!
WITH T AS (
SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL
SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201902' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201903' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201905' , '111225' ,'19890302' ,'이순신' FROM DUAL
), COPY_T AS (
SELECT TO_CHAR(ADD_MONTHS( MIN_DT , LEVEL - 1 ),'YYYYMM') DT
FROM (SELECT SYSDATE CURR_DT
, MIN(TO_DATE(hr_date,'YYYYMM')) MIN_DT
FROM T )
CONNECT BY LEVEL <= MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1
)
SELECT B.DT
, LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.DT) emp_no
, LAST_VALUE(A.birthday IGNORE NULLS) OVER(ORDER BY B.DT) birthday
, LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.DT) name
FROM T A
, COPY_T B
WHERE A.hr_date(+) = B.DT
이렇게 실행하였을때
DT | emp_no | birthday | name |
201901 | 111222 | 19900901 | 홍길동 |
201907 | 111333 | 19900901 | 홍길동 |
201908 | 111333 | 19900901 | 홍길동 |
201909 | 111333 | 19900901 | 홍길동 |
201902 | 111225 | 19890302 | 이순신 |
201903 | 111225 | 19890302 | 이순신 |
201904 | 111225 | 19890302 | 이순신 |
201905 | 111225 | 19890302 | 이순신 |
201906 | 111225 | 19890302 | 이순신 |
이렇게 출력이되네요 ㅠㅠ특정한사람만 조건걸었을때는 월별로 잘 출력이되는데 ㅠㅠ
hr_date의 원본데이터는 20190101 이런형식입니다.(vachar2)
-- sql 이 좀 지저분해졌네요. -- 수정햇어요 WITH T AS ( SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL UNION ALL SELECT '201902' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL SELECT '201903' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL SELECT '201905' , '111225' ,'19890302' ,'이순신' FROM DUAL ), COPY_T AS ( SELECT A.* , B.* FROM ( SELECT TO_CHAR(ADD_MONTHS( min_dt , LEVEL - 1 ),'YYYYMM') DT FROM (SELECT SYSDATE CURR_DT , MIN(TO_DATE(hr_date,'YYYYMM')) min_dt FROM T ) CONNECT BY LEVEL <= MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) A , ( SELECT name , MIN(hr_date) min_dt FROM T GROUP BY name ) B ) SELECT * FROM ( SELECT B.dt , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.name , B.dt) emp_no , LAST_VALUE(A.birthday IGNORE NULLS) OVER( ORDER BY B.name , B.dt) birthday , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.name , B.dt) name , MIN_DT -- 수정 FROM T A , COPY_T B WHERE A.hr_date(+) = B.DT AND A.name(+) = B.name ) T WHERE DT >= MIN_DT -- 수정 ORDER BY name , dt
답변감사드립니다....!
근데 원데이터를 넣고 하니,, 데이터에 상관없이 201901~201909부터 출력이 됩니다 ㅠㅠ
뭐가 잘못된건지 ㅠㅠ
WITH T AS (
select SUBSTR(hr_date,1,6)hr_date,emp_no,birthday,name
from student
where SUBSTR(hr_date,1,6)>='201901'
), COPY_T AS (
SELECT A.* , B.*
FROM ( SELECT TO_CHAR(ADD_MONTHS( min_dt , LEVEL - 1 ),'YYYYMM') DT
FROM (SELECT SYSDATE CURR_DT
, MIN(TO_DATE(hr_date,'YYYYMM')) min_dt
FROM T )
CONNECT BY LEVEL <= MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) A
, ( SELECT name , MIN(hr_date) min_dt FROM T GROUP BY name ) B
)
SELECT *
FROM ( SELECT B.dt
, LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.name , B.dt) emp_no
, LAST_VALUE(A.birthday IGNORE NULLS) OVER( ORDER BY B.name , B.dt) birthday
, LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.name , B.dt) name
FROM T A
, COPY_T B
WHERE A.hr_date(+) = B.DT
AND A.name(+) = B.name
) T
WHERE emp_no is not null
ORDER BY name , dt
원본데이터 : student
select SUBSTR(hr_date,1,6)hr_date,emp_no,birthday,name
from student
where SUBSTR(hr_date,1,6)>='201901'
hr_date | emp_no | birthday | name |
201907 | 111222 | 19900101 | 홍길동 |
201901 | 777999 | 19880101 | 이순신 |
201904 | 888999 | 19880101 | 이순신 |
201902 | 333444 | 19890301 | 안중근 |
답변 정말 감사합니다...!
만약 동일한 년월에 두개의 emp_no가 존재하니깐 마지막 emp_no가 출력이 됩니다 ㅠㅠ
WITH T AS (
SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL
SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111334' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201902' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201903' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201905' , '111225' ,'19890302' ,'이순신' FROM DUAL
), COPY_T AS (
SELECT A.* , B.*
FROM ( SELECT TO_CHAR(ADD_MONTHS( min_dt , LEVEL - 1 ),'YYYYMM') DT
FROM (SELECT SYSDATE CURR_DT
, MIN(TO_DATE(hr_date,'YYYYMM')) min_dt
FROM T )
CONNECT BY LEVEL <= MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) A
, ( SELECT name , MIN(hr_date) min_dt FROM T GROUP BY name ) B
)
SELECT *
FROM ( SELECT B.dt
, LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.name , B.dt) emp_no
, LAST_VALUE(A.birthday IGNORE NULLS) OVER( ORDER BY B.name , B.dt) birthday
, LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.name , B.dt) name
, MIN_DT
FROM T A
, COPY_T B
WHERE A.hr_date(+) = B.DT
AND A.name(+) = B.name
) T
WHERE DT >= MIN_DT
ORDER BY name , dt
-----
질문이 계속 바뀌어서 죄송합니다 ㅠㅠ
죄송합니다...!저도 헷갈렸네요 ㅠㅠ
동일월에 동일인이 2건 이상일때
ex)
hr_date | emp_no | birthday | name |
201903 | 111222 | 19910101 | 홍길동 |
201903 | 222333 | 19910101 | 홍길동 |
201905 | 444555 | 19910101 | 홍길동 |
201907 | 666777 | 19910101 | 홍길동 |
결과는
hr_date | emp_no | birthday | name |
201903 | 111222 | 19910101 | 홍길동 |
201903 | 222333 | 19910101 | 홍길동 |
201904 | 111222 | 19910101 | 홍길동 |
201904 | 222333 | 19910101 | 홍길동 |
201905 | 444555 | 19910101 | 홍길동 |
201906 | 444555 | 19910101 | 홍길동 |
201907 | 666777 | 19910101 | 홍길동 |
201908 | 666777 | 19910101 | 홍길동 |
201909 | 666777 | 19910101 | 홍길동 |
감사합니다.
WITH t AS ( SELECT '20190301' hr_date, '111222' emp_no, '19910101' birthday, '홍길동' name FROM dual UNION ALL SELECT '20190302', '222333', '19910101', '홍길동' FROM dual UNION ALL SELECT '20190501', '444555', '19910101', '홍길동' FROM dual UNION ALL SELECT '20190701', '666777', '19910101', '홍길동' FROM dual UNION ALL SELECT '20190701', '888999', '19910101', '이순신' FROM dual ) SELECT TO_CHAR(ADD_MONTHS(sdt, lv-1), 'yyyymm') hr_ym , emp_no , birthday , name FROM (SELECT sdt , emp_no , birthday , name , NVL( MIN(ADD_MONTHS(sdt, -1)) OVER(PARTITION BY birthday, name ORDER BY sdt RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING AND UNBOUNDED FOLLOWING) , TRUNC(sysdate, 'mm')) edt FROM (SELECT TRUNC(TO_DATE(hr_date, 'yyyymmdd'), 'mm') sdt , emp_no, birthday, name FROM t WHERE hr_date > '201901' ) ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 12) WHERE lv <= MONTHS_BETWEEN(edt, sdt) + 1 ORDER BY birthday, name, hr_ym, emp_no ;