亂馬客

https://rainmakerho.github.io/

2012-10-02

[SQL]組合具有連續性的資料~如員工經歷

[SQL]組合具有連續性的資料~如員工經歷

最近看到了 獨行俠 大大的「利用流水號(row_number)以及累加語法，組合具有連續性的資料~如員工經歷」，感覺應該還可以改用其他的方式。

所以就沿用他的範例來練習 ^_^。


--準備測試資料 WK1
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'PG1' As Title,convert(DATETIME,'2001/01/01') As HirDate INTO WK1
UNION
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'PG2' As Title,'2002/01/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'SD1' As Title,'2003/07/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'GL' As Dep,'SD1' As Title,'2005/02/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'GL' As Dep,'SD2' As Title,'2007/01/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'KM' As Dep,'SA1' As Title,'2001/01/01' As HirDate
UNION
SELECT '002' As EmpId,'李小寶' As Name,'KM' As Dep,'SA1' As Title,'2002/01/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'TR' As Dep,'SA2' As Title,'2003/07/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'TR' As Dep,'PM2' As Title,'2005/02/01' As HirDate

image

 

要組出連續性的資料，馬上就想到使用SQL 2012新提供的函數(LEAD or LAG)來做，如下，


SELECT  EmpId ,
        Name ,
        Dep ,
        HirDate ,
        ISNULL(LEAD(HirDate) OVER ( PARTITION BY EmpId ORDER BY HirDate ),
               CAST('2990/10/10' AS DATETIME)) AS NextHirDate
FROM    WK1

image

 

但如果是非SQL 2012呢? 那就使用SUBQUERY來做。如下


SELECT  EmpId ,
        Name ,
        Dep ,
        HirDate ,
        ( SELECT    ISNULL(MIN(HirDate), CAST('2990/10/10' AS DATETIME))
          FROM      dbo.WK1 t2
          WHERE     t1.EmpId = t2.EmpId
                    AND t2.HirDate > t1.HirDate
        ) AS NextHirDate
FROM    WK1 t1
ORDER BY t1.EmpId, t1.HirDate

image

 

以日期(HirDate)組出連續資料後，要再來看各員工在各部門的最小及最大日期，來組出「員工經歷」。如下，


SELECT  EmpId ,
        Name ,
        Dep ,
        CONVERT(VARCHAR, MIN(HirDate), 111) AS HirDate ,
        MAX(CASE NextHirDate
              WHEN CAST('2990/10/10' AS DATETIME) THEN 'UNTIL NOW'
              ELSE CONVERT(VARCHAR, DATEADD(DAY, -1, NextHirDate), 111)
            END) NextHirDate
FROM    ( SELECT    EmpId ,
                    Name ,
                    Dep ,
                    HirDate ,
                    ( SELECT    ISNULL(MIN(HirDate),
                                       CAST('2990/10/10' AS DATETIME))
                      FROM      dbo.WK1 t2
                      WHERE     t1.EmpId = t2.EmpId
                                AND t2.HirDate > t1.HirDate
                    ) AS NextHirDate
          FROM      WK1 t1
        ) T3
GROUP BY EmpId ,
        Name ,
        Dep
ORDER BY EmpId ,
        HirDate

image

 

看似OK的結果，但 獨行俠  大大說:

在下面的狀況可能會失真喔
1~3月：A部門
4~8月：B部門
9~12月：又回A部門，
取Min Max 生效日，Group By部門可能會有問題ㄟ  ^^

 

嗯，Nyah-Nyah的確會有獨行俠 大大說的那種狀況，再把資料加入以上狀況的資料，如下。


--準備測試資料 WK1
DROP TABLE WK1

SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'PG1' As Title,convert(DATETIME,'2001/01/01') As HirDate INTO WK1
UNION
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'PG2' As Title,'2002/01/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'SD1' As Title,'2003/07/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'GL' As Dep,'SD1' As Title,'2005/02/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'GL' As Dep,'SD2' As Title,'2007/01/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'SD3' As Title,'2009/01/01' As HirDate
Union
SELECT '001' As EmpId,'王小明' As Name,'HR' As Dep,'SWA1' As Title,'2010/08/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'KM' As Dep,'SA1' As Title,'2001/01/01' As HirDate
UNION
SELECT '002' As EmpId,'李小寶' As Name,'KM' As Dep,'SA1' As Title,'2002/01/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'TR' As Dep,'SA2' As Title,'2003/07/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'TR' As Dep,'PM2' As Title,'2005/02/01' As HirDate
Union
SELECT '002' As EmpId,'李小寶' As Name,'TR' As Dep,'PM3' As Title,'2007/02/01' As HirDate
Union
SELECT '003' As EmpId,'郭小玉' As Name,'MIS' As Dep,'MG1' As Title,'2009/01/01' As HirDate
Union
SELECT '003' As EmpId,'郭小玉' As Name,'TO' As Dep,'MG2' As Title,'2010/01/01' As HirDate

