Consecutive Perspective Stored Procedure

create procedure dbo.USR_USP_REPORT_PLANSTAGEDURATIONSCONSECUTIVE (@PROSPECTPLANTYPECODEID uniqueidentifier)
as
with [STEPS]
as (
	select row_number() over (
			order by i.[PROSPECTPLANID],
				i.[ACTUALSTARTDATETIME]
			) as [ALLSTEPSEQUENCENUMBER],
		i.[ACTUALSTARTDATETIME],
		i.[ACTUALENDDATETIME],
		i.[PROSPECTPLANID],
		max(i.[ACTUALENDDATETIME]) over (partition by i.[PROSPECTPLANID]) as [LASTSTEPINPLANENDDATETIME],
		i.[PROSPECTPLANSTATUSCODEID]
	from [INTERACTION] as i
	inner join [PROSPECTPLAN] pl on i.[PROSPECTPLANID] = pl.[ID]
	where i.[COMPLETED] = 1
		and pl.[PROSPECTPLANTYPECODEID] = @PROSPECTPLANTYPECODEID
	),
[STAGEOCCURRENCESFIRSTPASS]
as (
	select s.[ACTUALSTARTDATETIME],
		s.[ACTUALENDDATETIME],
		r.[PROSPECTPLANSTATUSCODEID] as [PREVIOUSSTEPPROSPECTPLANSTATUSCODEID],
		s.[PROSPECTPLANSTATUSCODEID],
		r.[PROSPECTPLANID] as [PREVIOUSSTEPPROSPECTPLANID],
		s.[PROSPECTPLANID],
		s.[LASTSTEPINPLANENDDATETIME]
	from [STEPS] as s
	left join [STEPS] as r on s.[ALLSTEPSEQUENCENUMBER] - 1 = r.[ALLSTEPSEQUENCENUMBER]
	),
[STAGEOCCURRENCES]
as (
	select row_number() over (
			order by sofp.[PROSPECTPLANID],
				sofp.[ACTUALSTARTDATETIME]
			) as [ALLSTAGEOCCURRENCESSEQUENCENUMBER],
		sofp.[ACTUALSTARTDATETIME],
		sofp.[ACTUALENDDATETIME],
		sofp.[LASTSTEPINPLANENDDATETIME],
		sofp.[PROSPECTPLANID],
		sofp.[PROSPECTPLANSTATUSCODEID]
	from [STAGEOCCURRENCESFIRSTPASS] as sofp
	where sofp.[PROSPECTPLANID] <> sofp.[PREVIOUSSTEPPROSPECTPLANID]
		or (
			(sofp.[PROSPECTPLANSTATUSCODEID] <> sofp.[PREVIOUSSTEPPROSPECTPLANSTATUSCODEID])
			and (sofp.[PROSPECTPLANID] = sofp.[PREVIOUSSTEPPROSPECTPLANID])
			)
	),
[STAGEOCCURRENCEDURATIONS]
as (
	select so1.[ACTUALSTARTDATETIME] as [STARTDATETIME],
		so1.[ACTUALENDDATETIME] as [ENDDATETIME],
		"STAGEOCCURRENCEDURATION" = case 
			when so1.[ACTUALENDDATETIME] <> so1.[LASTSTEPINPLANENDDATETIME]
				then cast(so2.[ACTUALSTARTDATETIME] - so1.[ACTUALSTARTDATETIME] as float)
			when so1.[ACTUALENDDATETIME] = so1.[LASTSTEPINPLANENDDATETIME]
				then cast(so1.[ACTUALENDDATETIME] - so1.[ACTUALSTARTDATETIME] as float)
			end,
		so1.[PROSPECTPLANID],
		so1.[PROSPECTPLANSTATUSCODEID]
	from [STAGEOCCURRENCES] as so1
	left join [STAGEOCCURRENCES] as so2 on so1.ALLSTAGEOCCURRENCESSEQUENCENUMBER + 1 = so2.ALLSTAGEOCCURRENCESSEQUENCENUMBER
	)
select p.[DESCRIPTION] as [STAGENAME],
	avg(cast([STAGEOCCURRENCEDURATIONS].[STAGEOCCURRENCEDURATION] as float)) as [AVGSTAGEOCCURRENCEDURATION],
	min(cast([STAGEOCCURRENCEDURATIONS].[STAGEOCCURRENCEDURATION] as float)) as [MINSTAGEOCCURRENCEDURATION],
	max(cast([STAGEOCCURRENCEDURATIONS].[STAGEOCCURRENCEDURATION] as float)) as [MAXSTAGEOCCURRENCEDURATION],
	cast(count([STAGEOCCURRENCEDURATIONS].[PROSPECTPLANID]) as float) / cast(count(distinct ([STAGEOCCURRENCEDURATIONS].[PROSPECTPLANID])) as float) as [AVGTIMESINSTAGE]
from [STAGEOCCURRENCEDURATIONS]
inner join [PROSPECTPLANSTATUSCODE] as p on [STAGEOCCURRENCEDURATIONS].[PROSPECTPLANSTATUSCODEID] = p.[ID]
group by p.[DESCRIPTION]