Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
431921	1	"with timePeriods as ( -- sort time frames according to [created_date] per [Id]   Select   dt.TheDate     ,jbs.asset_id     ,min(jbs.created_date) as created_date     ,Isnull(Max(jbs.completed_datetime),getdate()) as completed_datetime     ,count(*) as JobCount     ,ROW_NUMBER() over (partition by jbs.asset_id order by min(jbs.created_date), Isnull(Max(jbs.completed_datetime),getdate())) as rn     from [dbo].[vwJobs] jbs     Join dbo.DateDimension  dt  on       Datediff(day, dt.TheDate , jbs.created_date) <=0         and Datediff(day, dt.TheDate ,  isnull(jbs.completed_datetime, getdate())) >=0      Join [dbo].[vwAssets] ast  on ast.asset_id = jbs.asset_id     --where   jbs.asset_id in( 'TCS_192', 'TCS_3312','TCS_3706','TCS_601')      Where ---jbs.asset_id = 'TCS_2611'      asset_code = 'TCS' --TCS: Traffic Control System'     And jbs.[availability] ='Y' and isnull(ast.deadflag,'') <> 'Y'     Group by dt.TheDate, jbs.asset_id  ), cte as ( -- SQL recursive CTE expression   select -- anchor query    [TheDate], asset_id, [created_date], [completed_datetime], rn, 1 as GroupId   from timePeriods   where rn = 1     union all     select -- recursive sql query     p1.TheDate,     p1.[asset_id],    case    when (p1.[created_date] between p2.[created_date] and p2.[completed_datetime]) then p2.[created_date]    when (p2.[created_date] between p1.[created_date] and p1.[completed_datetime]) then p1.[created_date]    when (p1.[created_date] < p2.[created_date] and p1.[completed_datetime] > p2.[completed_datetime]) then p1.[created_date]    when (p1.[created_date] > p2.[created_date] and p1.[completed_datetime] < p2.[completed_datetime]) then p2.[created_date]    else p2.[created_date]    end as [created_date],      case    when (p1.[completed_datetime] between p2.[created_date] and p2.[completed_datetime]) then p2.[completed_datetime]    when (p2.[completed_datetime] between p1.[created_date] and p1.[completed_datetime]) then p1.[completed_datetime]    when (p1.[created_date] < p2.[created_date] and p1.[completed_datetime] > p2.[completed_datetime]) then p1.[completed_datetime]    when (p1.[created_date] > p2.[created_date] and p1.[completed_datetime] < p2.[completed_datetime]) then p2.[completed_datetime]    else p2.[completed_datetime]    end as [completed_datetime],         p2.rn,    case when    (p1.[created_date] between p2.[created_date] and p2.[completed_datetime]) or    (p1.[completed_datetime] between p2.[created_date] and p2.[completed_datetime]) or    (p1.[created_date] < p2.[created_date] and p1.[completed_datetime] > p2.[completed_datetime]) or    (p1.[created_date] > p2.[created_date] and p1.[completed_datetime] < p2.[completed_datetime])    then    p1.GroupId    else    (p1.GroupId+1)    end as GroupId   from cte p1 -- referencing CTE itself   inner join timePeriods p2    on p1.[asset_id] = p2.[asset_id] and    (p1.rn+1) = p2.rn  )  ---select * from cte order by asset_id, rn  /*select ""KPI No"" = 51,   ""KPI Type"" = 'Availability',   ""KPI Category"" = 'Traffic Control Signals',   TheDate,   [asset_Id],    ---GroupId,    60*24 as DailyTotalTime,   min(created_date) DownTimeStart,    max(completed_datetime) DownTimeEnd,    DATEDIFF(MINUTE, min(created_date), max(completed_datetime)) as AssetDailyDowntimeMinutes  --- string_agg(asset_id,',') within group (order by asset_id) as taskList  from cte  group by thedate,asset_id  order by asset_Id, DownTimeStart*/      Select    ""KPI No""=  51   ,""KPI Type"" = 'Availability'   ,""KPI Category"" = 'Traffic Control Signals'   ,""KPI Name"" = 'Availability Traffic Control Signals'+ ast.[conops_rank]   ,dt.Thedate  --as ReportMonthYear   ,ast.Asset_id   ,ast.Asset_code   ,ast.Asset_Type   ,Conops_Rank   ,60*24 as DailyTotalTime   ,created_date DownTimeStart   ,completed_datetime DownTimeEnd   ,Isnull(DATEDIFF(MINUTE, created_date, completed_datetime),0) as AssetDailyDowntimeMinutes    ---,Isnull(JobCount,0) as JobCount  from [dbo].[vwAssets] ast  Join dbo.DateDimension  dt  on Datediff(day, dt.TheD"	1	1	0	NULL	NULL	NULL	NULL	836.0119	NULL	NULL	NULL	1974.297	NULL	NULL	SELECT	0	NULL
0	0	"  |--Compute Scalar(DEFINE:([Expr1046]=(51), [Expr1047]='Availability', [Expr1048]='Traffic Control Signals', [Expr1050]=(1440), [Expr1051]=isnull(datediff(minute,[Recr1042],[Recr1043]),(0))))"	1	2	1	Compute Scalar	Compute Scalar	"DEFINE:([Expr1046]=(51), [Expr1047]='Availability', [Expr1048]='Traffic Control Signals', [Expr1050]=(1440), [Expr1051]=isnull(datediff(minute,[Recr1042],[Recr1043]),(0)))"	"[Expr1046]=(51), [Expr1047]='Availability', [Expr1048]='Traffic Control Signals', [Expr1050]=(1440), [Expr1051]=isnull(datediff(minute,[Recr1042],[Recr1043]),(0))"	836.0119	0	8.36E-05	243	1974.297	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [dt].[TheDate], [Recr1042], [Recr1043], [Expr1046], [Expr1047], [Expr1048], [Expr1049], [Expr1050], [Expr1051]"	NULL	PLAN_ROW	0	1
431921	1	"       |--Parallelism(Gather Streams, ORDER BY:([srapc].[dbo].[Assets].[asset_id] ASC, [dt].[TheDate] ASC))"	1	3	2	Parallelism	Gather Streams	"ORDER BY:([srapc].[dbo].[Assets].[asset_id] ASC, [dt].[TheDate] ASC)"	NULL	836.0119	0	0.04058727	209	1974.297	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [dt].[TheDate], [Recr1042], [Recr1043], [Expr1049]"	NULL	PLAN_ROW	1	1
431921	4	"            |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Recr1041], [Recr1040])=([srapc].[dbo].[Assets].[asset_id], [dt].[TheDate]), RESIDUAL:([Recr1040]=[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate] AND [Recr1041]=[srapc].[dbo].[Assets].[asset_id]))"	1	4	3	Merge Join	Right Outer Join	"MANY-TO-MANY MERGE:([Recr1041], [Recr1040])=([srapc].[dbo].[Assets].[asset_id], [dt].[TheDate]), RESIDUAL:([Recr1040]=[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate] AND [Recr1041]=[srapc].[dbo].[Assets].[asset_id])"	NULL	836.0119	0.003703137	0.004473122	209	1974.256	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [dt].[TheDate], [Recr1042], [Recr1043], [Expr1049]"	NULL	PLAN_ROW	1	1
416	4	"                 |--Sort(ORDER BY:([Recr1041] ASC, [Recr1040] ASC))"	1	5	4	Sort	Sort	"ORDER BY:([Recr1041] ASC, [Recr1040] ASC)"	NULL	171.1777	0.005630631	0.001040649	55	1936.308	"[Recr1040], [Recr1041], [Recr1042], [Recr1043]"	NULL	PLAN_ROW	1	1
416	4	"                 |    |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Recr1041], [Recr1040]))"	1	6	5	Parallelism	Distribute Streams	"PARTITION COLUMNS:([Recr1041], [Recr1040])"	NULL	171.1777	0	0.02990721	55	1936.302	"[Recr1040], [Recr1041], [Recr1042], [Recr1043]"	NULL	PLAN_ROW	1	1
416	1	                 |         |--Index Spool(WITH STACK)	1	7	6	Index Spool	Lazy Spool	WITH STACK	NULL	171.1777	0	8.46E-07	55	1936.272	"[Expr1080], [Recr1040], [Recr1041], [Recr1042], [Recr1043], [Recr1044], [Recr1045]"	NULL	PLAN_ROW	0	1
416	1	                 |              |--Concatenation	1	8	7	Concatenation	Concatenation	NULL	"[Expr1080] = ([Expr1077], [Expr1079]), [Recr1040] = ([dt].[TheDate], [Recr1018]), [Recr1041] = ([srapc].[dbo].[Jobs].[asset_id], [Recr1019]), [Recr1042] = ([Expr1011], [Expr1037]), [Recr1043] = ([Expr1016], [Expr1038]), [Recr1044] = ([Expr1015], [Expr1035]), [Recr1045] = ([Expr1017], [Expr1039])"	171.1777	0	1.69E-07	55	1925.106	"[Expr1080], [Recr1040], [Recr1041], [Recr1042], [Recr1043], [Recr1044], [Recr1045]"	NULL	PLAN_ROW	0	1
0	0	                 |                   |--Compute Scalar(DEFINE:([Expr1077]=(0)))	1	9	8	Compute Scalar	Compute Scalar	DEFINE:([Expr1077]=(0))	[Expr1077]=(0)	1	0	1.69E-06	55	1.69E-06	"[Expr1077], [dt].[TheDate], [srapc].[dbo].[Jobs].[asset_id], [Expr1011], [Expr1016], [Expr1015], [Expr1017]"	NULL	PLAN_ROW	0	172.1777
0	0	"                 |                   |    |--Compute Scalar(DEFINE:([Expr1016]=isnull([Expr1012],getdate()), [Expr1017]=(1)))"	1	10	9	Compute Scalar	Compute Scalar	"DEFINE:([Expr1016]=isnull([Expr1012],getdate()), [Expr1017]=(1))"	"[Expr1016]=isnull([Expr1012],getdate()), [Expr1017]=(1)"	168.1777	0	1.68E-05	52	12.84005	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1015], [Expr1016], [Expr1017]"	NULL	PLAN_ROW	0	1
235	1	                 |                   |         |--Filter(WHERE:([Expr1015]=(1)))	1	11	10	Filter	Filter	WHERE:([Expr1015]=(1))	NULL	168.1777	0	8.07E-05	48	12.84004	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1012], [Expr1015]"	NULL	PLAN_ROW	0	1
416	1	                 |                   |              |--Sequence Project(DEFINE:([Expr1015]=row_number))	1	12	11	Sequence Project	Compute Scalar	DEFINE:([Expr1015]=row_number)	[Expr1015]=row_number	168.1777	0	1.35E-05	48	12.83996	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1012], [Expr1015]"	NULL	PLAN_ROW	0	1
416	1	                 |                   |                   |--Segment	1	13	12	Segment	Segment	[srapc].[dbo].[Jobs].[asset_id]	NULL	168.1777	0	3.36E-06	48	12.83994	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1012], [Expr1014], [Segment1074]"	NULL	PLAN_ROW	0	1
416	1	"                 |                   |                        |--Sort(ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [Expr1011] ASC, [Expr1014] ASC))"	1	14	13	Sort	Sort	"ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [Expr1011] ASC, [Expr1014] ASC)"	NULL	168.1777	0.01126126	0.002039876	48	12.83994	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1012], [Expr1014]"	NULL	PLAN_ROW	0	1
0	0	"                 |                   |                             |--Compute Scalar(DEFINE:([Expr1014]=isnull([Expr1012],getdate())))"	1	15	14	Compute Scalar	Compute Scalar	"DEFINE:([Expr1014]=isnull([Expr1012],getdate()))"	"[Expr1014]=isnull([Expr1012],getdate())"	168.1777	0	1.68E-05	48	12.82664	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1012], [Expr1014]"	NULL	PLAN_ROW	0	1
416	1	"                 |                   |                                  |--Stream Aggregate(GROUP BY:([srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate]) DEFINE:([Expr1011]=MIN([srapc].[dbo].[Jobs].[created_date]), [Expr1012]=MAX([srapc].[dbo].[Jobs].[completed_datetime])))"	1	16	15	Stream Aggregate	Aggregate	"GROUP BY:([srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate])"	"[Expr1011]=MIN([srapc].[dbo].[Jobs].[created_date]), [Expr1012]=MAX([srapc].[dbo].[Jobs].[completed_datetime])"	168.1777	0	0.000184996	40	12.82662	"[srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate], [Expr1011], [Expr1012]"	NULL	PLAN_ROW	0	1
445	1	"                 |                   |                                       |--Sort(ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [dt].[TheDate] ASC))"	1	17	16	Sort	Sort	"ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [dt].[TheDate] ASC)"	NULL	168.1786	0.01126126	0.002039881	40	12.82644	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [dt].[TheDate]"	NULL	PLAN_ROW	0	1
445	1	"                 |                   |                                            |--Nested Loops(Inner Join, WHERE:(datediff(day,[Expr1063],[Expr1062])<=(0) AND datediff(day,[Expr1063],CONVERT_IMPLICIT(datetimeoffset(3),isnull([srapc].[dbo].[Jobs].[completed_datetime],getdate()),0))>=(0)))"	1	18	17	Nested Loops	Inner Join	"WHERE:(datediff(day,[Expr1063],[Expr1062])<=(0) AND datediff(day,[Expr1063],CONVERT_IMPLICIT(datetimeoffset(3),isnull([srapc].[dbo].[Jobs].[completed_datetime],getdate()),0))>=(0))"	NULL	168.1786	0	7.702625	40	12.81313	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [dt].[TheDate]"	NULL	PLAN_ROW	0	1
408	1	"                 |                   |                                                 |--Hash Match(Inner Join, HASH:([srapc].[dbo].[Jobs].[asset_id])=([srapc].[dbo].[Assets].[asset_id]), RESIDUAL:([srapc].[dbo].[Jobs].[asset_id]=[srapc].[dbo].[Assets].[asset_id]))"	1	19	18	Hash Match	Inner Join	"HASH:([srapc].[dbo].[Jobs].[asset_id])=([srapc].[dbo].[Assets].[asset_id]), RESIDUAL:([srapc].[dbo].[Jobs].[asset_id]=[srapc].[dbo].[Assets].[asset_id])"	NULL	168.1786	0	0.0543449	46	1.570899	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [Expr1062]"	NULL	PLAN_ROW	0	1
0	0	"                 |                   |                                                 |    |--Compute Scalar(DEFINE:([Expr1062]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Jobs].[created_date],0)))"	1	20	19	Compute Scalar	Compute Scalar	"DEFINE:([Expr1062]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Jobs].[created_date],0))"	"[Expr1062]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Jobs].[created_date],0)"	667	0	6.67E-05	46	0.6507695	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [Expr1062]"	NULL	PLAN_ROW	0	1
667	1	"                 |                   |                                                 |    |    |--Clustered Index Scan(OBJECT:([srapc].[dbo].[Jobs].[ClusteredIndex-20221102-114402]), WHERE:([srapc].[dbo].[Jobs].[availability]='Y'))"	1	21	20	Clustered Index Scan	Clustered Index Scan	"OBJECT:([srapc].[dbo].[Jobs].[ClusteredIndex-20221102-114402]), WHERE:([srapc].[dbo].[Jobs].[availability]='Y')"	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime]"	667	0.6275694	0.0161532	49	0.6437227	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime]"	NULL	PLAN_ROW	0	1
862	1	"                 |                   |                                                 |    |--Clustered Index Scan(OBJECT:([srapc].[dbo].[Assets].[ClusteredIndex-asset_id_code]), WHERE:([srapc].[dbo].[Assets].[asset_code]='TCS' AND isnull([srapc].[dbo].[Assets].[deadflag],'')<>'Y'))"	1	23	19	Clustered Index Scan	Clustered Index Scan	"OBJECT:([srapc].[dbo].[Assets].[ClusteredIndex-asset_id_code]), WHERE:([srapc].[dbo].[Assets].[asset_code]='TCS' AND isnull([srapc].[dbo].[Assets].[deadflag],'')<>'Y')"	[srapc].[dbo].[Assets].[asset_id]	860.5111	0.7557176	0.058281	50	0.8139986	[srapc].[dbo].[Assets].[asset_id]	NULL	PLAN_ROW	0	1
4470456	408	                 |                   |                                                 |--Table Spool	1	24	18	Table Spool	Lazy Spool	NULL	NULL	10957	0.01	0.00207246	20	0.5912368	"[dt].[TheDate], [Expr1063]"	NULL	PLAN_ROW	0	168.1786
0	0	"                 |                   |                                                      |--Compute Scalar(DEFINE:([Expr1063]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0)))"	1	25	24	Compute Scalar	Compute Scalar	"DEFINE:([Expr1063]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0))"	"[Expr1063]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0)"	10957	0	0.0010957	20	0.2327267	"[dt].[TheDate], [Expr1063]"	NULL	PLAN_ROW	0	1
10957	1	                 |                   |                                                           |--Clustered Index Scan(OBJECT:([srapc].[dbo].[DateDimension].[PK_DateDimension] AS [dt]))	1	26	25	Clustered Index Scan	Clustered Index Scan	OBJECT:([srapc].[dbo].[DateDimension].[PK_DateDimension] AS [dt])	[dt].[TheDate]	10957	0.2194213	0.0122097	10	0.231631	[dt].[TheDate]	NULL	PLAN_ROW	0	1
181	1	                 |                   |--Assert(WHERE:(CASE WHEN [Expr1079]>(100) THEN (0) ELSE NULL END))	1	27	8	Assert	Assert	WHERE:(CASE WHEN [Expr1079]>(100) THEN (0) ELSE NULL END)	NULL	1.017838	0	1.42E-05	55	1912.266	"[Expr1079], [Recr1018], [Recr1019], [Expr1037], [Expr1038], [Expr1035], [Expr1039]"	NULL	PLAN_ROW	0	172.1777
181	1	"                 |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1079], [Recr1018], [Recr1019], [Recr1020], [Recr1021], [Recr1022], [Recr1023]))"	1	28	27	Nested Loops	Inner Join	"OUTER REFERENCES:([Expr1079], [Recr1018], [Recr1019], [Recr1020], [Recr1021], [Recr1022], [Recr1023])"	NULL	1.017838	0	1.42E-05	55	1912.266	"[Expr1079], [Recr1018], [Recr1019], [Expr1037], [Expr1038], [Expr1035], [Expr1039]"	NULL	PLAN_ROW	0	172.1777
0	0	                 |                             |--Compute Scalar(DEFINE:([Expr1079]=[Expr1078]+(1)))	1	29	28	Compute Scalar	Compute Scalar	DEFINE:([Expr1079]=[Expr1078]+(1))	[Expr1079]=[Expr1078]+(1)	1	0	1.69E-06	55	1.69E-06	"[Expr1079], [Recr1018], [Recr1019], [Recr1020], [Recr1021], [Recr1022], [Recr1023]"	NULL	PLAN_ROW	0	172.1777
416	1	                 |                             |    |--Table Spool(WITH STACK)	1	30	29	Table Spool	Lazy Spool	WITH STACK	NULL	1	0	1.69E-06	55	1.69E-06	"[Expr1078], [Recr1018], [Recr1019], [Recr1020], [Recr1021], [Recr1022], [Recr1023]"	NULL	PLAN_ROW	0	172.1777
0	0	"                 |                             |--Compute Scalar(DEFINE:([Expr1037]=CASE WHEN [Recr1020]>=[Expr1031] AND [Recr1020]<=isnull([Expr1032],getdate()) THEN [Expr1031] ELSE CASE WHEN [Expr1031]>=[Recr1020] AND [Expr1031]<=[Recr1021] THEN [Recr1020] ELSE CASE WHEN [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) THEN [Recr1020] ELSE [Expr1031] END END END, [Expr1038]=CASE WHEN [Recr1021]>=[Expr1031] AND [Recr1021]<=isnull([Expr1032],getdate()) THEN isnull([Expr1032],getdate()) ELSE CASE WHEN isnull([Expr1032],getdate())>=[Recr1020] AND isnull([Expr1032],getdate())<=[Recr1021] THEN [Recr1021] ELSE CASE WHEN [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) THEN [Recr1021] ELSE isnull([Expr1032],getdate()) END END END, [Expr1039]=CASE WHEN [Recr1020]>=[Expr1031] AND [Recr1020]<=isnull([Expr1032],getdate()) OR [Recr1021]>=[Expr1031] AND [Recr1021]<=isnull([Expr1032],getdate()) OR [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) OR [Recr1020]>[Expr1031] AND [Recr1021]<isnull([Expr1032],getdate()) THEN [Recr1023] ELSE [Recr1023]+(1) END))"	1	31	28	Compute Scalar	Compute Scalar	"DEFINE:([Expr1037]=CASE WHEN [Recr1020]>=[Expr1031] AND [Recr1020]<=isnull([Expr1032],getdate()) THEN [Expr1031] ELSE CASE WHEN [Expr1031]>=[Recr1020] AND [Expr1031]<=[Recr1021] THEN [Recr1020] ELSE CASE WHEN [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) THEN [Recr1020] ELSE [Expr1031] END END END, [Expr1038]=CASE WHEN [Recr1021]>=[Expr1031] AND [Recr1021]<=isnull([Expr1032],getdate()) THEN isnull([Expr1032],getdate()) ELSE CASE WHEN isnull([Expr1032],getdate())>=[Recr1020] AND isnull([Expr1032],getdate())<=[Recr1021] THEN [Recr1021] ELSE CASE WHEN [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) THEN [Recr1021] ELSE isnull([Expr1032],getdate()) END END END, [Expr1039]=CASE WHEN [Recr1020]>=[Expr1031] AND [Recr1020]<=isnull([Expr1032],getdate()) OR [Recr1021]>=[Expr1031] AND [Recr1021]<=isnull([Expr1032],getdate()) OR [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) OR [Recr1020]>[Expr1031] AND [Recr1021]<isnull([Expr1032],getdate()) THEN [Recr1023] ELSE [Recr1023]+(1) END)"	"[Expr1037]=CASE WHEN [Recr1020]>=[Expr1031] AND [Recr1020]<=isnull([Expr1032],getdate()) THEN [Expr1031] ELSE CASE WHEN [Expr1031]>=[Recr1020] AND [Expr1031]<=[Recr1021] THEN [Recr1020] ELSE CASE WHEN [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) THEN [Recr1020] ELSE [Expr1031] END END END, [Expr1038]=CASE WHEN [Recr1021]>=[Expr1031] AND [Recr1021]<=isnull([Expr1032],getdate()) THEN isnull([Expr1032],getdate()) ELSE CASE WHEN isnull([Expr1032],getdate())>=[Recr1020] AND isnull([Expr1032],getdate())<=[Recr1021] THEN [Recr1021] ELSE CASE WHEN [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) THEN [Recr1021] ELSE isnull([Expr1032],getdate()) END END END, [Expr1039]=CASE WHEN [Recr1020]>=[Expr1031] AND [Recr1020]<=isnull([Expr1032],getdate()) OR [Recr1021]>=[Expr1031] AND [Recr1021]<=isnull([Expr1032],getdate()) OR [Recr1020]<[Expr1031] AND [Recr1021]>isnull([Expr1032],getdate()) OR [Recr1020]>[Expr1031] AND [Recr1021]<isnull([Expr1032],getdate()) THEN [Recr1023] ELSE [Recr1023]+(1) END"	1	0	1.00E-07	35	1912.266	"[Expr1035], [Expr1037], [Expr1038], [Expr1039]"	NULL	PLAN_ROW	0	171.1777
181	416	                 |                                  |--Filter(WHERE:([Recr1019]=[srapc].[dbo].[Jobs].[asset_id] AND ([Recr1022]+(1))=[Expr1035]))	1	32	31	Filter	Filter	WHERE:([Recr1019]=[srapc].[dbo].[Jobs].[asset_id] AND ([Recr1022]+(1))=[Expr1035])	NULL	1	0	0.000181632	31	1912.266	"[Expr1031], [Expr1032], [Expr1035]"	NULL	PLAN_ROW	0	171.1777
173056	416	                 |                                       |--Sequence Project(DEFINE:([Expr1035]=row_number))	1	33	32	Sequence Project	Compute Scalar	DEFINE:([Expr1035]=row_number)	[Expr1035]=row_number	168.1777	0	1.35E-05	60	1912.235	"[srapc].[dbo].[Jobs].[asset_id], [Expr1031], [Expr1032], [Expr1035]"	NULL	PLAN_ROW	0	171.1777
173056	416	                 |                                            |--Segment	1	34	33	Segment	Segment	[srapc].[dbo].[Jobs].[asset_id]	NULL	168.1777	0	3.36E-06	60	1912.232	"[srapc].[dbo].[Jobs].[asset_id], [Expr1031], [Expr1032], [Expr1034], [Segment1076]"	NULL	PLAN_ROW	0	171.1777
173056	416	"                 |                                                 |--Sort(ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [Expr1031] ASC, [Expr1034] ASC))"	1	35	34	Sort	Sort	"ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [Expr1031] ASC, [Expr1034] ASC)"	NULL	168.1777	0.01126126	0.002039888	60	1912.232	"[srapc].[dbo].[Jobs].[asset_id], [Expr1031], [Expr1032], [Expr1034]"	NULL	PLAN_ROW	0	171.1777
0	0	"                 |                                                      |--Compute Scalar(DEFINE:([Expr1034]=isnull([Expr1032],getdate())))"	1	36	35	Compute Scalar	Compute Scalar	"DEFINE:([Expr1034]=isnull([Expr1032],getdate()))"	"[Expr1034]=isnull([Expr1032],getdate())"	168.1777	0	1.68E-05	60	1909.955	"[srapc].[dbo].[Jobs].[asset_id], [Expr1031], [Expr1032], [Expr1034]"	NULL	PLAN_ROW	0	171.1777
173056	416	"                 |                                                           |--Stream Aggregate(GROUP BY:([srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate]) DEFINE:([Expr1031]=MIN([srapc].[dbo].[Jobs].[created_date]), [Expr1032]=MAX([srapc].[dbo].[Jobs].[completed_datetime])))"	1	37	36	Stream Aggregate	Aggregate	"GROUP BY:([srapc].[dbo].[Jobs].[asset_id], [dt].[TheDate])"	"[Expr1031]=MIN([srapc].[dbo].[Jobs].[created_date]), [Expr1032]=MAX([srapc].[dbo].[Jobs].[completed_datetime])"	168.1777	0	0.000184996	52	1909.952	"[srapc].[dbo].[Jobs].[asset_id], [Expr1031], [Expr1032]"	NULL	PLAN_ROW	0	171.1777
185120	416	"                 |                                                                |--Sort(ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [dt].[TheDate] ASC))"	1	38	37	Sort	Sort	"ORDER BY:([srapc].[dbo].[Jobs].[asset_id] ASC, [dt].[TheDate] ASC)"	NULL	168.1786	0.01126126	0.002039896	55	1909.92	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [dt].[TheDate]"	NULL	PLAN_ROW	0	171.1777
185120	416	"                 |                                                                     |--Nested Loops(Inner Join, WHERE:(datediff(day,[Expr1066],[Expr1065])<=(0) AND datediff(day,[Expr1066],CONVERT_IMPLICIT(datetimeoffset(3),isnull([srapc].[dbo].[Jobs].[completed_datetime],getdate()),0))>=(0)))"	1	39	38	Nested Loops	Inner Join	"WHERE:(datediff(day,[Expr1066],[Expr1065])<=(0) AND datediff(day,[Expr1066],CONVERT_IMPLICIT(datetimeoffset(3),isnull([srapc].[dbo].[Jobs].[completed_datetime],getdate()),0))>=(0))"	NULL	168.1786	0	7.702625	55	1907.643	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [dt].[TheDate]"	NULL	PLAN_ROW	0	171.1777
169728	416	"                 |                                                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([srapc].[dbo].[Jobs].[asset_id], [Expr1075]) WITH UNORDERED PREFETCH)"	1	41	39	Nested Loops	Inner Join	"OUTER REFERENCES:([srapc].[dbo].[Jobs].[asset_id], [Expr1075]) WITH UNORDERED PREFETCH"	NULL	168.1786	0	0.00278806	61	24.52718	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [Expr1065]"	NULL	PLAN_ROW	0	171.1777
0	0	"                 |                                                                          |    |--Compute Scalar(DEFINE:([Expr1065]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Jobs].[created_date],0)))"	1	43	41	Compute Scalar	Compute Scalar	"DEFINE:([Expr1065]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Jobs].[created_date],0))"	"[Expr1065]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Jobs].[created_date],0)"	667	0	6.67E-05	61	4.598903	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime], [Expr1065]"	NULL	PLAN_ROW	0	171.1777
277472	416	"                 |                                                                          |    |    |--Clustered Index Scan(OBJECT:([srapc].[dbo].[Jobs].[ClusteredIndex-20221102-114402]), WHERE:([srapc].[dbo].[Jobs].[availability]='Y'))"	1	44	43	Clustered Index Scan	Clustered Index Scan	"OBJECT:([srapc].[dbo].[Jobs].[ClusteredIndex-20221102-114402]), WHERE:([srapc].[dbo].[Jobs].[availability]='Y')"	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime]"	667	0.6275694	0.0161532	64	3.392637	"[srapc].[dbo].[Jobs].[asset_id], [srapc].[dbo].[Jobs].[created_date], [srapc].[dbo].[Jobs].[completed_datetime]"	NULL	PLAN_ROW	0	171.1777
169728	277472	"                 |                                                                          |    |--Clustered Index Seek(OBJECT:([srapc].[dbo].[Assets].[ClusteredIndex-asset_id_code]), SEEK:([srapc].[dbo].[Assets].[asset_id]=[srapc].[dbo].[Jobs].[asset_id] AND [srapc].[dbo].[Assets].[asset_code]='TCS'),  WHERE:(isnull([srapc].[dbo].[Assets].[deadflag],'')<>'Y') ORDERED FORWARD)"	1	45	41	Clustered Index Seek	Clustered Index Seek	"OBJECT:([srapc].[dbo].[Assets].[ClusteredIndex-asset_id_code]), SEEK:([srapc].[dbo].[Assets].[asset_id]=[srapc].[dbo].[Jobs].[asset_id] AND [srapc].[dbo].[Assets].[asset_code]='TCS'),  WHERE:(isnull([srapc].[dbo].[Assets].[deadflag],'')<>'Y') ORDERED FORWARD"	NULL	1	0.003125	0.0001581	21	19.37338	NULL	NULL	PLAN_ROW	0	114175.5
1859709696	169728	                 |                                                                          |--Table Spool	1	46	39	Table Spool	Lazy Spool	NULL	NULL	10957	0.01	0.00207246	20	59.89985	"[dt].[TheDate], [Expr1066]"	NULL	PLAN_ROW	0	28788.43
0	0	"                 |                                                                               |--Compute Scalar(DEFINE:([Expr1066]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0)))"	1	47	46	Compute Scalar	Compute Scalar	"DEFINE:([Expr1066]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0))"	"[Expr1066]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0)"	10957	0	0.0010957	20	0.2327267	"[dt].[TheDate], [Expr1066]"	NULL	PLAN_ROW	0	1
10957	1	                 |                                                                                    |--Clustered Index Scan(OBJECT:([srapc].[dbo].[DateDimension].[PK_DateDimension] AS [dt]))	1	48	47	Clustered Index Scan	Clustered Index Scan	OBJECT:([srapc].[dbo].[DateDimension].[PK_DateDimension] AS [dt])	[dt].[TheDate]	10957	0.2194213	0.0122097	10	0.231631	[dt].[TheDate]	NULL	PLAN_ROW	0	1
431745	4	"                 |--Sort(ORDER BY:([srapc].[dbo].[Assets].[asset_id] ASC, [dt].[TheDate] ASC))"	1	49	4	Sort	Sort	"ORDER BY:([srapc].[dbo].[Assets].[asset_id] ASC, [dt].[TheDate] ASC)"	NULL	867	0.005630631	0.006650318	193	37.93982	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [dt].[TheDate], [Expr1049]"	NULL	PLAN_ROW	1	1
431745	4	"                      |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([srapc].[dbo].[Assets].[asset_id], [dt].[TheDate]))"	1	50	49	Parallelism	Repartition Streams	"PARTITION COLUMNS:([srapc].[dbo].[Assets].[asset_id], [dt].[TheDate])"	NULL	867	0	0.03661024	193	37.92753	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [dt].[TheDate], [Expr1049]"	NULL	PLAN_ROW	1	1
431745	4	"                           |--Nested Loops(Inner Join, WHERE:(datediff(day,[Expr1060],[Expr1059])<=(0) AND datediff(day,[Expr1060],CONVERT_IMPLICIT(datetimeoffset(3),isnull([Expr1061],getdate()),0))>=(0)))"	1	51	50	Nested Loops	Inner Join	"WHERE:(datediff(day,[Expr1060],[Expr1059])<=(0) AND datediff(day,[Expr1060],CONVERT_IMPLICIT(datetimeoffset(3),isnull([Expr1061],getdate()),0))>=(0))"	NULL	867	0	19.85441	193	37.89092	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [dt].[TheDate], [Expr1049]"	NULL	PLAN_ROW	1	1
867	4	"                                |--Parallelism(Repartition Streams, RoundRobin Partitioning)"	1	52	51	Parallelism	Repartition Streams	NULL	NULL	867	0	0.0364311	207	0.8340141	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [Expr1049], [Expr1059], [Expr1061]"	NULL	PLAN_ROW	1	1
0	0	"                                |    |--Compute Scalar(DEFINE:([Expr1049]='Availability Traffic Control Signals'+[srapc].[dbo].[Assets].[conops_rank], [Expr1059]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Assets].[feature_start_date],0), [Expr1061]=CASE WHEN [srapc].[dbo].[Assets].[feature_end_date]='3000-01-01 00:00:00.000' THEN NULL ELSE [srapc].[dbo].[Assets].[feature_end_date] END))"	1	53	52	Compute Scalar	Compute Scalar	"DEFINE:([Expr1049]='Availability Traffic Control Signals'+[srapc].[dbo].[Assets].[conops_rank], [Expr1059]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Assets].[feature_start_date],0), [Expr1061]=CASE WHEN [srapc].[dbo].[Assets].[feature_end_date]='3000-01-01 00:00:00.000' THEN NULL ELSE [srapc].[dbo].[Assets].[feature_end_date] END)"	"[Expr1049]='Availability Traffic Control Signals'+[srapc].[dbo].[Assets].[conops_rank], [Expr1059]=CONVERT_IMPLICIT(datetimeoffset(3),[srapc].[dbo].[Assets].[feature_start_date],0), [Expr1061]=CASE WHEN [srapc].[dbo].[Assets].[feature_end_date]='3000-01-01 00:00:00.000' THEN NULL ELSE [srapc].[dbo].[Assets].[feature_end_date] END"	867	0	4.34E-05	207	0.797583	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [Expr1049], [Expr1059], [Expr1061]"	NULL	PLAN_ROW	1	1
867	4	"                                |         |--Clustered Index Scan(OBJECT:([srapc].[dbo].[Assets].[ClusteredIndex-asset_id_code]), WHERE:([srapc].[dbo].[Assets].[asset_code]='TCS'))"	1	54	53	Clustered Index Scan	Clustered Index Scan	"OBJECT:([srapc].[dbo].[Assets].[ClusteredIndex-asset_id_code]), WHERE:([srapc].[dbo].[Assets].[asset_code]='TCS')"	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [srapc].[dbo].[Assets].[feature_start_date], [srapc].[dbo].[Assets].[feature_end_date]"	867	0.7557176	0.0291405	161	0.7848581	"[srapc].[dbo].[Assets].[asset_id], [srapc].[dbo].[Assets].[asset_code], [srapc].[dbo].[Assets].[asset_type], [srapc].[dbo].[Assets].[conops_rank], [srapc].[dbo].[Assets].[feature_start_date], [srapc].[dbo].[Assets].[feature_end_date]"	NULL	PLAN_ROW	1	1
9499719	867	                                |--Table Spool	1	55	51	Table Spool	Lazy Spool	NULL	NULL	10957	0.01	0.00207246	20	2.039376	"[dt].[TheDate], [Expr1060]"	NULL	PLAN_ROW	1	867
0	0	"                                     |--Compute Scalar(DEFINE:([Expr1060]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0)))"	1	56	55	Compute Scalar	Compute Scalar	"DEFINE:([Expr1060]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0))"	"[Expr1060]=CONVERT_IMPLICIT(datetimeoffset(7),[srapc].[dbo].[DateDimension].[TheDate] as [dt].[TheDate],0)"	10957	0	0.0010957	20	0.2327267	"[dt].[TheDate], [Expr1060]"	NULL	PLAN_ROW	1	1
43828	4	                                          |--Clustered Index Scan(OBJECT:([srapc].[dbo].[DateDimension].[PK_DateDimension] AS [dt]))	1	57	56	Clustered Index Scan	Clustered Index Scan	OBJECT:([srapc].[dbo].[DateDimension].[PK_DateDimension] AS [dt])	[dt].[TheDate]	10957	0.2194213	0.0122097	10	0.231631	[dt].[TheDate]	NULL	PLAN_ROW	1	1