디비랑[dɪ'bɪraŋ]

DROP TABLE copy_ymd;
DROP TABLE ttt;
GO

-- copy_ymd 생성
CREATE TABLE copy_ymd
( ymd DATETIME PRIMARY KEY
, holiday_yn BIT DEFAULT 0
);
GO

-- copy_ymd 데이터 저장
DECLARE @first_day DATETIME, @i INT;
SELECT @first_day = '20000101', @i = 1;

WHILE (@i <= 3650)
BEGIN
    INSERT INTO copy_ymd
    SELECT @first_day  + @i - 1
         , CASE WHEN DATEPART(dw, @first_day  + @i - 1) IN (1, 7)
                THEN 1
                ELSE 0
           END;
    SET @i = @i + 1
END;
GO

-- copy_ymd 확인
SELECT * FROM copy_ymd;
GO

-- prj_hist
CREATE TABLE prj_hist
( emp_no INT
, prj_nm NVARCHAR(30)
, start_date DATETIME
, end_date DATETIME
, work_rate DECIMAL(5,2)
);
GO

-- prj_hist 데이터 저장
INSERT prj_hist
SELECT 101, 'AA', '20070101', '20070430', 50 UNION ALL
SELECT 101, 'BB', '20061108', '20070223', 50 UNION ALL
SELECT 102, 'AA', '20070110', '99991231', 80 UNION ALL
SELECT 102, 'CC', '20070401', '99991231', 20 UNION ALL
SELECT 103, 'AA', '20070101', '99991231', 30 UNION ALL
SELECT 103, 'BB', '20061201', '20070308', 30 UNION ALL
SELECT 103, 'CC', '20070208', '20070520', 40 UNION ALL
SELECT 104, 'BB', '20060910', '99991231', 100;
GO

-- prj_hist 확인
SELECT * FROM prj_hist;

-- 조회 : 사원별로 프로젝트에 투입된 일자를 계산하는데
--        휴일은 제외하고 합산한다.

-- 조작 1.
SELECT *
FROM prj_hist p
INNER JOIN copy_ymd y
ON y.ymd BETWEEN p.start_date AND p.end_date
WHERE y.ymd BETWEEN '2007-01-01' AND '2007-02-28'
AND y.holiday_yn = 0

-- 결과
SELECT emp_no
, COUNT(DISTINCT ymd)
FROM prj_hist p
INNER JOIN copy_ymd y
ON y.ymd BETWEEN p.start_date AND p.end_date
WHERE y.ymd BETWEEN '2007-01-01' AND '2007-02-28'
AND y.holiday_yn = 0
GROUP BY emp_no;