CODEWITHSUNDEEP
sqlsql-servert-sqlssms
sara
sara

Reputation: 534

How to get the next row value in another column

I have Table called Opp Table

   OppID         actualclosedate   
--------------------------------------
    1            10-March

I have a Table called stagechange

--------------------------------------
   OppID      Stage       createdon   
--------------------------------------
    1         Propsect    1-Jan        
    1         Qualify     15-Jan       
    1         Develop     25-Jan       
    1         Qualify     9-Feb       
    1         Develop     7-March     
    1         Prospect    9-April

How Can I create a new column and get the next row value that is in createdon column; to have something like this. If there is no more next values, we get the date from other table like actual close date in Opp Table

--------------------------------------
   OppID      Stage       createdon   newcolumn 
--------------------------------------
    1         Propsect    1-Jan        15-Jan 
    1         Qualify     15-Jan       25-Jan 
    1         Develop     25-Jan       9-Feb    
    1         Qualify     9-Feb        7-March    
    1         Develop     7-March      9-April 
    1         Prospect    9-April     10-March ----(actualclosedate)

Upvotes: 1

Views: 2540

Answers (3)

jalel eddine Khadhraoui
jalel eddine Khadhraoui

Reputation: 24

you can use the COALESCE fonction https://msdn.microsoft.com/fr-fr/library/ms190349.aspx

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82020

This will replace the final/missing date with actualclosedate

Select A.*
      ,NewColumn = IsNull(Lead(creaton) over (Partition By A.OppID Order By A.CreateOn),B.actualclosedate)
 From  stagechange A
 Join  Opp         B on A.OppID=B.OppID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Assuming createdOn is stored as a valid date/time type and by next you mean next by this column, then just use lead():

select sc.*,
       lead(createdOn) over (partition by OppId order by createdOn) as next_created_on
from stagechange sc;

This assumes (although not explicitly stated in the question) that you want the next date per OppId.

lead() is an ANSI standard function available in most, but not all databases.

Upvotes: 1

Related Questions