SQL 쿼리를 작성하고있는데 도움이 필요합니다..

Question

월별 휴가통계에 대한 쿼리를 작성하려고합니다. A라는 테이블에 START(휴가 시작일) END(휴가 종료일) USEDAY(사용일) 이라는 컬럼이 있을때 1월 30일 ~ 2월2일까지 4일의 휴가를 쓴다라고 가정하면 1월에 2일 2월에 2일이 나오게 쿼리를 작성해야하는데도저히 방법을 모르곘습니다.. 도움좀 부탁드립니다 ㅠㅠ..

르매 · Accepted Answer

다음 규칙을 추가해서 MySQL에서 동작하는 SQL로 작성해 봤습니다.한 번의 휴가는 최대 365일 이하토요일과 일요일은 휴가 일수에서 제외공휴일은 휴가 일수에서 제외직원 별로 집계절차는 이렇습니다.START ~ END의 기간이 1개의 Row로 되어 있는 원본으로부터 기간 만큼의 Rows를 가진 테이블을 만들어 내고, 여기서 주말과 공휴일인 Row를 삭제합니다. 그리고 마지막으로, 2023년으로 필터링 한 후 Month로 그룹핑하면 됩니다.아래는 마지막 집계 직전까지의 쿼리입니다.WITH RECURSIVE tally (n) AS ( -- 1 ~ 365 사이의 정수 SELECT 1 UNION ALL SELECT n &#43; 1 FROM tally WHERE n < 365 ), holidays (holiday_date, holiday_name) AS ( -- 공휴일 VALUES ROW ('2023-01-23', '설날') , ROW ('2023-01-24', '설날') ), annual_leaves (employee_id, start_date, end_date) AS ( -- 직원이 사용한 연차 VALUES ROW (1, '2023-01-30', '2023-02-02') -- 2023년 1월: 2일, 2월: 2일 , ROW (2, '2022-12-28', '2023-01-03') -- 2023년 1월: 2일 (주말 제외) , ROW (3, '2023-01-20', '2023-01-27') -- 2023년 1월: 4일 (설 연휴, 주말 제외) ), tmp_annual_leaves (employee_id, leave_date) AS ( SELECT al.employee_id, TIMESTAMPADD(DAY, t.n - 1, al.start_date) FROM annual_leaves al INNER JOIN tally t ON t.n <&#61; TIMESTAMPDIFF(DAY, al.start_date, al.end_date) &#43; 1 ) SELECT tal.employee_id, tal.leave_date FROM tmp_annual_leaves tal WHERE WEEKDAY(tal.leave_date) BETWEEN 0 AND 4 -- 월요일~금요일까지만 필터링 AND NOT EXISTS ( -- 공휴일 제거 SELECT * FROM holidays WHERE holiday_date &#61; tal.leave_date );위의 결과를 보면 직원이 실제 사용한 연차의 날짜 목록이 나옵니다.이 데이터를 2023년 한 해로 필터링하고, 월 별로 그룹핑한 최종 결과는 아래와 같습니다. (직원 별로 볼 필요가 없다면.. employee_id를 제거하면 되겠죠!)WITH RECURSIVE tally (n) AS ( -- 1 ~ 365 사이의 정수 SELECT 1 UNION ALL SELECT n &#43; 1 FROM tally WHERE n < 365 ), holidays (holiday_date, holiday_name) AS ( -- 공휴일 VALUES ROW ('2023-01-23', '설날') , ROW ('2023-01-24', '설날') ), annual_leaves (employee_id, start_date, end_date) AS ( -- 직원이 사용한 연차 VALUES ROW (1, '2023-01-30', '2023-02-02') -- 2023년 1월: 2일, 2월: 2일 , ROW (2, '2022-12-28', '2023-01-03') -- 2023년 1월: 2일 (주말 제외) , ROW (3, '2023-01-20', '2023-01-27') -- 2023년 1월: 4일 (설 연휴, 주말 제외) ), tmp_annual_leaves (employee_id, leave_date) AS ( SELECT al.employee_id, TIMESTAMPADD(DAY, t.n - 1, al.start_date) FROM annual_leaves al INNER JOIN tally t ON t.n <&#61; TIMESTAMPDIFF(DAY, al.start_date, al.end_date) &#43; 1 ), real_annual_leaves (employee_id, leave_date) AS ( SELECT tal.employee_id, tal.leave_date FROM tmp_annual_leaves tal WHERE WEEKDAY(tal.leave_date) BETWEEN 0 AND 4 -- 월요일~금요일까지만 필터링 AND NOT EXISTS ( -- 공휴일 제거 SELECT * FROM holidays WHERE holiday_date &#61; tal.leave_date ) ) SELECT employee_id, MONTH(leave_date) AS month_no, COUNT(*) FROM real_annual_leaves WHERE leave_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY employee_id, MONTH(leave_date) ORDER BY employee_id, month_no;

Clickin · Answer

http://www.gurubee.net/lecture/2848기간 분할 검색으로 링크 글을 상황에 잘 맞춰서 바꾸시면 될 듯 합니다.

오케잇 · Answer

SELECT HOLIDAY, COUNT(*) CNT FROM (SELECT LPAD(TO_CHAR(TO_DATE('20230130','YYYYMMDD') &#43; (LEVEL-1), 'YYYYMMDD'),6) AS &#34;HOLIDAY&#34;FROM DUALCONNECT BY LEVEL <&#61; TO_DATE('20230202','YYYYMMDD') - TO_DATE('20230130','YYYYMMDD') &#43; 1) GROUP BY HOLIDAY;두 날짜 사이에 모든 일자를 일단 출력하고 그것을 월별로 그룹짓게 하고 카운트 한겁니다.

최은영 · Answer

오라클 기준select LAST_DAY(to_date('20230130', 'YYYYMMDD')) - to_date('20230130', 'YYYYMMDD') + 1 as start_diff_day, to_date('20230202', 'YYYYMMDD') - to_date(to_char(to_date('20230202', 'YYYYMMDD'), 'YYYYMM') || '01', 'YYYYMMDD') + 1 as last_diff_day from dual;

SQL 쿼리를 작성하고있는데 도움이 필요합니다..

4개의 답변

Q&A 목록