기간과 대상만 주어졌을때, 기간내에 날짜를 반영할 수 있도록 하여 Count하는 방법을 찾고 있습니다~! 0 21 1,918

by 뽀꼼여사 [SQL Query] QUERY ORACLE SQL [2017.09.01 14:18:09]


일별 휴가간 인원 COUNT하기

 

DATA

이름 시작
강단이 8/1 8/5
박지훈 8/5 8/7
이대휘 8/1 8/3
김재환 8/4 8/6

 

결과값

날짜 인원
8/1 2
8/2 2
8/3 2
8/4 2
8/5 3
8/6 2
8/7 1

 

by 마농 [2017.09.01 14:44:50]

조회 기간이 따로 주어지는 건가요? (예 : 8/1 ~ 8/7)
아니면 그냥 전체 테이블 읽어서 결과를 내는 건가요?


by 뽀꼼여사 [2017.09.01 14:50:06]

조회기간은 유동적으로 가져가려고 하는데요,

오늘기준 ~개월전 이렇게 하려고 해요~~


by 마농 [2017.09.01 14:52:24]

조회기간이 주어진다면?
조회기간 중 인원이 없는 일자는 0 으로 표시하나요?
아니면 아예 목록에서 제외하나요?


by 뽀꼼여사 [2017.09.01 14:53:49]

인원이 없는 날짜는 0으로 반영해야해요~

날짜가 중간에 없으면 안돼요~


by 마농 [2017.09.01 14:58:57]

아래 아찌님 쿼리를 아우터 조인으로 변경하시면 됩니다.
그리고,,,
질문하실 때 자료를 명확하게 표시해 주셔야 합니다.
8/1 이렇게 저장되어 있을리는 없을 것 같고..
날짜 타입으로 저장되는지? 문자형으로 저장되는지?
문자형이라면 어떤 포멧으로 저장되는지 등을 명확하게 해주셔야 합니다.


by 뽀꼼여사 [2017.09.01 15:04:09]

네~!


by 우리집아찌 [2017.09.01 14:45:23]
/*
FRDT : 20170801
TODT : 20170806
*/
WITH DATE_T AS (
   SELECT TO_DATE( :FRDT , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
    CONNECT BY LEVEL <= TO_DATE(:TODT , 'YYYYMMDD') - TO_DATE(:FRDT, 'YYYYMMDD') + 1 
) , EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈'  , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘'  , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환'  , '20170804' , '20170806' FROM DUAL
)

SELECT A.DT , COUNT(NM) CNT 
  FROM DATE_T A
      ,EMP_T B
  WHERE A.DT BETWEEN B.ST_DT AND B.ED_DT
  GROUP BY DT 
  ORDER BY DT

 


by 뽀꼼여사 [2017.09.01 15:04:54]

감사합니다~!

일단 돌려봤는데, raw가 1개만 떠서요~

기간은 들어가 있는데, raw가 8/1만 확인되는건 혹시 왜 그런걸까요~?


by 마농 [2017.09.01 15:09:30]

위 쿼리는 묵시적 자동 형변환이 되는 부분이 있습니다.
이는 오류의 가능성을 내포합니다.
명시적으로 바꿔 줄 필요가 있습니다.
1. 시작일, 종료일자의 자료형과 저장 형태 확인해서 알려주세요.
2. 아우터 조인으로 바꾸셨나요?
3. 적용하신 쿼리를 보여주세요.


by 우리집아찌 [2017.09.01 15:09:49]

FRDT 와 TODT 가 20170801 로 되어있지 않으신가요?

FRDT -- 20170801

TODT -- 20170806

확인해주세요.


by 뽀꼼여사 [2017.09.01 15:10:59]

WITH DATE_T AS (
   SELECT TO_DATE('20170801' , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
    CONNECT BY LEVEL <= TO_DATE('20170801' , 'YYYYMMDD') - TO_DATE('20170806', 'YYYYMMDD') + 1 ) , EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈'  , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘'  , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환'  , '20170804' , '20170806' FROM DUAL
)
SELECT A.DT , COUNT(NM) CNT
  FROM DATE_T A
      ,EMP_T B
  WHERE A.DT BETWEEN B.ST_DT AND B.ED_DT
  GROUP BY DT
  ORDER BY DT


by 우리집아찌 [2017.09.01 15:15:54]

CONNECT BY LEVEL <= TO_DATE('20170801' , 'YYYYMMDD') - TO_DATE('20170806', 'YYYYMMDD') + 1 ) , 

--> CONNECT BY LEVEL <= TO_DATE('20170806' , 'YYYYMMDD') - TO_DATE('20170801', 'YYYYMMDD') + 1 ) , 


by 뽀꼼여사 [2017.09.01 15:18:43]

아, 제가 날짜를 반대로 넣었었네여^^;;

 

감사합니다~!!!!

아,, 0인 날짜는 안나와요~~

이거 어떻게 하면 될까요~??

 

