question

poornima avatar image
poornima asked

SQL Columns based on Current and Previous Row

This is how my table should look like: MY requirement is i have to add a new column called **ChangeType** that should be calculated based on the **Phasechange**. If the first row phasechange is null it should be "Baseline".IF it has value then it should be phasechange.IN this example for RevisionNO=2 ,phasechange=1 then the changetype=phasechange as previous value for the RevisionNo=1 the phasechange was null. Then for revisionno=3 ,phasechange=null so changeType should be again a phasechange. Then for revisionno=4 ,phasechange= 3 so changeType should be again a phasechange (as for revisionno=3 it was null) for revisionno in (5,6) phasechange =3 which is same as previos Revision(4) so the changetype should be baselined. ProjectID DocumentNo RevisionNo PhaseChange ChangeType 102176-001 1 1 NULL Baselined 102176-001 2 2 1 Phasechange 102176-001 3 3 NULL Phasechange 102176-001 4 4 3 Phasechange 102176-001 5 5 3 Baselined 102176-001 6 6 3 Baselined Code: Create table #temp( ProjectID varchar(35), Documentno int, Revisionno int, PhaseChange int, ChangeType varchar(35)) insert into #temp values ( '102176-001', 1, 1, NULL,NULL) insert into #temp values ( '102176-001', 2, 2, 1,NULL) insert into #temp values ( '102176-001', 3, 3, NULL,NULL) insert into #temp values ( '102176-001', 4, 4, 3,NULL) insert into #temp values ( '102176-001', 5, 5, 3,NULL) insert into #temp values ( '102176-001', 6, 6, 3,NULL)
sql query
2 comments
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
No time to give a proper answer - but this might give you an idea: https://msdn.microsoft.com/en-us/library/hh231256.aspx T-SQL LAG & LEAD functions.
0 Likes 0 ·
poornima avatar image poornima commented ·
Can somebody plz help i need this to b completed ASAP.I'm stuck on this problem
0 Likes 0 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
As Thomas suggested, the best way to do this is with LAG. I can't test this right now, but I believe this should work (unless I make a typo): SELECT t.ProjectId, t.DocumentNo, t.RevisionNo, t.PhaseChange , ChangeType = CASE (LAG(t.PhaseChange) OVER (PARTITION BY t.ProjectId ORDER BY t.RevisionNo)) WHEN null THEN IIF(t.PhaseChange is null, 'BaseLined', 'PhaseChange') WHEN t.PhaseChange THEN 'BaseLined' ELSE 'PhaseChange' END FROM #temp ORDER BY ProjectId, RevisionNo ;
1 comment
10 |1200

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

poornima avatar image poornima commented ·
It worked like wonder :) Thanks a lot Tom.Did made few changes SELECT t.ProjectId, t.DocumentNo, t.RevisionNo, t.PhaseChange , CASE when (LAG(t.PhaseChange)OVER (PARTITION BY t.ProjectId ORDER BY t.RevisionNo)) is null THEN IIF(t.PhaseChange is null , 'BaseLined', 'PhaseChange') WHEN (LAG(t.PhaseChange)OVER (PARTITION BY t.ProjectId ORDER BY t.RevisionNo)) =t.PhaseChange THEN 'BaseLined'ELSE 'PhaseChange' END FROM #temp t ORDER BY ProjectId, RevisionNo
0 Likes 0 ·

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.