Hi
Can somone help me explain this.
Why does the left join not group the rnGrp column like the outer apply?
Thanks,
Danii
CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,START DATETIME NOT NULL
,ENDS DATETIME NOT NULL
,TYPE varchar(20) NOT NULL
,STAT varchar(20) NOT NULL
)
;
INSERT INTO #temp
VALUES('64074558792','20160731','20160805','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160801','20160805','Act','PART');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Act','PART');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Act','Yes');
INSERT INTO #temp
VALUES('64074558792','20160812','20160814','Act','No');
-- WITH OUTER APPLY
;WITH src AS (
SELECT Identifier , START, ENDS, TYPE, STAT,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY START , ENDS) AS rn
FROM #temp
)
,grouped as (
Select s.* ,d.rnGrp from src s
outer apply (select top 1 rn rnGrp from src s2
where s.Identifier = s2.Identifier and s2.STAT='Yes'and s2.rn>=s.rn ) d(rnGrp))
Select *
from grouped g2
-- WITH LEFT JOIN
;WITH src AS (
SELECT Identifier , START, ENDS, TYPE, STAT,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY START , ENDS) AS rn
FROM #temp
)
,grouped as (
Select s.* ,x.rnGrp from src s
left outer join (select top 1 rn,rn rnGrp,Identifier,STAT from src s2) x
on s.Identifier=x.Identifier and x.STAT='Yes' and x.rn>=s.rn )
Select *
from grouped g2