sql – 从非规范化表中规范化数据

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

解决方法

将它们分组后,您只需使用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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!