This is my Source resultset :
PID CID SubCID Status APhase
T8877-001 1 1 Unrejected 1
T8877-001 2 4 Unrejected 1
T8877-001 3 4 Unrejected 1
T8877-001 4 9 Approved 1
T8877-001 5 10 Approved 2
T8877-001 6 10 Approved 2
T8877-001 7 14 Initiated 2
T8877-001 8 22 Approved 3
T8877-001 9 22 Approved 3
T8877-001 10 25 Initiated 3
I am creating a new field with RPHASE which is calculated based on Aphase.The first row would always be 0.For the second row if the previous row value is "Approved" ,the previous row Aphase would be the currentrow Rphase.The Rphase has to be retained in all the rows unless if there is a new approval.For eg:for Cid= 6 and SubCID =10 ,the previous row's approved status="Approved" so the Rphase would be changed to 2 and so on.Retain Rphase until there is a Aphase change and Status="Approved"
PID CID SubCID Status RPhase APhase
T8877-001 1 1 Approved 0 1
T8877-001 2 4 Unrejected 0 1
T8877-001 3 4 Unrejected 0 1
T8877-001 4 9 Approved 0 1
T8877-001 5 10 Approved 1 2
T8877-001 6 10 Approved 2 2
T8877-001 7 14 Initiated 2 2
T8877-001 8 22 Approved 2 3
T8877-001 9 22 Approved 3 3
T8877-001 10 25 Initiated 3 3
Scripts :
Create table sampleData (ProjectID varchar(50),ClassID int,SubClassID int,Status varchar(50),Aphase int)
Insert into sampleData
select 'T8877-001', 1, 1 ,'Unrejected', 1
union
select 'T8877-001', 2, 4 ,'Unrejected', 1
union
select 'T8877-001', 3, 4 ,'Unrejected', 1
union
select 'T8877-001', 4, 9 ,'Approved', 1
union
select 'T8877-001', 5, 10 ,'Approved', 2
union
select 'T8877-001', 6, 10 ,'Approved', 2
union
select 'T8877-001', 7, 14 ,'Initiated', 2
union
select 'T8877-001', 8, 22 ,'Approved', 3
union
select 'T8877-001', 9, 22 ,'Approved', 3
union
select 'T8877-001', 10, 25 ,'Initiated', 3
select * from sampleData