亂馬客

前言

前陣子遇到要找出"本月全勤的人員清單"，格式為:部門 員工數 員工名單(以逗號分開)，如部門1  3 何大牌,郭小玉,林大仔。

以下是實作過程整理。

資料準備

開始前，先準備部門檔、員工檔及請假檔，並加入測試資料。

--部門檔
CREATE TABLE DEPT
(
    DEPT_ID VARCHAR(32) NOT NULL PRIMARY KEY
    ,DEPT_NAME NVARCHAR(64)
)
--初始資料 (新增99個部門)
DELETE FROM DEPT
DECLARE @I INT
SET @I = 1
WHILE @I < 100
BEGIN
    INSERT INTO DEPT(DEPT_ID, DEPT_NAME) 
    VALUES('DEPT_ID:' + RIGHT('0' + LTRIM(STR(@I)),  2), 'DEPT_NAME:' + RIGHT('0' + LTRIM(STR(@I)),  2))
    SET @I = @I + 1
END
SELECT * 
FROM DEPT
ORDER BY DEPT_ID

--員工檔
CREATE TABLE EMP
(
EMP_NO VARCHAR(32) NOT NULL PRIMARY KEY
,EMP_NAME NVARCHAR(64)
,DEPT_ID VARCHAR(32)
)

--初始資料 (各部門新增10個人)
delete from EMP
DECLARE @I INT, @J INT, @K INT
SET @I = 1
SET @J = 1
WHILE @I < 100
BEGIN
    SET @K = 1
    WHILE @K < 11
    BEGIN
        INSERT INTO EMP(EMP_NO, EMP_NAME, DEPT_ID) 
        VALUES('EMP_NO:' +  RIGHT('0000' + LTRIM(STR(@J)),  4), 'EMP_NAME:' +  RIGHT('0000' + LTRIM(STR(@J)),  4) 
        , 'DEPT_ID:' + RIGHT('0' + LTRIM(STR(@I)),  2))
        SET @J = @J + 1
        SET @K = @K + 1
    END
    SET @I = @I + 1
END
SELECT * FROM EMP 
ORDER BY EMP_NO

--請假檔
CREATE TABLE LEAVE
(
    SEQ_NO INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,EMP_NO VARCHAR(32)
    ,LEAVE_START_DATE DATETIME
    ,LEAVE_END_DATE DATETIME
)

--初始資料
DELETE FROM LEAVE
DECLARE @I INT, @J INT, @K INT
SET @I = 1
SET @J = 1
WHILE @I < 100
BEGIN
    SET @K = 1
    WHILE @K < 11
    BEGIN
        IF (@K % 2 = 0 AND @J % 7 <> 0)
        BEGIN
            INSERT INTO LEAVE(EMP_NO, LEAVE_START_DATE, LEAVE_END_DATE)
            VALUES('EMP_NO:' +  RIGHT('0000' + LTRIM(STR(@J)),  4)
            , DATEADD(day, -1, GETDATE()), DATEADD(day, -1, GETDATE()))
        END 
        ELSE
        BEGIN
            -- DO NOTHING
            PRINT '@K:' + STR(@K)
        END
        SET @J = @J + 1
        SET @K = @K + 1
    END

    SET @I = @I + 1
END
SELECT * FROM LEAVE 
ORDER BY EMP_NO

實作

建好資料後，就可以開始練習了! 我的習慣是先一步一步拆解，各部份都解出來後，再組起來。

• 先找出本月的方式

DATEDIFF(MONTH, GETDATE(), DATA日期) = 0 AND  DATEDIFF(YEAR, GETDATE(), DATA日期) = 0 或是 YEAR = YEAR(DATA日期) AND MONTH = MONTH(DATA日期)

• 找出本月有請假的員工

DECLARE @CUR_MONTH INT, @CUR_YEAR INT
SET @CUR_MONTH = MONTH(GETDATE())
SET @CUR_YEAR = YEAR(GETDATE())
SELECT A.*
FROM LEAVE A (NOLOCK)
WHERE (YEAR(A.LEAVE_START_DATE) = @CUR_YEAR AND MONTH(A.LEAVE_START_DATE) = @CUR_MONTH) 
OR (YEAR(A.LEAVE_END_DATE) = @CUR_YEAR AND MONTH(A.LEAVE_END_DATE) = @CUR_MONTH) 

• 找出本月沒有請假的員工(全勤)，去除掉本月請假員工就是本月的全勤員工

DECLARE @CUR_MONTH INT, @CUR_YEAR INT
SET @CUR_MONTH = MONTH(GETDATE())
SET @CUR_YEAR = YEAR(GETDATE())

