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
