规范化来自非规范化表的数据

Naveen 发表于 Dev

纳文

我的表中有这样的数据

RepID|Role|Status|StartDate |EndDate   |
-----|----|------|----------|----------|  
10001|R1  |Active|01/01/2015|01/31/2015|
-----|----|------|----------|----------|
10001|R1  |Leavee|02/01/2015|02/12/2015|
-----|----|------|----------|----------|
10001|R1  |Active|02/13/2015|02/28/2015|
-----|----|------|----------|----------|
10001|R2  |Active|03/01/2015|03/18/2015|
-----|----|------|----------|----------|
10001|R2  |Leave |03/19/2015|04/10/2015|
-----|----|------|----------|----------|
10001|R2  |Active|04/11/2015|05/10/2015|
-----|----|------|----------|----------|
10001|R1  |Active|05/11/2015|06/13/2015|
-----|----|------|----------|----------|
10001|R1  |Leave |06/14/2015|12/31/9998|
-----|----|------|----------|----------|

我正在寻找这样的输出，

RepID|Role|StartDate |EndDate   |   
-----|----|----------|----------|
10001|R1  |01/01/2015|02/28/2015|
-----|----|----------|----------|  
10001|R2  |03/01/2015|05/10/2015|
-----|----|----------|----------|  
10001|R1  |05/11/2015|12/31/9998|
-----|----|----------|----------|

每当仅发生角色更改时，我就需要捕获start和EndDate。我尝试了不同的方法，但无法获得输出。

任何帮助表示赞赏。

以下是我尝试过的SQL，但没有帮助，

SELECT T1.RepID, T1.Role, Min(T1.StartDate)     AS StartDate,     Max(T1.EndDate) AS    EndDate
FROM
 (SELECT rD1.RepID, rD1.Role, rD1.StartDate, rD1.EndDate 
FROM repDetails rD1
INNER JOIN repDetails rD2
    ON rD2.RepID = rD1.RepID AND rD2.StartDate = DateAdd (Day, 1, rD1.EndDate)      AND (rD2.Role = rD1.Role OR (rD2.Role IS NULL AND rD1.Role IS NULL)         OR (rD2.Role = '' AND rD1.Role = ''))

UNION

SELECT rD2.RepID, rD2.Role, rD2.StartDate, rD2.EndDate 
FROM repDetails rD1
INNER JOIN repDetails rD2
    ON rD2.RepID = rD1.RepID AND rD2.StartDate = DateAdd (Day, 1, rD1.EndDate)      AND (rD2.Role = rD1.Role OR (rD2.Role IS NULL AND rD1.Role IS NULL)         OR (rD2.Role = '' AND rD1.Role = ''))
    ) T1
GROUP BY T1.RepID, T1.Role

UNION

SELECT EP.RepID, EP.Role AS DataValue, EP.StartDate,    EP.EndDate
FROM repDetails EP
LEFT OUTER JOIN 
(SELECT rD1.RepID, rD1.Role, rD1.StartDate, rD1.EndDate 
FROM repDetails rD1
INNER JOIN repDetails rD2
    ON rD2.RepID = rD1.RepID AND rD2.StartDate = DateAdd (Day, 1, rD1.EndDate)      AND (rD2.Role = rD1.Role OR (rD2.Role IS NULL AND rD1.Role IS NULL)         OR (rD2.Role = '' AND rD1.Role = ''))

UNION

SELECT rD2.RepID, rD2.Role ,    rD2.StartDate, rD2.EndDate 
FROM repDetails rD1
INNER JOIN repDetails rD2
    ON rD2.RepID = rD1.RepID AND rD2.StartDate = DateAdd (Day, 1, rD1.EndDate)      AND (rD2.Role = rD1.Role OR (rD2.Role IS NULL AND rD1.Role IS NULL)         OR (rD2.Role = '' AND rD1.Role = ''))
    ) T1
ON EP.RepID = T1.RepID AND EP.StartDate =   T1.StartDate
WHERE T1.RepID IS NULL

瓦姆西·普拉巴拉（Vamsi Prabhala）

此处的关键是识别连续的行，直到角色更改为止。可以通过使用lead功能比较下一行的角色和一些其他逻辑来将所有先前的行归为同一组来完成此操作。

将它们分类后，您只需要使用min和max获取开始和结束日期。

with groups as (
select x.*
,case when grp = 1 then 0 else 1 end + sum(grp) over(partition by repid order by startdate) grps
from (select t.*
      ,case when lead(role) over(partition by repid order by startdate) = role then 0 else 1 end grp
      from t) x
)
select distinct repid,role
,min(startdate) over(partition by repid,grps) startdt
,max(enddate) over(partition by repid,grps) enddt
from groups
order by 1,3

Sample demo

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。