SQLTeam.com | Weblogs | Forums

Retain Previous ROW values -SQL

sql2008

#1
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

#2

Change the name of the tables for the ones you have in your system

with MyTable
AS
(
SELECT T1.ProjectID, T1.ClassID,T1.SubClassID, T1.Status, T1.Aphase
, COALESCE( LAG(T1.Aphase) OVER(partition by T1.ProjectID order by ClassID),0 )as Prev_Val,
LAG(T1.Status) OVER(partition by T1.ProjectID order by ClassID) as Prev_status
FROM ##Data as T1
)
SELECT ProjectID, ClassID, Status, Aphase,
Rphase=(CASE when Prev_status='Unrejected' then '0'
else Prev_Val end)
FROM MyTable