利用流水號(row_number)以及累加語法，組合具有連續性的資料~如員工經歷

2012-09-16

28885
0

我們公司是做HR系統的廠商，所以經常會有需要切割/合併員工經歷的需求，

比如年底發獎金的時候，不同單位可能會有不同的獎金成數，這個時侯就必須把同仁在當年度在其服務部門的服務年資給算出來，

而這個時候就需要依服務單位為基準作經歷資料的合併，示意圖如下

(左邊是Row Data，右邊是我們期望的結果，這邊先不考慮員工進出的問題，假設員工是一直再職的)

因為每次寫都得從舊程式找，另外也常被問，所以這邊就做一下紀錄

1.先準備測試資料

--準備測試資料
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

2.依照員工編號及生效日編流水號

--先依員工編號,生效日編個字的流水號
SELECT row_number() OVER(PARTITION BY EmpId ORDER BY hirDate) As Seq,* Into WK2 FROM WK1 Order BY EmpId,HirDate

另外SQL 2000下沒有row_number()可以用以下語法達到同樣的目的(這邊要注意Order By的部分，就依照你要的排序去下，另外如果你的 Script有Join很多Table或是其他複雜運算，建議先Create Table 再用Insert Into的方式把資料寫進去)

--SQL 2000下依群組編流水號
SELECT *,identity(INT,1,1) As IDField,0 As Seq INTO WK21 FROM WK1 Order BY EmpId,HirDate


UPDATE A SET A.Seq=B.IDField FROM WK21 As A INNER JOIN
(
Select EmpId,min(IDField) As IDField FROM WK21 Group BY EmpId
) As B ON A.EmpId=B.EmpId


Update WK21 SET Seq=IDField-Seq+1

3.長出每筆記錄的起迄日

--長出每筆記錄的起迄日
DECLARE @VirtulDate DateTime
--最後一筆接不到迄日先預先給一個無限大的日期
SET @VirtulDate='2900/01/01'

SELECT 
  0 As ComputerField,0 As ComputerField1,
  A.*,IsNull(DateAdd(d,-1,B.HirDate),@VirtulDate) As QutDate Into WK3 FROM WK2 As A 
  LEFT JOIN WK2 As B On A.EmpId=B.EmpId AND A.Seq=B.Seq-1

4.接下來是這個演算法的重點，先用幾張圖來說明

(先建立一個運算欄位，預設都給0)

(重點1、每一筆資料都跟前一筆做比較，如果單位不一樣的話，就將運算欄位更新為 1)

(重點2、將運算欄位依員工作累加)

(經過上述步驟，我們可以看到計算欄位已經依照員工單位的異動落上可供區分的註記了)

以下為程式碼

--如果跟上一筆的Dep不同ComputerField=1
Update A SET A.ComputerField=1 FROM WK3 As A INNER JOIN WK3 As B
  On A.EmpId=B.EmpId AND A.Seq=B.Seq+1 AND A.Dep<>B.Dep

--依照EmpId(人)ComputerField做累加(放到ComputerField1)
Update A SET A.ComputerField1=B.ComputerField1 FROM WK3 As A INNER JOIN
(
Select A.EmpId,A.Seq,sum(B.ComputerField) As ComputerField1 FROM WK3 As A INNER JOIN WK3 As B 
  On A.EmpId=B.EmpId AND A.Seq>=B.Seq GROUP BY A.EmpId,A.Seq
) As B ON A.EmpId=B.EmpId AND A.Seq=B.Seq

5.丟出最後要的結果

SELECT 
EmpId,Name,
Max(Dep) As Dep,min(HirDate) As Sdate,
Case When max(QutDate)='2900/01/01' THEN NULL ELSE max(QutDate) END  As EDate FROM WK3 Group BY EmpId,Name,ComputerField1

其實語法不難，難的是想這個演算法的過程XD，當初也是跟資深同仁討論好久才想出來的，給大家參考參考囉

Sample.zip

T SQL

回首頁