데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 34965 게시물 읽기

No. 34965

쿼리부탁드립니다(남은 휴일수계산)
작성자	초보자	작성일	2008-12-15 13:38ⓒ 2008-12-15 13:41ⓜ	조회수	3,164

DAY_OFF(TABLE)
ID      DAY_OFF UPDATE_DATE
1       1       2008/10/17
1       1       2008/10/11
2       1       2008/10/17
1       1       2008/10/26
2       1       2008/10/26
1       1       2008/11/11
1       1       2008/11/19
3       1       2008/11/19
2       1       2008/12/04
3       1       2008/12/06
1       1       2008/12/08
2       1       2008/12/09
1       1       2008/12/10

DAY_OFF_PERSONAL(TABLE)
ID      TOTAL_OFF_DAY
1       11
2       10
3       9

위처럼 2개의 테이블이 있습니다.
DAY_OFF는 사용한 휴일이 등록되어 있고
DAY_OFF_PERSONAL은 개인이 사용할수 있는 휴가일수 입니다.

아래쿼리 처럼 했더니

마지막 DAY_OFF_REMIND_DAYS 가 원하는 데로 나오지 않습니다.
즉 사용할수 있는 총휴가일수에서 지금까지 사용한 휴가일수를 뺀나머지가 나와야합니다.

SELECT   d.id                                                  USER_ID
              ,to_char(d.update_date,'yyyy/mm')      DAY_OFF_MONTH
            ,p.total_off_day                                  DAY_OFF_STANDARD_DAYS
            ,sum(d.day_off)                                  DAY_OFF_DAYS_PER_MONTH
             ,p.total_off_day - sum(d.day_off)          DAY_OFF_REMIND_DAYS     <-------- 이부분입니다.
FROM     day_off                 d
             ,day_off_personal   p
WHERE    d.id = p.id
GROUP BY d.id
               ,p.total_off_day
              ,to_char(d.update_date,'yyyy/mm')

USER_ID DAY_OFF_MONTH DAY_OFF_STANDARD_DAYS DAY_OFF_DAYS_PER_MONTH DAY_OFF_REMIND_DAYS
1        2008/10        11             3               8                  <- 11-3(10월)                        = 8
1        2008/11        11             2               9                  <- 11-3(10월)-2(11월)            = 6
1        2008/12        11             2               9                  <- 11-3(10월)-2(11월)-2(12월) = 4
2        2008/10        10             2               8                  <- 10-2(10월)                        = 8
2        2008/12        10             2               8                  <- 10-2(10월)-2(12월)            = 6
3        2008/11        9              1               8                  <-   9-1(11월)                          = 8
3        2008/12        9              1               8                  <- 9-1(11월)-1(12월)              = 7

위의 결과에서 DAY_OFF_REMIND_DAYS가 옆에 나와있는 것처럼 나와야 합니다.

오라클 8.1이구요 ROLLUP은 사용불가능입니다.
통밥굴려봐도 안나오네요..
도와 주십시오.

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

사원테이블(DAY_OFF_PERSONAL)과 조인해서 쓰면 되겠네요..

SELECT A.ID, YYYYMD, SUM(DAY_OFF), MIN(CUM_SUM)
FROM (
  select a.id, substr(up_date,1,7) YYYYMD, DAY_OFF, sum(day_off) over (partition by a.id order by a.id, substr(up_date,1,7)) cum_SUM
  from
    (
    select '1' id, 1 day_off,   '2008/10/17' up_date from dual union all
    select '1' id, 1 day_off,   '2008/10/11' up_date from dual union all
    select '2' id, 1 day_off,   '2008/10/17' up_date from dual union all
    select '1' id, 1 day_off,   '2008/10/26' up_date from dual union all
    select '2' id, 1 day_off,   '2008/10/26' up_date from dual union all
    select '1' id, 1 day_off,   '2008/11/11' up_date from dual union all
    select '1' id, 1 day_off,   '2008/11/19' up_date from dual union all
    select '3' id, 1 day_off,   '2008/11/19' up_date from dual union all
    select '2' id, 1 day_off,   '2008/12/04' up_date from dual union all
    select '3' id, 1 day_off,   '2008/12/06' up_date from dual union all
    select '1' id, 1 day_off,   '2008/12/08' up_date from dual union all
    select '2' id, 1 day_off,   '2008/12/09' up_date from dual union all
    select '1' id, 1 day_off,   '2008/12/10' up_date from dual ) a
  order by 1,2
) A
GROUP BY A.ID, YYYYMD
ORDER BY 1,2

DOL님이 2008-12-15 14:56에 작성한 댓글입니다. Edit Ｘ

감사합니다.

문제는 오라클 8.1이라 over()를 사용할수가 없습니다.

다시 한번 도와주십시오

초보자님이 2008-12-15 15:15에 작성한 댓글입니다.
이 댓글은 2008-12-15 15:15에 마지막으로 수정되었습니다. Edit Ｘ

분석함수가 8.1.6부터 되는군요.. 8.i에서 되길래 되는줄 알았습니다.

별로 보기 좋아 보이지는 않지만 작성해봤습니다.

사원과는 조인해서 쓰세요..

select a.id, a.yyyymm, min(a.off_cnt), sum(b.off_cnt)
from (
  select id,
          substr(up_date,1,7) yyyymm,
    sum(day_off)        off_cnt
  from
    (
    select '1' id, 1 day_off,   '2008/10/17' up_date from dual union all
    select '1' id, 1 day_off,   '2008/10/11' up_date from dual union all
    select '2' id, 1 day_off,   '2008/10/17' up_date from dual union all
    select '1' id, 1 day_off,   '2008/10/26' up_date from dual union all
    select '2' id, 1 day_off,   '2008/10/26' up_date from dual union all
    select '1' id, 1 day_off,   '2008/11/11' up_date from dual union all
    select '1' id, 1 day_off,   '2008/11/19' up_date from dual union all
    select '3' id, 1 day_off,   '2008/11/19' up_date from dual union all
    select '2' id, 1 day_off,   '2008/12/04' up_date from dual union all
    select '3' id, 1 day_off,   '2008/12/06' up_date from dual union all
    select '1' id, 1 day_off,   '2008/12/08' up_date from dual union all
    select '2' id, 1 day_off,   '2008/12/09' up_date from dual union all
    select '1' id, 1 day_off,   '2008/12/10' up_date from dual ) a
  group by id, substr(up_date,1,7) ) a,
  (
  select id,
          substr(up_date,1,7) yyyymm,
    sum(day_off)        off_cnt
  from
    (
    select '1' id, 1 day_off,   '2008/10/17' up_date from dual union all
    select '1' id, 1 day_off,   '2008/10/11' up_date from dual union all
    select '2' id, 1 day_off,   '2008/10/17' up_date from dual union all
    select '1' id, 1 day_off,   '2008/10/26' up_date from dual union all
    select '2' id, 1 day_off,   '2008/10/26' up_date from dual union all
    select '1' id, 1 day_off,   '2008/11/11' up_date from dual union all
    select '1' id, 1 day_off,   '2008/11/19' up_date from dual union all
    select '3' id, 1 day_off,   '2008/11/19' up_date from dual union all
    select '2' id, 1 day_off,   '2008/12/04' up_date from dual union all
    select '3' id, 1 day_off,   '2008/12/06' up_date from dual union all
    select '1' id, 1 day_off,   '2008/12/08' up_date from dual union all
    select '2' id, 1 day_off,   '2008/12/09' up_date from dual union all
    select '1' id, 1 day_off,   '2008/12/10' up_date from dual ) a
  group by id, substr(up_date,1,7) ) b
where a.id     = b.id
and   a.yyyymm >= b.yyyymm
group by A.id, A.yyyymm

DOL님이 2008-12-15 15:46에 작성한 댓글입니다. Edit Ｘ

감사합니다.

조인해서 잘돌아갑니다.

좀더 공부해야겠네요.

초보자님이 2008-12-15 16:00에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
34968	[쿼리질문]자기값보다 작은 값의 개수COUNT [3]	이동훈	2008-12-15	2434
34967	수식계산..? [1]	MR강	2008-12-15	2245
34966	매 시간마다 30분 단위로 통계를 내고 싶은데... [1]	박문로	2008-12-15	4630
34965	쿼리부탁드립니다(남은 휴일수계산) [4]	초보자	2008-12-15	3164
34958	NOT EXISTS 에 대해서...A , B 테이블에서 B 테이블에 존재 하지 않는 데이터를 뽑을때... [2]	행동하자	2008-12-15	3846
34957	여러개의 칼럼을 하나의 primary key로 지정하고 싶은데요. [4]	유재훈	2008-12-14	2573
34956	전년대비를 어떻게 구해야할까요? [1]	궁금이	2008-12-14	2164