sql - 规范化非规范化表中的数据

Question

我的表中有这样的数据

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|
-----|----|----------|----------|

只要发生角色更改，我就需要捕获开始日期和结束日期。我尝试了不同的方法，但无法得到输出。

感谢任何帮助。

下面是我尝试过的 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

Answer 1

这里的关键是识别连续的行，直到角色发生变化。这可以通过使用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