Nonconsecutive Perspective Stored Procedure

create procedure dbo.USR_USP_REPORT_PLANSTAGEDURATIONSNONCONSECUTIVE (@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],
		so1.[ACTUALENDDATETIME],
		"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.[PROSPECTPLANSTATUSCODEID],
		so1.[PROSPECTPLANID]
	from [STAGEOCCURRENCES] as so1
	left join [STAGEOCCURRENCES] as so2 on so1.ALLSTAGEOCCURRENCESSEQUENCENUMBER + 1 = so2.ALLSTAGEOCCURRENCESSEQUENCENUMBER
	),
[STAGEDURATIONS]
as (
	select sum(cast(sod.[STAGEOCCURRENCEDURATION] as float)) over (
			partition by sod.[PROSPECTPLANID],
			sod.[PROSPECTPLANSTATUSCODEID]
			) as [STAGEDURATION],
		RANK() over (
			partition by sod.[PROSPECTPLANID],
			sod.[PROSPECTPLANSTATUSCODEID] order by sod.[ACTUALSTARTDATETIME]
			) as [FIRSTOCCURRENCEROWINSTAGE],
		sod.[PROSPECTPLANSTATUSCODEID]
	from [STAGEOCCURRENCEDURATIONS] as sod
	)
select p.[DESCRIPTION] as [STAGENAME],
	avg(cast([STAGEDURATIONS].[STAGEDURATION] as float)) as [AVGSTAGEDURATION],
	min(cast([STAGEDURATIONS].[STAGEDURATION] as float)) as [MINSTAGEDURATION],
	max(cast([STAGEDURATIONS].[STAGEDURATION] as float)) as [MAXSTAGEDURATION]
from [STAGEDURATIONS]
inner join [PROSPECTPLANSTATUSCODE] as p on [STAGEDURATIONS].[PROSPECTPLANSTATUSCODEID] = p.[ID]
where [FIRSTOCCURRENCEROWINSTAGE] = 1
group by p.[DESCRIPTION]