Report Spec for OLTP Version

Note: This version includes stored procedure parameters and UIModel items described elsewhere in the documentation.
<ReportSpec 
    xmlns="bb_appfx_report"
    xmlns:common="bb_appfx_commontypes"
    ID="882fe807-2570-4900-9c89-0861070f7ea5"
    Name="Prospect Plan Stage Durations Report (OLTP Version)"
    Description="Displays the averages, minimums, and maximums of durations of plan stages and stage occurrences. Also displays an average count of stage occurrences."
    Author="Technical Training"
    >

	<RDLFileName>Custom.AppFx.PlanStageDurations.Catalog.PlanStageDurationsReport.rdl</RDLFileName>
	<Folder>Custom Reports/Misc Reports</Folder>
	
	<DataRetrieval>
		<CreateSQL ObjectName="dbo.USR_USP_REPORT_PLANSTAGEDURATIONSOVERLAPPING" ObjectType="SQLStoredProc">
			<![CDATA[
create procedure dbo.USR_USP_REPORT_PLANSTAGEDURATIONSOVERLAPPING (@PROSPECTPLANTYPECODEID uniqueidentifier)
as
with [STEPS]
as (
	select i.[PROSPECTPLANSTATUSCODEID],
		i.[ACTUALENDDATETIME],
		min(i.[ACTUALSTARTDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [FIRSTSTEPINSTAGEDATETIME],
		max(i.[ACTUALENDDATETIME]) over (
			partition by i.[PROSPECTPLANID],
			i.[PROSPECTPLANSTATUSCODEID]
			) as [LASTSTEPINSTAGEDATETIME]
	from [INTERACTION] as i
	inner join [PROSPECTPLAN] pl on i.[PROSPECTPLANID] = pl.[ID]
	where i.[COMPLETED] = 1
		and pl.[PROSPECTPLANTYPECODEID] = @PROSPECTPLANTYPECODEID
	)
select p.[DESCRIPTION] as [STAGENAME],
	avg(cast((s.[LASTSTEPINSTAGEDATETIME] - s.[FIRSTSTEPINSTAGEDATETIME]) as float)) as [AVGSTAGEDURATION],
	min(cast((s.[LASTSTEPINSTAGEDATETIME] - s.[FIRSTSTEPINSTAGEDATETIME]) as float)) as [MINSTAGEDURATION],
	max(cast((s.[LASTSTEPINSTAGEDATETIME] - s.[FIRSTSTEPINSTAGEDATETIME]) as float)) as [MAXSTAGEDURATION]
from [STEPS] as s
inner join [PROSPECTPLANSTATUSCODE] as p on s.[PROSPECTPLANSTATUSCODEID] = p.[ID]
where (s.[ACTUALENDDATETIME] = s.[LASTSTEPINSTAGEDATETIME])
group by p.[DESCRIPTION]
			]]>
		</CreateSQL>

    <CreateSQL ObjectName="dbo.USR_USP_REPORT_PLANSTAGEDURATIONSCONSECUTIVE" ObjectType="SQLStoredProc">
      <![CDATA[
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]     
      ]]>
    </CreateSQL>

    <CreateSQL ObjectName="dbo.USR_USP_REPORT_PLANSTAGEDURATIONSNONCONSECUTIVE" ObjectType="SQLStoredProc">
      <![CDATA[
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]     
      ]]>
    </CreateSQL>
	</DataRetrieval>

  <common:FormMetaData>
    <common:FormFields>
      <common:FormField DataType="Guid" FieldID="PROSPECTPLANTYPECODEID" Caption="Prospect Plan Type Code" Required="true">
        <common:CodeTable CodeTableName="PROSPECTPLANTYPECODE" />
      </common:FormField>
    </common:FormFields>

        <common:WebUIComponent>
            <common:UIModel AssemblyName="Custom.AppFx.PlanStageDurations.UIModel.dll" ClassName="Custom.AppFx.PlanStageDurations.UIModel.ProspectPlanStatusDurations.ProspectPlanStageDurationsReportOLTPVersionUIModel" />
            <common:WebUI>
                <common:ExternalResource Url="browser/htmlforms/ProspectPlanStageDurationsReportOLTPVersion.html" />
            </common:WebUI>
        </common:WebUIComponent>

        

        

        
  </common:FormMetaData>
	
</ReportSpec>