WITH DATE_T AS (
SELECT TO_DATE('20170801' , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20170810' , 'YYYYMMDD') - TO_DATE('20170801', 'YYYYMMDD') + 1 ),--LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE-1),-3))+1-2/24) ,
EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈'  , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘'  , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환'  , '20170804' , '20170806' FROM DUAL
)
SELECT A.DT , COUNT(NM) CNT
FROM DATE_T A
    ,EMP_T B
WHERE A.DT BETWEEN B.ST_DT AND B.ED_DT
GROUP BY DT
ORDER BY DT


by 마농 [2017.09.01 15:22:14]

1. 날짜를 거꾸로 뺐구요.
  - to_dt - fr_dt
2. 아우터 조인 안했구요.
  - a.dt BETWEEN b.st_dt(+) AND b.ed_dt(+)
3. date_t 안의 dt 의 타입이 date 입니다.
  - 형식을 문자로 일치시켜 주셔야 합니다.
  - to_char(..., 'yyyymmdd') dt


추가로 올리신 완성 쿼리는 달력 생성 부분이 상당히 이상하네요.
조건절 부분과 select 절 부분의 일자가 일치하지 않네요.


by 우리집아찌 [2017.09.01 15:25:14]

BETWEEN 도 아우터 조인이 되는군요... ^^


by 뽀꼼여사 [2017.09.01 15:30:22]

아~ 알려주신대로 해봤어요~~

이렇게 하면 될까요~?

 

WITH DATE_T AS (
SELECT TO_DATE('20170801' , 'YYYYMMDD' ) + LEVEL - 1 DT FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20170810' , 'YYYYMMDD') - TO_DATE('20170801', 'YYYYMMDD') + 1 ),
EMP_T AS (
SELECT '강단이' NM , '20170801' ST_DT , '20170805' ED_DT FROM DUAL UNION ALL
SELECT '박지훈'  , '20170805' , '20170807' FROM DUAL UNION ALL
SELECT '이대휘'  , '20170801' , '20170803' FROM DUAL UNION ALL
SELECT '김재환'  , '20170804' , '20170806' FROM DUAL
)
SELECT A.DT , COUNT(NM) CNT
FROM DATE_T A
    ,EMP_T B
WHERE A.DT BETWEEN B.ST_DT(+) AND B.ED_DT(+)
GROUP BY DT
ORDER BY DT
 


by 뽀꼼여사 [2017.09.01 15:31:04]

두분 덕분에, 원하는 값을 산출할 수 있었어요~~! 너무 감사드립니다.^ㅡ^


by 마농 [2017.09.01 15:38:55]

dt 는 date type 이고, 비교되는 항목 st_dt 는 문자 타입이네요.
비교 타입을 일치시켜 주셔야 합니다.
dt 를 to_char 함수를 이용해 문자형으로 바꿔 주셔야 합니다.


by 뽀꼼여사 [2017.09.01 15:50:43]

네~!!!


by 마농 [2017.09.01 15:54:15]
WITH emp_t AS
(
SELECT '강단이' nm, '20170801' st_dt, '20170805' ed_dt FROM dual
UNION ALL SELECT '박지훈', '20170805', '20170807' FROM dual
UNION ALL SELECT '이대휘', '20170801', '20170803' FROM dual
UNION ALL SELECT '김재환', '20170804', '20170806' FROM dual
)
, date_t AS
(
SELECT TO_CHAR(sdt + LEVEL - 1, 'yyyymmdd') dt
  FROM (SELECT TO_DATE('20170801', 'yyyymmdd') sdt
             , TO_DATE('20170808', 'yyyymmdd') edt
          FROM dual
        )
 CONNECT BY LEVEL <= edt - sdt + 1
)
SELECT a.dt
     , COUNT(b.nm) cnt
  FROM date_t a
  LEFT OUTER JOIN emp_t b
    ON a.dt BETWEEN b.st_dt AND b.ed_dt
 GROUP BY dt
 ORDER BY dt
;

 


by 박군two [2017.09.04 11:11:52]

다른 분들의 SQL을 많이 보고 연습하다보니,

SQL 구문이 비슷하게 만들어지네요..

 

WITH EMP_VACATION_M AS (
    SELECT '강단이' EMP_NAME, '20170801' S_DATE, '20170804' E_DATE FROM DUAL UNION ALL
    SELECT '박지훈'         , '20170806',        '20170807'        FROM DUAL UNION ALL
    SELECT '이대휘'         , '20170801',        '20170803'        FROM DUAL UNION ALL
    SELECT '김재환'         , '20170804',        '20170804'        FROM DUAL 
),
DATE_LIST AS (
    SELECT S_DATE + (LEVEL - 1) AS VACATION_DATE
    FROM   (SELECT MIN(TO_DATE(EMP_VACATION_M.S_DATE, 'YYYY-MM-DD')) S_DATE, MAX(TO_DATE(EMP_VACATION_M.E_DATE, 'YYYY-MM-DD')) E_DATE
            FROM   EMP_VACATION_M
           ) TT
    CONNECT BY LEVEL<= TT.E_DATE - TT.S_DATE + 1
)
SELECT B.VACATION_DATE, COUNT(A.EMP_NAME) AS EMP_COUNT
FROM   EMP_VACATION_M A RIGHT OUTER JOIN DATE_LIST B
       ON B.VACATION_DATE BETWEEN TO_DATE(A.S_DATE, 'YYYY-MM-DD') AND  TO_DATE(A.E_DATE, 'YYYY-MM-DD')
GROUP BY B.VACATION_DATE
ORDER BY B.VACATION_DATE

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입