Create Another Data Warehouse Version

The goal of this version is to migrate the granularity logic in the stored procedures for the other versions of the report from the stored procedures to the ETL process. The load step in the SSIS packages look similar to the common table expressions in the OLTP version of the stored procedure for the report. What follows is the Transact-SQL for the load step for the stage occurrence package. The SSIS package will convert the data gathered from this query into rows for new tables defined in a set of data warehouse revisions extensions.

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
	),
[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 t on s.[ALLSTEPSEQUENCENUMBER] + 1 = t.[ALLSTEPSEQUENCENUMBER]
	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],
		pl.[PROSPECTPLANTYPECODEID],
		pl.[PROSPECTID]
	from [STAGEOCCURRENCESFIRSTPASS] as sofp
	inner join [PROSPECTPLAN] pl on sofp.[PROSPECTPLANID] = pl.[ID]
	where sofp.[PROSPECTPLANID] <> sofp.[PREVIOUSSTEPPROSPECTPLANID]
		or (
			(sofp.[PROSPECTPLANSTATUSCODEID] <> sofp.[PREVIOUSSTEPPROSPECTPLANSTATUSCODEID])
			and (sofp.[PROSPECTPLANID] = sofp.[PREVIOUSSTEPPROSPECTPLANID])
			)
	)
select so1.[ACTUALSTARTDATETIME] as [STARTDATETIME],
	so1.[ACTUALENDDATETIME] as [ENDDATETIME],
	"STAGEOCCURRENCEDURATION" = case 
		when so1.[ACTUALENDDATETIME] <> so1.[LASTSTEPINPLANENDDATETIME]
			then so2.[ACTUALSTARTDATETIME] - so1.[ACTUALSTARTDATETIME]
		when so1.[ACTUALENDDATETIME] = so1.[LASTSTEPINPLANENDDATETIME]
			then so1.[ACTUALENDDATETIME] - so1.[ACTUALSTARTDATETIME]
		end,
	so1.[PROSPECTPLANID] as [PROSPECTPLANSYSTEMID],
	so1.[PROSPECTPLANSTATUSCODEID] as [PROSPECTPLANSTATUSSYSTEMID],
	so1.[PROSPECTPLANTYPECODEID] as [PROSPECTPLANTYPESYSTEMID],
	so1.[PROSPECTID] as [CONSTITUENTSYSTEMID],
	1 as ISINCLUDED
from [STAGEOCCURRENCES] as so1
left join [STAGEOCCURRENCES] as so2 on so1.ALLSTAGEOCCURRENCESSEQUENCENUMBER + 1 = so2.ALLSTAGEOCCURRENCESSEQUENCENUMBER