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

Database
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

  • SQL
  • sqlserver
Tags (2)