Last active
December 14, 2015 02:39
-
-
Save jfrux/5015679 to your computer and use it in GitHub Desktop.
trying to improve this query... :-/
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| |--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])) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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