question

poornima avatar image
poornima asked

Lag on Groups -Need Help

This is how my table looks. PID JNo EV Phase Name 100 1 1 NULL Test1 101 2 2 NULL Test2 102 3 3 NULL Test3 103 4 4 1 Test4.1 103 4 4 1 Test4.2 103 4 4 1 Test4.3 104 5 5 3 Test5.1 104 5 5 3 Test5.2 104 5 5 3 Test5.3 105 6 6 3 Test6.1 105 6 6 3 Test6.2 105 6 6 3 Test6.3 107 7 7 NULL Test7.1 107 7 7 NULL Test7.2 107 7 7 NULL Test7.3 I have to create a column which says whether my phase has been changed or Baselined. for Eg: For PID=100 first phase is null so it should be Baselined. For PID=101 phase is null .Previous value (100) it was null So it should be Baselined for 101.Similarly for 102 it should be null. For PID=103 phase =1.Previous value was (102) it was null.So it should be PhaseChange. For PID=104 phase =3 .Previous value was (103) it was 1So it should be PhaseChange. For PID=105 phase =3 .Previous value was (104) it was 3.So it should be Baselined. PID JNo Ev Phase Name PCH 100 1 1 NULL Test1 Baselined 101 2 2 NULL Test2 Baselined 102 3 3 NULL Test3 Baselined 103 4 4 1 Test4.1 PhaseChange 103 4 4 1 Test4.2 PhaseChange 103 4 4 1 Test4.3 PhaseChange 104 5 5 3 Test5.1 PhaseChange 104 5 5 3 Test5.2 PhaseChange 104 5 5 3 Test5.3 PhaseChange 105 6 6 3 Test6.1 Baselined 105 6 6 3 Test6.2 Baselined 105 6 6 3 Test6.3 Baselined 107 7 7 NULL Test7.1 PhaseChange 107 7 7 NULL Test7.2 PhaseChange 107 7 7 NULL Test7.3 PhaseChange Code Sample: create Table #SampleTable (ProjectID int, JustNo int, ERev int, Phase int, Name varchar(100) ) insert into #SampleTable values(100,1,1,Null,'Test1') insert into #SampleTable values(101,2,2,Null,'Test2') insert into #SampleTable values(102,3,3,Null,'Test3') insert into #SampleTable values(103,4,4,1,'Test4.1') insert into #SampleTable values(103,4,4,1,'Test4.2') insert into #SampleTable values(103,4,4,1,'Test4.3') insert into #SampleTable values(104,5,5,3,'Test5.1') insert into #SampleTable values(104,5,5,3,'Test5.2') insert into #SampleTable values(104,5,5,3,'Test5.3') insert into #SampleTable values(105,6,6,3,'Test6.1') insert into #SampleTable values(105,6,6,3,'Test6.2') insert into #SampleTable values(105,6,6,3,'Test6.3') insert into #SampleTable values(107,7,7,null,'Test7.1') insert into #SampleTable values(107,7,7,null,'Test7.2') insert into #SampleTable values(107,7,7,null,'Test7.3') select * from #SampleTable I tried using Lag function no luck :(
sql server 2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Gazz avatar image
Gazz answered
This will give you a table that you can easily join back up to #SampleTable and get PCH from - the [PHASE] and [Pre Phase] columns are not actually needed in the select statement, I just kept them so you could see what it does. SELECT B.PROJECTID , B.PHASE , A.PHASE [Prev Phase] , PCH = CASE WHEN A.PHASE IS NULL AND B.PHASE IS NULL THEN 'Baselined' WHEN A.PHASE IS NULL AND B.PHASE IS NOT NULL THEN 'PhaseChange' WHEN A.PHASE IS NOT NULL AND B.PHASE IS NULL THEN 'PhaseChange' WHEN A.PHASE < B.PHASE THEN 'PhaseChange' ELSE 'Baselined' END FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY projectid ) + 1 [RowNum] , * FROM ( SELECT projectid , phase FROM #SampleTable GROUP BY projectid , phase ) x ) A RIGHT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY projectid ) [RowNum] , * FROM ( SELECT projectid , phase FROM #SampleTable GROUP BY projectid , phase ) x ) B ON A.ROWNUM = B.RowNum
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.