--本月沒請假的員工
SELECT B.DEPT_ID, B.DEPT_NAME, A.EMP_NO, A.EMP_NAME 
FROM EMP A (NOLOCK) INNER JOIN DEPT B (NOLOCK)
ON (A.DEPT_ID = B.DEPT_ID)
WHERE A.EMP_NO 
NOT IN
(
    --本月有請假的員工
    SELECT A.EMP_NO
    FROM LEAVE A (NOLOCK)
    WHERE (YEAR(A.LEAVE_START_DATE) = @CUR_YEAR AND MONTH(A.LEAVE_START_DATE) = @CUR_MONTH) 
    OR (YEAR(A.LEAVE_END_DATE) = @CUR_YEAR AND MONTH(A.LEAVE_END_DATE) = @CUR_MONTH) 
)

• 找出本月某部門的全勤員工姓名清單(如何大牌,郭小玉,林大仔)

這個需要利用一個字串變數來串接，所以建立UFN_GET_NONLEAVE_EMPNAMELIST Function來用

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFN_GET_NONLEAVE_EMPNAMELIST]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFN_GET_NONLEAVE_EMPNAMELIST]
GO
CREATE FUNCTION [dbo].[UFN_GET_NONLEAVE_EMPNAMELIST]
/****************************************************************************************
'程式代號：UFN_GET_EMP_NAME_LIST
'程式名稱：傳入年、月、部門代號，取得該年月，該部門的全勤的員工姓名LIST
'參數說明：@intYear:年, @intMonth:月, @strDeptId:部門代號
'傳回值　：NVARCHAR:員工姓名LIST
'副作用　：無
'備　註　：所有參數皆必需傳入
'範　例　：SELECT dbo.UFN_GET_NONLEAVE_EMPNAMELIST(2010, 02, 'DEPT_ID:01')         
****************************************************************************************/ 
(@intYear INT
, @intMonth INT
, @strDeptId VARCHAR(64))
RETURNS NVARCHAR(MAX)
BEGIN
        DECLARE @EMP_NAME_LIST NVARCHAR(MAX)
        SET @EMP_NAME_LIST = ''
        SELECT @EMP_NAME_LIST = @EMP_NAME_LIST + A.EMP_NAME + ','
        FROM EMP A (NOLOCK) INNER JOIN DEPT B (NOLOCK)
        ON (B.DEPT_ID = @strDeptId AND A.DEPT_ID = B.DEPT_ID)
        WHERE A.EMP_NO 
        NOT IN
        (
            --本月有請假的員工
            SELECT A.EMP_NO
            FROM LEAVE A (NOLOCK)
            WHERE (YEAR(A.LEAVE_START_DATE) = @intYear AND MONTH(A.LEAVE_START_DATE) = @intMonth) 
            OR (YEAR(A.LEAVE_END_DATE) = @intYear AND MONTH(A.LEAVE_END_DATE) = @intMonth) 
        )
        DECLARE @RESULT_LEN INT
        SET @RESULT_LEN = LEN(@EMP_NAME_LIST)
        IF @RESULT_LEN > 0
        BEGIN
            SET @EMP_NAME_LIST = LEFT(@EMP_NAME_LIST, @RESULT_LEN -1)
        END
        RETURN @EMP_NAME_LIST
END

• 找出本月各部門的的全勤員工數及員工姓名清單

利用Group再加上之前寫的Function就可以達到我們最後要的結果

DECLARE @CUR_MONTH INT, @CUR_YEAR INT
SET @CUR_MONTH = MONTH(GETDATE())
SET @CUR_YEAR = YEAR(GETDATE())
PRINT '@CUR_MONTH:' + STR(@CUR_MONTH)
PRINT '@CUR_YEAR:' + STR(@CUR_YEAR)
--本月沒請假的員工
SELECT B.DEPT_ID, B.DEPT_NAME, COUNT(*) AS EMP_CNT
, dbo.UFN_GET_NONLEAVE_EMPNAMELIST(@CUR_YEAR, @CUR_MONTH, B.DEPT_ID) AS EMP_NAME_LIST 
FROM EMP A (NOLOCK) INNER JOIN DEPT B (NOLOCK)
ON (A.DEPT_ID = B.DEPT_ID)
WHERE A.EMP_NO 
NOT IN
(
    --本月有請假的員工
    SELECT A.EMP_NO
    FROM LEAVE A (NOLOCK)
    WHERE (YEAR(A.LEAVE_START_DATE) = @CUR_YEAR AND MONTH(A.LEAVE_START_DATE) = @CUR_MONTH) 
    OR (YEAR(A.LEAVE_END_DATE) = @CUR_YEAR AND MONTH(A.LEAVE_END_DATE) = @CUR_MONTH) 
)
GROUP BY B.DEPT_ID, B.DEPT_NAME

結論

這個練習主要是利用Group來算出各部門的員工人數，另外就是再透過Function來取得員工名稱清單，而Function中則是透過一個字串變數來串員工的姓名，不知是否有無更好的方式來達成呢? Thanks.

附上範例程式:找出本月全勤人員.rar

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^