I thought that might be the case;-)
Here is an adjustment to the code, picks the two last entries for each subject
😎
create table #temp(candId int identity(1,1),CanNum int,name varchar(50),Attempt1 int,Attempt2 int,attempt3 int)
insert into #temp values (1001, 'Abhas', 120, 150, 180) ;
insert into #temp values (1002, 'John', 150, 150, 180) ;
insert into #temp values (1001, 'Abhas', 150, 150, 180) ;
insert into #temp values (1002, 'John', 150, 150, 190) ;
insert into #temp values (1001, 'Abhas', 150, 150, 125) ;
insert into #temp values (1001, 'Abhas', 50, 50, 125) ;
;WITH BASE_DATA AS
(
SELECT
2 + ROW_NUMBER() OVER
(
PARTITION BY T.CanNum
ORDER BY T.candId
) --AS CAN_RID
- COUNT(T.candId) OVER
(
PARTITION BY T.CanNum
) AS CAN_RID
,T.candId
,T.CanNum
,T.name
,T.Attempt1
,T.Attempt2
,T.attempt3
from #temp T
)
SELECT
BD.CAN_RID
,BD.candId
,BD.CanNum
,BD.name
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt1 IS NULL THEN BD.Attempt1
WHEN BD.CAN_RID = 1 AND BD.Attempt1 <> B2.Attempt1 THEN BD.Attempt1
WHEN BD.CAN_RID > 1 THEN BD.Attempt1
ELSE NULL
END AS Attempt1
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt2 IS NULL THEN BD.Attempt2
WHEN BD.CAN_RID = 1 AND BD.Attempt2 <> B2.Attempt2 THEN BD.Attempt2
WHEN BD.CAN_RID > 1 THEN BD.Attempt2
ELSE NULL
END AS Attempt2
,CASE
WHEN BD.CAN_RID = 1 AND B2.Attempt3 IS NULL THEN BD.Attempt3
WHEN BD.CAN_RID = 1 AND BD.Attempt3 <> B2.Attempt3 THEN BD.Attempt3
WHEN BD.CAN_RID > 1 THEN BD.Attempt3
ELSE NULL
END AS Attempt3
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.CanNum = B2.CanNum
AND BD.CAN_RID = B2.CAN_RID - 1
WHERE BD.CAN_RID > 0;
DROP TABLE #temp;
Results
CAN_RID candId CanNum name Attempt1 Attempt2 Attempt3
-------- ------- ------- ------ ----------- ----------- -----------
1 5 1001 Abhas 150 150 NULL
2 6 1001 Abhas 50 50 125
1 2 1002 John NULL NULL 180
2 4 1002 John 150 150 190