[SQL] 서브쿼리로 휴일을 제외한 근무일수 계산하기

0. 목표

 - 서브쿼리로 휴일을 제외한 근무일수 계산하기

1. 실습하기

 1) 테이블 생성

  (1) procedure_test

CREATE TABLE sql_test.procedure_test
(
	id int,
    date1 datetime,
    date2 datetime
);

 (2) holiday

 CREATE TABLE sql_test.holiday
(
	holiday datetime
);

 2) 데이터 삽입

INSERT INTO sql_test.procedure_test(id, date1, date2) VALUES(1, '2021-01-01 11:11:11', '2021-01-20 11:11:11');
INSERT INTO sql_test.procedure_test(id, date1, date2) VALUES(2, '2021-01-10 11:11:11', '2021-01-20 11:11:11');

INSERT INTO sql_test.holiday(holiday) VALUES('2021-01-02');
INSERT INTO sql_test.holiday(holiday) VALUES('2021-01-08');
INSERT INTO sql_test.holiday(holiday) VALUES('2021-01-12');
INSERT INTO sql_test.holiday(holiday) VALUES('2021-01-15');
INSERT INTO sql_test.holiday(holiday) VALUES('2021-01-18');

 3) 업무일 계산

SELECT 
	id, 
    DATEDIFF(date2, date1) AS CNT1, 
	(SELECT COUNT(holiday) FROM holiday WHERE holiday >= date1 AND holiday < date2) AS CNT2
FROM sql_test.procedure_test

 4) 결과

WITH A AS (
	SELECT 
		id, 
		DATEDIFF(date2, date1) AS CNT1, 
		(SELECT COUNT(holiday) FROM holiday WHERE holiday >= date1 AND holiday < date2) AS CNT2
	FROM sql_test.procedure_test
) 

SELECT
	id,
    CNT1 - CNT2 AS result
FROM A;