image

 

所以單以部門及員工來GROUP是不行的.....


SELECT  EmpId ,
        Name ,
        Dep ,
        CONVERT(VARCHAR, MIN(HirDate), 111) AS HirDate ,
        MAX(CASE NextHirDate
              WHEN CAST('2990/10/10' AS DATETIME) THEN 'UNTIL NOW'
              ELSE CONVERT(VARCHAR, DATEADD(DAY, -1, NextHirDate), 111)
            END) NextHirDate
FROM    ( SELECT    EmpId ,
                    Name ,
                    Dep ,
                    HirDate ,
                    ( SELECT    ISNULL(MIN(HirDate),
                                       CAST('2990/10/10' AS DATETIME))
                      FROM      dbo.WK1 t2
                      WHERE     t1.EmpId = t2.EmpId
                                AND t2.HirDate > t1.HirDate
                    ) AS NextHirDate
          FROM      WK1 t1
        ) T3
GROUP BY EmpId ,
        Name ,
        Dep
ORDER BY EmpId ,
        HirDate

image

 

那就再加一個區分的FLAG，一樣是用SUBQUERY來算出要區分的FLAG(請注意SQL中DiffFlag)，再加入區分的FLAG來Group應該就可以了吧，如下，


SELECT  EmpId ,
        Name ,
        Dep ,
        CONVERT(VARCHAR, MIN(HirDate), 111) AS HirDate ,
        MAX(CASE NextHirDate
              WHEN CAST('2990/10/10' AS DATETIME) THEN 'UNTIL NOW'
              ELSE CONVERT(VARCHAR, DATEADD(DAY, -1, NextHirDate), 111)
            END) NextHirDate
FROM    ( SELECT    EmpId ,
                    Name ,
                    Dep ,
                    HirDate ,
                    ( SELECT    ISNULL(MIN(HirDate),
                                       CAST('2990/10/10' AS DATETIME))
                      FROM      dbo.WK1 t2
                      WHERE     t1.EmpId = t2.EmpId
                                AND t2.HirDate > t1.HirDate
                    ) AS NextHirDate ,
                    ( SELECT    COUNT(t2.Dep)
                      FROM      dbo.WK1 t2
                      WHERE     t1.EmpId = t2.EmpId
                                AND t2.HirDate > t1.HirDate
                                AND t2.Dep <> t1.Dep
                    ) AS DiffFlag
          FROM      WK1 t1
        ) T3
GROUP BY EmpId ,
        Name ,
        Dep ,
        DiffFlag
ORDER BY EmpId ,
        HirDate

image

 

Eye rolling smile...…….

那除了看員工進出部門的時間外，如果要再看員工Title的變換呢???

一樣，修改區分的FLAG，加入Title的條件即可，如下，


SELECT  EmpId ,
        Name ,
        Dep ,
        Title ,
        CONVERT(VARCHAR, MIN(HirDate), 111) AS HirDate ,
        MAX(CASE NextHirDate
              WHEN CAST('2990/10/10' AS DATETIME) THEN 'UNTIL NOW'
              ELSE CONVERT(VARCHAR, DATEADD(DAY, -1, NextHirDate), 111)
            END) NextHirDate
FROM    ( SELECT    EmpId ,
                    Name ,
                    Dep ,
                    Title ,
                    HirDate ,
                    ( SELECT    ISNULL(MIN(HirDate),
                                       CAST('2990/10/10' AS DATETIME))
                      FROM      dbo.WK1 t2
                      WHERE     t1.EmpId = t2.EmpId
                                AND t2.HirDate > t1.HirDate
                    ) AS NextHirDate ,
                    ( SELECT    COUNT(t2.Dep)
                      FROM      dbo.WK1 t2
                      WHERE     t1.EmpId = t2.EmpId
                                AND t2.HirDate > t1.HirDate
                                AND t2.Dep <> t1.Dep
                                AND t2.Title <> t1.Title
                    ) AS DiffFlag
          FROM      WK1 t1
        ) T3
GROUP BY EmpId ,
        Name ,
        Dep ,
        Title ,
        DiffFlag
ORDER BY EmpId ,
        HirDate

image

 

所以，以上的這種狀況，要記得加入「區分的FLAG」來處理哦。

如果大家有其他的方式，請告訴我哦。

Hi, 

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

請大家繼續支持 ^_^

學習網站

pluralsight

wintellectnow

標籤雲

系列文章

最新留言