How to replace the null values from lead with other value from other table

Database
Highlighted
Visitor

How to replace the null values from lead with other value from other table


 

I have two Tables. One called Opportunity Table and Opp stage change Table.

Opportunity Table

     OppID|  actualclosedate |statecodename
    ----------------------------------------    
    1    |   5-7-2016       |Won    
    2    |   5-9-2017       |Lost   
    3    |   5-10-2017      |open   
    etc    

Stage Change Table

Opp ID  Stage       createdonDate
1       Prospect    1-Jan
1 Qualify 15-Jan
1 Develop 25-Jan
1 Qualify 9-Feb
1 Develop 7-March
1 Prospect 9-April

I want to calculate the total time an Opportunity stays in prospect stage, develop, qualify stage.  I tried to get the 

OppID      Stage       createdon   NextStageDate                  Time in stage(Days)
------------------------------------------------------------------------------------
    1         Propsect    1-Jan        15-Jan                        14
    1         Qualify     15-Jan       25-Jan                        10
    1         Develop     25-Jan       9-Feb                         15
    1         Qualify     9-Feb        7-March                       27
    1         Develop     7-March      9-April                       33 
    1         Prospect    9-April      5-July--(actualclosedate)     87

I want to use Today Date if that Opp is Open, Actual close date(from OppTable) if its won/lost.

Age in Prospect = 14+87= 101 Days

Age in Develop= 15+33= 48 Days

Age in Qualify= 27+10= 37 Days

 

I want the Final Results to be like this

   Opp ID   AgeInProspect   AgeInDevelop   AgeinQualify 
    -------------------------------------------------------
    1        101             48             37 
    2        ..etc 
    3

 

my initial attempt to get the next stage date

 

select *,

lead(createdon) over (partition by id order by createdon) as nextstagedate

from stagechange

Tags (2)