我的表格中有数据
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
