Skip to content

Instantly share code, notes, and snippets.

@jfrux

jfrux/execution_plan

Last active December 14, 2015 02:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jfrux/5015679 to your computer and use it in GitHub Desktop.
Save jfrux/5015679 to your computer and use it in GitHub Desktop.
Download ZIP
trying to improve this query... :-/
Raw
execution_plan
|--Compute Scalar(DEFINE:([Expr1015]=isnull(CASE WHEN [Expr1118]=N'M' THEN isnull([Expr1007],(0.000000000000000e+000)) ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN isnull([Expr1013],(0.000000000000000e+000)) ELSE NULL END END,(0.000000000000000e+000)), [Expr1034]=isnull(CASE WHEN [Expr1118]=N'M' THEN CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[ParentActivityID] as [A].[ParentActivityID],(0))=(0) THEN [Expr1020] ELSE [Expr1026] END ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [Expr1032] ELSE NULL END END,(0)), [Expr1053]=isnull(CASE WHEN [Expr1118]=N'M' THEN CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[ParentActivityID] as [A].[ParentActivityID],(0))=(0) THEN [Expr1039] ELSE [Expr1045] END ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [Expr1051] ELSE NULL END END,(0)), [Expr1079]=CONVERT_IMPLICIT(float(53),isnull(CASE WHEN [Expr1118]=N'M' THEN CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[ParentActivityID] as [A].[ParentActivityID],(0))=(0) THEN [Expr1058] ELSE [Expr1064] END ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [Expr1070] ELSE NULL END END,(0)),0)+isnull(CASE WHEN [Expr1118]=N'M' THEN CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[ParentActivityID] as [A].[ParentActivityID],(0))=(0) THEN isnull([Expr1074],(0.000000000000000e+000)) ELSE [Expr1078] END ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [CCPD_PROD].[ceschema].[ce_Activity].[StatAddlAttendees] as [A].[StatAddlAttendees] ELSE NULL END END,(0.000000000000000e+000)), [Expr1091]=isnull(CASE WHEN [Expr1118]=N'M' THEN [Expr1084] ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [Expr1089] ELSE NULL END END,(0)), [Expr1103]=CASE WHEN isnull(CASE WHEN [Expr1118]=N'M' THEN [Expr1096] ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [Expr1101] ELSE NULL END END,(0))=(0) THEN 'No' ELSE 'Yes' END, [Expr1114]=isnull(CASE WHEN [Expr1118]=N'M' THEN [Expr1108] ELSE CASE WHEN isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S')=N'S' THEN [Expr1112] ELSE NULL END END,(0.000000000000000e+000))))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
|--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M'), OUTER REFERENCES:([A].[ActivityID]))
| |--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
| | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M'), OUTER REFERENCES:([A].[ActivityID]))
| | | |--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
| | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M'), OUTER REFERENCES:([A].[ActivityID]))
| | | | | |--Nested Loops(Left Outer Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR IsFalseOrNull [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | |--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR IsFalseOrNull [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR IsFalseOrNull [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M' OR IsFalseOrNull [Expr1119]=(0)), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:([Expr1118]=N'M' OR IsFalseOrNull [Expr1118]=N'S'), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Expr1118]=N'M'), OUTER REFERENCES:([A].[ActivityID]))
| | | | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1002]=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A].[EndDate]), [Expr1118]=isnull([CCPD_PROD].[ceschema].[ce_Activity].[SessionType] as [A].[SessionType],N'S'), [Expr1119]=isnull([CCPD_PROD].[ceschema].[ce_Activity].[ParentActivityID] as [A].[ParentActivityID],(0))))
| | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A]), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StartDate] as [A].[StartDate]>'2007-01-01 00:00:00.000' AND [CCPD_PROD].[ceschema].[ce_Activity].[refreshFlag] as [A].[refreshFlag]=(1) AND [CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A].[DeletedFlag]=N'N' AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A].[StatusID]=(3))))
| | | | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Expr1183]=(0) THEN NULL ELSE [Expr1184] END))
| | | | | | | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1183]=Count(*), [Expr1184]=SUM([CCPD_PROD].[ceschema].[ce_Activity_Credit].[Amount] as [AC].[Amount])))
| | | | | | | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity_Credit].[CreditID] as [AC].[CreditID]=(1) AND [CCPD_PROD].[ceschema].[ce_Activity_Credit].[DeletedFlag] as [AC].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([AC].[Activity_CreditID]))
| | | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A4].[ActivityID]))
| | | | | | | | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A4].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A4].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A4].[StatusID]=(3)))
| | | | | | | | | | | | | | | | | | | | |--Index Spool(SEEK:([A4].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A4]))
| | | | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_Credit].[IX_ACTIVITY] AS [AC]), SEEK:([AC].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A4].[ActivityID]) ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_Credit].[PK_ce_CourseCredit] AS [AC]), SEEK:([AC].[Activity_CreditID]=[CCPD_PROD].[ceschema].[ce_Activity_Credit].[Activity_CreditID] as [AC].[Activity_CreditID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [Expr1185]=(0) THEN NULL ELSE [Expr1186] END))
| | | | | | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1185]=Count(*), [Expr1186]=SUM([CCPD_PROD].[ceschema].[ce_Activity_Credit].[Amount] as [AC].[Amount])))
| | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A4].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A4].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A4].[StatusID]=(3)))
| | | | | | | | | | | | | | | | | | |--Index Spool(SEEK:([A4].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STATUS] AS [A4]))
| | | | | | | | | | | | | | | | | |--Index Spool(SEEK:([AC].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [AC].[CreditID]=(1) AND [AC].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_Credit].[PK_ce_CourseCredit] AS [AC]))
| | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1020]=CONVERT_IMPLICIT(int,[Expr1187],0)))
| | | | | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1187]=Count(*)))
| | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID], [A2].[StartDate], [Expr1120]))
| | | | | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3)))
| | | | | | | | | | | | | | | | | |--Index Spool(SEEK:([A2].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1120]=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate])))
| | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]))
| | | | | | | | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID] AND [Att].[CompleteDate] >= [CCPD_PROD].[ceschema].[ce_Activity].[StartDate] as [A2].[StartDate] AND [Att].[CompleteDate] <= [Expr1120]))
| | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]), WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[StatusID] as [Att].[StatusID]=(1) AND [CCPD_PROD].[ceschema].[ce_Attendee].[DeletedFlag] as [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1026]=CONVERT_IMPLICIT(int,[Expr1189],0)))
| | | | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1189]=Count(*)))
| | | | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate]<=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate]) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3))))
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID], [Expr1188]) WITH UNORDERED PREFETCH)
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Att].[CompleteDate]))
| | | | | | | | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [Att].[StatusID]=(1) AND [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]))
| | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STARTDATE] AS [A2]), SEEK:(([A2].[StartDate], [A2].[ActivityID]) <= ([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate], [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID])), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]), SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1032]=CONVERT_IMPLICIT(int,[Expr1191],0)))
| | | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1191]=Count(*)))
| | | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate]<=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate]) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3))))
| | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID], [Expr1190]) WITH UNORDERED PREFETCH)
| | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Att].[CompleteDate]))
| | | | | | | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [Att].[StatusID]=(1) AND [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]))
| | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STARTDATE] AS [A2]), SEEK:(([A2].[StartDate], [A2].[ActivityID]) <= ([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate], [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID])), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]), SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1039]=CONVERT_IMPLICIT(int,[Expr1192],0)))
| | | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1192]=Count(*)))
| | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID], [A2].[StartDate], [Expr1123]))
| | | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3)))
| | | | | | | | | | | | | | |--Index Spool(SEEK:([A2].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1123]=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate])))
| | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]))
| | | | | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID] AND [Att].[CompleteDate] >= [CCPD_PROD].[ceschema].[ce_Activity].[StartDate] as [A2].[StartDate] AND [Att].[CompleteDate] <= [Expr1123]))
| | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]), WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[StatusID] as [Att].[StatusID]=(1) AND [CCPD_PROD].[ceschema].[ce_Attendee].[MDflag] as [Att].[MDflag]=N'Y' AND [CCPD_PROD].[ceschema].[ce_Attendee].[DeletedFlag] as [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1045]=CONVERT_IMPLICIT(int,[Expr1193],0)))
| | | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1193]=Count(*)))
| | | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate]<=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate]) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3))))
| | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID]))
| | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Att].[CompleteDate]))
| | | | | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [Att].[StatusID]=(1) AND [Att].[MDflag]=N'Y' AND [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]))
| | | | | | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STARTDATE] AS [A2]), SEEK:(([A2].[StartDate], [A2].[ActivityID]) <= ([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate], [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID])), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]), SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1051]=CONVERT_IMPLICIT(int,[Expr1194],0)))
| | | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1194]=Count(*)))
| | | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate]<=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate]) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3))))
| | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID]))
| | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Att].[CompleteDate]))
| | | | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [Att].[StatusID]=(1) AND [Att].[MDflag]=N'Y' AND [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]))
| | | | | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STARTDATE] AS [A2]), SEEK:(([A2].[StartDate], [A2].[ActivityID]) <= ([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate], [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID])), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]), SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1058]=CONVERT_IMPLICIT(int,[Expr1195],0)))
| | | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1195]=Count(*)))
| | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID], [A2].[StartDate], [Expr1126]))
| | | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3)))
| | | | | | | | | | | |--Index Spool(SEEK:([A2].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1126]=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate])))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]))
| | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID] AND [Att].[CompleteDate] >= [CCPD_PROD].[ceschema].[ce_Activity].[StartDate] as [A2].[StartDate] AND [Att].[CompleteDate] <= [Expr1126]))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]), WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[StatusID] as [Att].[StatusID]=(1) AND [CCPD_PROD].[ceschema].[ce_Attendee].[MDflag] as [Att].[MDflag]=N'N' AND [CCPD_PROD].[ceschema].[ce_Attendee].[DeletedFlag] as [Att].[DeletedFlag]=N'N'))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1064]=CONVERT_IMPLICIT(int,[Expr1196],0)))
| | | | | | | | | |--Stream Aggregate(DEFINE:([Expr1196]=Count(*)))
| | | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate]<=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate]) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3))))
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID]))
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Att].[CompleteDate]))
| | | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [Att].[StatusID]=(1) AND [Att].[MDflag]=N'N' AND [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]))
| | | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STARTDATE] AS [A2]), SEEK:(([A2].[StartDate], [A2].[ActivityID]) <= ([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate], [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID])), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]), SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1070]=CONVERT_IMPLICIT(int,[Expr1197],0)))
| | | | | | | | |--Stream Aggregate(DEFINE:([Expr1197]=Count(*)))
| | | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate]<=dateadd(minute,(1439),[CCPD_PROD].[ceschema].[ce_Activity].[EndDate] as [A2].[EndDate]) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3))))
| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A2].[ActivityID]))
| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Att].[CompleteDate]))
| | | | | | | | | |--Index Spool(SEEK:([Att].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [Att].[StatusID]=(1) AND [Att].[MDflag]=N'N' AND [Att].[DeletedFlag]=N'N'))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Attendee].[PK_ce_CourseSect_Person] AS [Att]))
| | | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_STARTDATE] AS [A2]), SEEK:(([A2].[StartDate], [A2].[ActivityID]) <= ([CCPD_PROD].[ceschema].[ce_Attendee].[CompleteDate] as [Att].[CompleteDate], [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID])), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]), SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A2].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | | |--Compute Scalar(DEFINE:([Expr1074]=CASE WHEN [Expr1198]=(0) THEN NULL ELSE [Expr1199] END))
| | | | | | | |--Stream Aggregate(DEFINE:([Expr1198]=COUNT_BIG([CCPD_PROD].[ceschema].[ce_Activity].[StatAddlAttendees] as [A2].[StatAddlAttendees]), [Expr1199]=SUM([CCPD_PROD].[ceschema].[ce_Activity].[StatAddlAttendees] as [A2].[StatAddlAttendees])))
| | | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3)))
| | | | | | | |--Index Spool(SEEK:([A2].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]))
| | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A2].[StatusID]=(3)))
| | | | | | |--Index Spool(SEEK:([A2].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1078]=isnull([CCPD_PROD].[ceschema].[ce_Activity].[StatAddlAttendees] as [A2].[StatAddlAttendees],(0.000000000000000e+000))))
| | | | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A2]))
| | | | | |--Compute Scalar(DEFINE:([Expr1084]=CONVERT_IMPLICIT(int,[Expr1202],0)))
| | | | | |--Stream Aggregate(DEFINE:([Expr1202]=Count(*)))
| | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[DeletedFlag] as [FS].[DeletedFlag]=N'N' AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportTypeID] as [FS].[SupportTypeID]=(1)))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([FS].[SupportID], [Expr1201]) WITH UNORDERED PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A5].[ActivityID]))
| | | | | | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A5].[DeletedFlag]=N'N' AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(3))))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A5].[ActivityID], [Expr1200]) WITH UNORDERED PREFETCH)
| | | | | | | |--Stream Aggregate(GROUP BY:([A5].[ActivityID]))
| | | | | | | | |--Merge Join(Concatenation)
| | | | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_PARENTID] AS [A5]), SEEK:([A5].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A5]), SEEK:([A5].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A5].[DeletedFlag]=N'N' AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(3))) ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A5]), SEEK:([A5].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A5].[ActivityID]) LOOKUP ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[IX_ACTIVITY] AS [FS]), SEEK:([FS].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A5].[ActivityID]) ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[PK_ce_CourseContrib] AS [FS]), SEEK:([FS].[SupportID]=[CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportID] as [FS].[SupportID]) LOOKUP ORDERED FORWARD)
| | | | |--Compute Scalar(DEFINE:([Expr1089]=CONVERT_IMPLICIT(int,[Expr1203],0)))
| | | | |--Stream Aggregate(DEFINE:([Expr1203]=Count(*)))
| | | | |--Index Spool(SEEK:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportTypeID]=(1) AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[DeletedFlag]=N'N'))
| | | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[PK_ce_CourseContrib]))
| | | |--Compute Scalar(DEFINE:([Expr1096]=CONVERT_IMPLICIT(int,[Expr1204],0)))
| | | |--Stream Aggregate(DEFINE:([Expr1204]=Count(*)))
| | | |--Merge Join(Inner Join, MERGE:([A5].[ActivityID])=([FS].[ActivityID]), RESIDUAL:([CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A5].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[ActivityID] as [FS].[ActivityID] AND ([CCPD_PROD].[ceschema].[ce_Activity].[ParentActivityID] as [A5].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A5].[DeletedFlag]=N'N' AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportTypeID] as [FS].[SupportTypeID]=(1) AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[DeletedFlag] as [FS].[DeletedFlag]=N'N' AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(3)) OR [CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A5].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[DeletedFlag] as [FS].[DeletedFlag]=N'N' AND [CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A5].[DeletedFlag]=N'N' AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportTypeID] as [FS].[SupportTypeID]=(1) AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(3)))))
| | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A5]), ORDERED FORWARD)
| | | |--Sort(ORDER BY:([FS].[ActivityID] ASC))
| | | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[PK_ce_CourseContrib] AS [FS]))
| | |--Compute Scalar(DEFINE:([Expr1101]=CONVERT_IMPLICIT(int,[Expr1205],0)))
| | |--Stream Aggregate(DEFINE:([Expr1205]=Count(*)))
| | |--Index Spool(SEEK:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportTypeID]=(1) AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[DeletedFlag]=N'N'))
| | |--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[PK_ce_CourseContrib]))
| |--Compute Scalar(DEFINE:([Expr1108]=CASE WHEN [Expr1208]=(0) THEN NULL ELSE [Expr1209] END))
| |--Stream Aggregate(DEFINE:([Expr1208]=Count(*), [Expr1209]=SUM([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[Amount] as [FS].[Amount])))
| |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[DeletedFlag] as [FS].[DeletedFlag]=N'N' AND [CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportTypeID] as [FS].[SupportTypeID]=(1)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([FS].[SupportID], [Expr1207]) WITH UNORDERED PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES:([A5].[ActivityID]))
| | |--Filter(WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A5].[DeletedFlag]=N'N' AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(3))))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A5].[ActivityID], [Expr1206]) WITH UNORDERED PREFETCH)
| | | |--Stream Aggregate(GROUP BY:([A5].[ActivityID]))
| | | | |--Merge Join(Concatenation)
| | | | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[IX_PARENTID] AS [A5]), SEEK:([A5].[ParentActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A5]), SEEK:([A5].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID]), WHERE:([CCPD_PROD].[ceschema].[ce_Activity].[DeletedFlag] as [A5].[DeletedFlag]=N'N' AND ([CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(1) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(2) OR [CCPD_PROD].[ceschema].[ce_Activity].[StatusID] as [A5].[StatusID]=(3))) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity].[PK_ce_Activity] AS [A5]), SEEK:([A5].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A5].[ActivityID]) LOOKUP ORDERED FORWARD)
| | |--Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[IX_ACTIVITY] AS [FS]), SEEK:([FS].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A5].[ActivityID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[PK_ce_CourseContrib] AS [FS]), SEEK:([FS].[SupportID]=[CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[SupportID] as [FS].[SupportID]) LOOKUP ORDERED FORWARD)
|--Compute Scalar(DEFINE:([Expr1112]=CASE WHEN [Expr1210]=(0) THEN NULL ELSE [Expr1211] END))
|--Stream Aggregate(DEFINE:([Expr1210]=Count(*), [Expr1211]=SUM([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[Amount] as [A6].[Amount])))
|--Index Spool(SEEK:([A6].[ActivityID]=[CCPD_PROD].[ceschema].[ce_Activity].[ActivityID] as [A].[ActivityID] AND [A6].[SupportTypeID]=(1) AND [A6].[DeletedFlag]=N'N'))
|--Clustered Index Scan(OBJECT:([CCPD_PROD].[ceschema].[ce_Activity_FinSupport].[PK_ce_CourseContrib] AS [A6]))
Raw
query.sql
SELECT
A.ActivityID,
A.StartDate,
A.parentActivityId,
DATEADD(n, 1439, A.EndDate) As EndDate,
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
isNull((SELECT SUM(AC.Amount) AS TotalHours
FROM ceschema.ce_Activity_Credit AS AC
INNER JOIN ceschema.ce_Activity AS A4 ON AC.ActivityID = A4.ActivityID
WHERE (AC.CreditID = 1) AND (A4.ParentActivityID = A.ActivityID) AND AC.DeletedFlag='N' AND (A4.StatusID IN (1,2,3))),0)
WHEN 'S' THEN
isNull((SELECT SUM(AC.Amount) AS TotalHours
FROM ceschema.ce_Activity_Credit AS AC
INNER JOIN ceschema.ce_Activity AS A4 ON AC.ActivityID = A4.ActivityID
WHERE (AC.CreditID = 1) AND (A4.ActivityID = A.ActivityID) AND AC.DeletedFlag='N' AND (A4.StatusID IN (1,2,3))),0)
END),0) As StatHrs,
StatAttendees =
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
CASE
WHEN isNull(A.ParentActivityID,0) = 0 THEN
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (A2.ParentActivityID = A.ActivityID) AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
ELSE
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (A2.ActivityID = A.ActivityID) AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
END
WHEN 'S' THEN
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (Att.ActivityID = a.ActivityID) AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
END),0),
StatMD =
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
CASE
WHEN isNull(A.ParentActivityID,0) = 0 THEN
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (A2.ParentActivityID = A.ActivityID) AND (Att.MDflag = 'Y') AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
ELSE
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (A2.ActivityID = A.ActivityID) AND (Att.MDflag = 'Y') AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
END
WHEN 'S' THEN
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (Att.ActivityID = a.ActivityID) AND (Att.MDflag = 'Y') AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
END),0),
StatNonMD =
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
CASE
WHEN isNull(A.ParentActivityID,0) = 0 THEN
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND
(A2.ParentActivityID = A.ActivityID) AND
(Att.MDflag = 'N') AND
(A2.StatusID IN (1,2,3)) AND
(Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
ELSE
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND
(A2.ActivityID = A.ActivityID) AND
(Att.MDflag = 'N') AND
(A2.StatusID IN (1,2,3)) AND
(Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
END
WHEN 'S' THEN
(SELECT Count(Att.AttendeeID)
FROM ceschema.ce_Attendee AS Att
INNER JOIN ceschema.ce_Activity AS A2 ON Att.ActivityID = A2.ActivityID
WHERE
(Att.StatusID = 1) AND (Att.ActivityID = a.ActivityID) AND (Att.MDflag = 'N') AND (A2.StatusID IN (1,2,3)) AND (Att.CompleteDate BETWEEN A2.StartDate AND DATEADD(n, 1439, A2.EndDate)) AND (Att.DeletedFlag='N'))
END),0)+
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
CASE
WHEN isNull(A.ParentActivityID,0) = 0 THEN
(
SELECT isNull(SUM(A2.statAddlAttendees),0)
FROM ceschema.ce_Activity AS A2
WHERE
(A2.ParentActivityID = A.ActivityID) AND
(A2.StatusID IN (1,2,3))
)
ELSE
(SELECT isNull(A2.statAddlAttendees,0)
FROM
ceschema.ce_Activity AS A2
WHERE
(A2.ActivityID = A.ActivityID) AND
(A2.StatusID IN (1,2,3)))
END
WHEN 'S' THEN
A.statAddlAttendees
END),0)
,
StatSupporters =
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
(SELECT COUNT(FS.Amount)
FROM ceschema.ce_Activity_FinSupport AS FS INNER JOIN
ceschema.ce_Activity AS A5 ON FS.ActivityID = A5.ActivityID
WHERE
(A5.ParentActivityID = A.ActivityID) AND
(A5.DeletedFlag='N') AND
(FS.SupportTypeID = 1) AND
(FS.DeletedFlag = 'N') AND
(A5.StatusID IN (1,2,3))
OR
(A5.ActivityID = A.ActivityID) AND
(FS.DeletedFlag = 'N') AND
(A5.DeletedFlag = 'N') AND
(FS.SupportTypeID = 1) AND
(A5.StatusID IN (1,2,3))
OR
(A5.ParentActivityID = A.ActivityID) AND
(A5.DeletedFlag='N') AND
(FS.DeletedFlag = 'N') AND
(FS.SupportTypeID = 1) AND
(A5.StatusID IN (1,2,3))
)
WHEN 'S' THEN
(SELECT COUNT(Amount) AS Expr1
FROM ceschema.ce_Activity_FinSupport
WHERE (SupportTypeID = 1) AND (DeletedFlag = 'N') AND (ActivityID=a.ActivityID))
END),0)
,
(CASE isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
(SELECT COUNT(FS.Amount)
FROM ceschema.ce_Activity_FinSupport AS FS INNER JOIN
ceschema.ce_Activity AS A5 ON FS.ActivityID = A5.ActivityID
WHERE
(A5.ParentActivityID = A.ActivityID) AND
(A5.DeletedFlag='N') AND
(FS.SupportTypeID = 1) AND
(FS.DeletedFlag = 'N') AND
(A5.StatusID IN (1,2,3))
OR
(A5.ActivityID = A.ActivityID) AND
(FS.DeletedFlag = 'N') AND
(A5.DeletedFlag = 'N') AND
(FS.SupportTypeID = 1) AND
(A5.StatusID IN (1,2,3))
OR
(A5.ParentActivityID = A.ActivityID) AND
(A5.DeletedFlag='N') AND
(FS.DeletedFlag = 'N') AND
(FS.SupportTypeID = 1) AND
(A5.StatusID IN (1,2,3))
)
WHEN 'S' THEN
(SELECT COUNT(Amount) AS Expr1
FROM ceschema.ce_Activity_FinSupport
WHERE (SupportTypeID = 1) AND (DeletedFlag = 'N') AND (ActivityID=a.ActivityID))
END),0)
WHEN '0' THEN 'No'
ELSE 'Yes'
END) AS SupportReceived,
StatSuppAmount =
isNull((CASE isNull(A.SessionType,'S')
WHEN 'M' THEN
(SELECT SUM(FS.Amount)
FROM ceschema.ce_Activity_FinSupport AS FS INNER JOIN
ceschema.ce_Activity AS A5 ON FS.ActivityID = A5.ActivityID
WHERE
(A5.ParentActivityID = A.ActivityID) AND
(A5.DeletedFlag='N') AND
(FS.SupportTypeID = 1) AND
(FS.DeletedFlag = 'N') AND
(A5.StatusID IN (1,2,3))
OR
(A5.ActivityID = A.ActivityID) AND
(FS.DeletedFlag = 'N') AND
(A5.DeletedFlag = 'N') AND
(FS.SupportTypeID = 1) AND
(A5.StatusID IN (1,2,3))
OR
(A5.ParentActivityID = A.ActivityID) AND
(A5.DeletedFlag='N') AND
(FS.DeletedFlag = 'N') AND
(FS.SupportTypeID = 1) AND
(A5.StatusID IN (1,2,3))
)
WHEN 'S' THEN
(SELECT SUM(A6.Amount) AS Expr1
FROM ceschema.ce_Activity_FinSupport As A6
WHERE (A6.SupportTypeID = 1) AND (A6.DeletedFlag = 'N') AND (A6.ActivityID=A.ActivityID))
END),0)
FROM
ceschema.ce_Activity A
WHERE
0 = 0
AND (A.DeletedFlag = 'N')
AND (A.startDate > '1/1/2007')
AND (A.refreshFlag=1)
AND (A.statusid IN (1,2,3))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment