|--Compute Scalar(DEFINE[Expr1043]=CONVERT(int,isnull([Expr1012],(0.00000)),0), [Expr1044]=CONVERT(int,isnull([Expr1013],(0.00000)),0), [Expr1045]=CONVERT(int,isnull([Expr1014],(0.00000)),0), [Expr1047]=CONVERT(int,isnull([Expr1016],(0.00000)),0), [Expr1048]=CONVERT(int,isnull([Expr1017],(0.00000)),0), [Expr1051]=CONVERT(int,isnull([Expr1020],(0.00000)),0), [Expr1053]=CONVERT(int,isnull([Expr1022],(0.00000)),0), [Expr1054]=CONVERT(int,isnull([Expr1023],(0.00000)),0), [Expr1056]=CONVERT(int,isnull([Expr1025],(0.00000)),0), [Expr1058]=CONVERT(int,isnull([Expr1027],(0.00000)),0), [Expr1059]=CONVERT(int,isnull([Expr1028],(0.00000)),0), [Expr1061]=CONVERT(int,isnull([Expr1030],(0.00000)),0), [Expr1064]=CONVERT(int,isnull([Expr1033],(0.00000)),0), [Expr1066]=CONVERT(int,isnull([Expr1035],(0.00000)),0), [Expr1067]=CONVERT(int,isnull([Expr1036],(0.00000)),0), [Expr1068]=CONVERT(int,isnull([Expr1037],(0.00000)),0), [Expr1069]=CONVERT(int,isnull([Expr1038],(0.00000)),0), [Expr1070]=CONVERT(int,isnull([Expr1039],(0.00000)),0), [Expr1071]=CONVERT(int,isnull([Expr1040],(0.00000)),0), [Expr1072]=CONVERT(int,isnull([Expr1041],(0.00000)),0))) |--Top(TOP EXPRESSION(100000))) |--Parallelism(Gather Streams, ORDER BY[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC)) |--Nested Loops(Inner Join, WHERE[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod] AND [Expr1231]=[Expr1235])) |--Nested Loops(Inner Join, WHERE[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPYear] as [ytd].[GPYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPPeriod] as [ytd].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode]=[Expr1235])) | |--Sort(ORDER BY[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC)) | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE[pc].[GpYear], [pc].[GpPeriod], [Expr1235], [Expr1234])=([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode], [ei].[EstDebtorID]), RESIDUAL[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear]=[NCS_TSDEVON].[dbo].[EISProcCal].[GpYear] as [pc].[GpYear] AND [NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISProcCal].[GpPeriod] as [pc].[GpPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[Expr1235] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID]=[Expr1234])) | | |--Sort(ORDER BY[pc].[GpYear] ASC, [pc].[GpPeriod] ASC, [Expr1230] ASC, [Expr1234] ASC)) | | | |--Bitmap(HASH[r].[GPYear], [r].[GPPeriod], [Expr1235], [Expr1234]), DEFINE[Bitmap1345])) | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [Expr1235], [Expr1234])) | | | |--Hash Match(Inner Join, HASH[pc].[GpYear], [pc].[GpPeriod])=([r].[GPYear], [r].[GPPeriod]), RESIDUAL[NCS_TSDEVON].[dbo].[EISProcCal].[GpYear] as [pc].[GpYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISProcCal].[GpPeriod] as [pc].[GpPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod])) | | | |--Parallelism(Distribute Streams, Broadcast Partitioning) | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISProcCal].[PK_EISProcCal] AS [pc])) | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE[Expr1230])=([Expr1235]), RESIDUAL[Expr1230]=[Expr1235])) | | | |--Sort(ORDER BY[Expr1230] ASC)) | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS[Expr1230])) | | | | |--Top(TOP EXPRESSION(100000))) | | | | |--Compute Scalar(DEFINE[Expr1230]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0))) | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC)) | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]) DEFINE[Expr1199]=SUM([partialagg1326]), [Expr1200]=SUM([partialagg1327]), [Expr1201]=SUM([partialagg1328]), [Expr1203]=SUM([partialagg1329]), [Expr1204]=SUM([partialagg1330]), [Expr1207]=SUM([partialagg1331]), [Expr1209]=SUM([partialagg1332]), [Expr1210]=SUM([partialagg1333]), [Expr1212]=SUM([partialagg1334]), [Expr1214]=SUM([partialagg1335]), [Expr1215]=SUM([partialagg1336]), [Expr1217]=SUM([partialagg1337]), [Expr1220]=SUM([partialagg1338]), [Expr1222]=SUM([partialagg1339]), [Expr1223]=SUM([partialagg1340]), [Expr1224]=SUM([partialagg1341]), [Expr1225]=SUM([partialagg1342]), [Expr1226]=SUM([partialagg1343]), [Expr1228]=SUM([partialagg1344]))) | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[EstCode] ASC)) | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode])) | | | | |--Hash Match(Partial Aggregate, HASH[r].[EstCode], [r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod]), RESIDUAL[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]) DEFINE[partialagg1326]=SUM(CONVERT(int,isnull([Expr1138],(0.00000)),0)), [partialagg1327]=SUM(CONVERT(int,isnull([Expr1139],(0.00000)),0)), [partialagg1328]=SUM(CONVERT(int,isnull([Expr1140],(0.00000)),0)), [partialagg1329]=SUM(CONVERT(int,isnull([Expr1142],(0.00000)),0)), [partialagg1330]=SUM(CONVERT(int,isnull([Expr1143],(0.00000)),0)), [partialagg1331]=SUM(CONVERT(int,isnull([Expr1146],(0.00000)),0)), [partialagg1332]=SUM(CONVERT(int,isnull([Expr1148],(0.00000)),0)), [partialagg1333]=SUM(CONVERT(int,isnull([Expr1149],(0.00000)),0)), [partialagg1334]=SUM(CONVERT(int,isnull([Expr1151],(0.00000)),0)), [partialagg1335]=SUM(CONVERT(int,isnull([Expr1153],(0.00000)),0)), [partialagg1336]=SUM(CONVERT(int,isnull([Expr1154],(0.00000)),0)), [partialagg1337]=SUM(CONVERT(int,isnull([Expr1156],(0.00000)),0)), [partialagg1338]=SUM(CONVERT(int,isnull([Expr1159],(0.00000)),0)), [partialagg1339]=SUM(CONVERT(int,isnull([Expr1161],(0.00000)),0)), [partialagg1340]=SUM(CONVERT(int,isnull([Expr1162],(0.00000)),0)), [partialagg1341]=SUM(CONVERT(int,isnull([Expr1163],(0.00000)),0)), [partialagg1342]=SUM(CONVERT(int,isnull([Expr1164],(0.00000)),0)), [partialagg1343]=SUM(CONVERT(int,isnull([Expr1165],(0.00000)),0)), [partialagg1344]=SUM(CONVERT(int,isnull([Expr1167],(0.00000)),0)))) | | | | |--Hash Match(Inner Join, HASH[r].[EstCode], [r].[BudgetYear])=([r].[EstCode], [r].[BudgetYear]), RESIDUAL[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]<=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod])) | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS[r].[EstCode], [r].[BudgetYear])) | | | | | |--Top(TOP EXPRESSION(100000))) | | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC)) | | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC)) | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r])) | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS[r].[EstCode], [r].[BudgetYear])) | | | | |--Top(TOP EXPRESSION(100000))) | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC)) | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE[Expr1138]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1139]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1140]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1142]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1143]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1146]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1148]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1149]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1151]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1153]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1154]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1156]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1159]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1161]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1162]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1163]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1164]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1165]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1167]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END))) | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC)) | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r])) | | | |--Sort(ORDER BY[Expr1235] ASC)) | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS[Expr1235])) | | | |--Nested Loops(Inner Join, WHERE[Expr1232]=[Expr1233])) | | | |--Compute Scalar(DEFINE[Expr1233]=CONVERT_IMPLICIT(nvarchar(6),[NCS_TSDEVON].[dbo].[EISKAM].[KAMArea] as [ka].[KAMArea],0))) | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISKAM].[PK_EISKAM] AS [ka])) | | | |--Compute Scalar(DEFINE[Expr1232]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[ManArea] as [em].[ManArea],0), [Expr1234]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstDebtorID] as [em].[EstDebtorID],0), [Expr1235]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstCode] as [em].[EstCode],0))) | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS [em])) | | |--Compute Scalar(DEFINE[Expr1229]=[NCS_TSDEVON].[dbo].[EISInvoice].[Condemn] as [ei].[Condemn]*(-1.))) | | |--Sort(ORDER BY[ei].[GPYear] ASC, [ei].[GPPeriod] ASC, [ei].[EstCode] ASC, [ei].[EstDebtorID] ASC)) | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode], [ei].[EstDebtorID])) | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISInvoice].[PK_EISInvoice] AS [ei]), WHEREPROBE([Bitmap1345],[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear],[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod],[NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode],[NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID]))) | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[PK_EISInvoiceYTD] AS [ytd])) |--Top(TOP EXPRESSION(100000))) |--Compute Scalar(DEFINE[Expr1231]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0))) |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE[Expr1012]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1013]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1014]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1016]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1017]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1020]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1022]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1023]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1025]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1027]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1028]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1030]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1033]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1035]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1036]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1037]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1038]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1039]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1040]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VAT' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1041]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END))) |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC)) |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r]))
|--Compute Scalar(DEFINE[Expr1051]=CONVERT(int,isnull([Expr1020],(0.00000)),0), [Expr1052]=CONVERT(int,isnull([Expr1021],(0.00000)),0), [Expr1053]=CONVERT(int,isnull([Expr1022],(0.00000)),0), [Expr1055]=CONVERT(int,isnull([Expr1024],(0.00000)),0), [Expr1056]=CONVERT(int,isnull([Expr1025],(0.00000)),0), [Expr1059]=CONVERT(int,isnull([Expr1028],(0.00000)),0), [Expr1061]=CONVERT(int,isnull([Expr1030],(0.00000)),0), [Expr1062]=CONVERT(int,isnull([Expr1031],(0.00000)),0), [Expr1064]=CONVERT(int,isnull([Expr1033],(0.00000)),0), [Expr1066]=CONVERT(int,isnull([Expr1035],(0.00000)),0), [Expr1067]=CONVERT(int,isnull([Expr1036],(0.00000)),0), [Expr1069]=CONVERT(int,isnull([Expr1038],(0.00000)),0), [Expr1072]=CONVERT(int,isnull([Expr1041],(0.00000)),0), [Expr1074]=CONVERT(int,isnull([Expr1043],(0.00000)),0), [Expr1075]=CONVERT(int,isnull([Expr1044],(0.00000)),0), [Expr1076]=CONVERT(int,isnull([Expr1045],(0.00000)),0), [Expr1077]=CONVERT(int,isnull([Expr1046],(0.00000)),0), [Expr1078]=CONVERT(int,isnull([Expr1047],(0.00000)),0), [Expr1079]=CONVERT(int,isnull([Expr1048],(0.00000)),0), [Expr1080]=CONVERT(int,isnull([Expr1049],(0.00000)),0))) |--Top(TOP EXPRESSION(100000))) |--Parallelism(Gather Streams, ORDER BY[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC)) |--Nested Loops(Inner Join, WHERE[Expr1254]=[Expr1255])) |--Nested Loops(Inner Join, OUTER REFERENCES[ei].[GPYear], [ei].[GPPeriod])) | |--Nested Loops(Inner Join, WHERE[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode]=[Expr1257] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstDebtorID] as [ei].[EstDebtorID]=[Expr1256])) | | |--Sort(ORDER BY[ei].[GPYear] ASC, [ei].[EstCode] ASC, [ei].[DocumentNo] ASC)) | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE[ei].[GPYear], [ei].[GPPeriod])=([ytd].[GPYear], [ytd].[GPPeriod]), RESIDUAL[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPYear] as [ytd].[GPYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [NCS_TSDEVON].[dbo].[EISInvoiceYTD].[GPPeriod] as [ytd].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[EstCode] as [ytd].[EstCode])) | | | |--Sort(ORDER BY[ei].[GPYear] ASC, [ei].[GPPeriod] ASC)) | | | | |--Bitmap(HASH[r].[GPYear], [r].[GPPeriod]), DEFINE[Bitmap1369])) | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod])) | | | | |--Hash Match(Inner Join, HASH[r].[GPYear], [r].[GPPeriod], [ei].[EstCode])=([r].[GPYear], [r].[GPPeriod], [Expr1349]), RESIDUAL[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[Expr1349])) | | | | |--Parallelism(Distribute Streams, Broadcast Partitioning) | | | | | |--Hash Match(Inner Join, HASH[r].[GPYear], [r].[GPPeriod], [Expr1348])=([ei].[GPYear], [ei].[GPPeriod], [ei].[EstCode]), RESIDUAL[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISInvoice].[EstCode] as [ei].[EstCode]=[Expr1348])) | | | | | |--Compute Scalar(DEFINE[Expr1348]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0))) | | | | | | |--Top(TOP EXPRESSION(100000))) | | | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC)) | | | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode]) DEFINE[Expr1223]=SUM([partialagg1350]), [Expr1224]=SUM([partialagg1351]), [Expr1225]=SUM([partialagg1352]), [Expr1227]=SUM([partialagg1353]), [Expr1228]=SUM([partialagg1354]), [Expr1231]=SUM([partialagg1355]), [Expr1233]=SUM([partialagg1356]), [Expr1234]=SUM([partialagg1357]), [Expr1236]=SUM([partialagg1358]), [Expr1238]=SUM([partialagg1359]), [Expr1239]=SUM([partialagg1360]), [Expr1241]=SUM([partialagg1361]), [Expr1244]=SUM([partialagg1362]), [Expr1246]=SUM([partialagg1363]), [Expr1247]=SUM([partialagg1364]), [Expr1248]=SUM([partialagg1365]), [Expr1249]=SUM([partialagg1366]), [Expr1250]=SUM([partialagg1367]), [Expr1252]=SUM([partialagg1368]))) | | | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[EstCode] ASC)) | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod], [r].[EstCode])) | | | | | | |--Hash Match(Partial Aggregate, HASH[r].[EstCode], [r].[GPYear], [r].[GPPeriod], [r].[BudgetYear], [r].[BudgetPeriod]), RESIDUAL[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod] = [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]) DEFINE[partialagg1350]=SUM(CONVERT(int,isnull([Expr1162],(0.00000)),0)), [partialagg1351]=SUM(CONVERT(int,isnull([Expr1163],(0.00000)),0)), [partialagg1352]=SUM(CONVERT(int,isnull([Expr1164],(0.00000)),0)), [partialagg1353]=SUM(CONVERT(int,isnull([Expr1166],(0.00000)),0)), [partialagg1354]=SUM(CONVERT(int,isnull([Expr1167],(0.00000)),0)), [partialagg1355]=SUM(CONVERT(int,isnull([Expr1170],(0.00000)),0)), [partialagg1356]=SUM(CONVERT(int,isnull([Expr1172],(0.00000)),0)), [partialagg1357]=SUM(CONVERT(int,isnull([Expr1173],(0.00000)),0)), [partialagg1358]=SUM(CONVERT(int,isnull([Expr1175],(0.00000)),0)), [partialagg1359]=SUM(CONVERT(int,isnull([Expr1177],(0.00000)),0)), [partialagg1360]=SUM(CONVERT(int,isnull([Expr1178],(0.00000)),0)), [partialagg1361]=SUM(CONVERT(int,isnull([Expr1180],(0.00000)),0)), [partialagg1362]=SUM(CONVERT(int,isnull([Expr1183],(0.00000)),0)), [partialagg1363]=SUM(CONVERT(int,isnull([Expr1185],(0.00000)),0)), [partialagg1364]=SUM(CONVERT(int,isnull([Expr1186],(0.00000)),0)), [partialagg1365]=SUM(CONVERT(int,isnull([Expr1187],(0.00000)),0)), [partialagg1366]=SUM(CONVERT(int,isnull([Expr1188],(0.00000)),0)), [partialagg1367]=SUM(CONVERT(int,isnull([Expr1189],(0.00000)),0)), [partialagg1368]=SUM(CONVERT(int,isnull([Expr1191],(0.00000)),0)))) | | | | | | |--Hash Match(Inner Join, HASH[r].[EstCode], [r].[BudgetYear])=([r].[EstCode], [r].[BudgetYear]), RESIDUAL[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear] AND [NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod]<=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod])) | | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS[r].[EstCode], [r].[BudgetYear])) | | | | | | | |--Top(TOP EXPRESSION(100000))) | | | | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC)) | | | | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC)) | | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | | | | |--Compute Scalar(DEFINE[r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear], [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod], [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear], [r].[BudgetPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod], [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode], [r].[SourceID]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[SourceID] as [r].[SourceID])) | | | | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r])) | | | | | | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS[r].[EstCode], [r].[BudgetYear])) | | | | | | |--Top(TOP EXPRESSION(100000))) | | | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC)) | | | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE[Expr1162]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1163]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1164]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1166]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1167]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1170]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1172]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1173]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1175]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1177]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1178]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1180]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1183]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1185]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1186]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1187]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1188]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1189]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1191]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END))) | | | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC)) | | | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | | | |--Compute Scalar(DEFINE[r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear], [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod], [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear], [r].[BudgetPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod], [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode], [r].[SourceID]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[SourceID] as [r].[SourceID])) | | | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r])) | | | | | |--Compute Scalar(DEFINE[Expr1253]=[NCS_TSDEVON].[dbo].[EISInvoice].[Condemn] as [ei].[Condemn]*(-1.))) | | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISInvoice].[PK_EISInvoice] AS [ei])) | | | | |--Compute Scalar(DEFINE[Expr1349]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode],0))) | | | | |--Parallelism(Distribute Streams, RoundRobin Partitioning) | | | | |--Top(TOP EXPRESSION(100000))) | | | | |--Parallelism(Gather Streams, ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC)) | | | | |--Stream Aggregate(GROUP BY[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID]) DEFINE[Expr1020]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Cash' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1021]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Condemn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1022]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='Functions' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1024]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStd' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1025]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='HoursStdOU' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1028]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='InvRec' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1030]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='ManFee' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1031]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MFree' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1033]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='MWheels' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1035]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurcHO' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1036]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1038]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='PurNFood' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1041]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='StockClose' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1043]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TDays' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1044]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranInDC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1045]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TranOutPK' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1046]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransIn' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1047]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='TransOut' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1048]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VAT' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END), [Expr1049]=MAX(CASE WHEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[LineCode] as [r].[LineCode]='VatRecPC' THEN [NCS_TSDEVON].[dbo].[EISBudgetRaw].[Value] as [r].[Value] ELSE NULL END))) | | | | |--Sort(ORDER BY[r].[GPYear] ASC, [r].[GPPeriod] ASC, [r].[EstCode] ASC, [r].[BudgetYear] ASC, [r].[BudgetPeriod] ASC, [r].[SourceID] ASC)) | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[r].[GPYear], [r].[GPPeriod], [r].[EstCode], [r].[BudgetYear], [r].[BudgetPeriod], [r].[SourceID])) | | | | |--Compute Scalar(DEFINE[r].[GPYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPYear] as [r].[GPYear], [r].[GPPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[GPPeriod] as [r].[GPPeriod], [r].[BudgetYear]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetYear] as [r].[BudgetYear], [r].[BudgetPeriod]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[BudgetPeriod] as [r].[BudgetPeriod], [r].[EstCode]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[EstCode] as [r].[EstCode], [r].[SourceID]=[NCS_TSDEVON].[dbo].[EISBudgetRaw].[SourceID] as [r].[SourceID])) | | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISBudgetRaw].[PK_EISBudgetRaw] AS [r])) | | | |--Sort(ORDER BY[ytd].[GPYear] ASC, [ytd].[GPPeriod] ASC)) | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[ytd].[GPYear], [ytd].[GPPeriod]), WHEREPROBE([Bitmap1369])=TRUE)) | | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISInvoiceYTD].[PK_EISInvoiceYTD] AS [ytd])) | | |--Table Spool | | |--Compute Scalar(DEFINE[Expr1254]=CONVERT_IMPLICIT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[ManArea] as [em].[ManArea],0), [Expr1256]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstDebtorID] as [em].[EstDebtorID],0), [Expr1257]=CONVERT(nvarchar(15),[NCS_TSDEVON].[dbo].[TSEstMast].[EstCode] as [em].[EstCode],0))) | | |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[TSEstMast].[PK_TSEstMast] AS [em])) | |--Clustered Index Seek(OBJECT[NCS_TSDEVON].[dbo].[EISProcCal].[PK_EISProcCal] AS [pc]), SEEK[pc].[GpYear]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPYear] as [ei].[GPYear] AND [pc].[GpPeriod]=[NCS_TSDEVON].[dbo].[EISInvoice].[GPPeriod] as [ei].[GPPeriod]) ORDERED FORWARD) |--Compute Scalar(DEFINE[Expr1255]=CONVERT_IMPLICIT(nvarchar(6),[NCS_TSDEVON].[dbo].[EISKAM].[KAMArea] as [ka].[KAMArea],0))) |--Clustered Index Scan(OBJECT[NCS_TSDEVON].[dbo].[EISKAM].[PK_EISKAM] AS [ka]))
SELECT TOP (100000) ei.Cash, ei.CashU, ei.Condemn * - 1 AS Condemn, ei.DocumentNo, ei.EstCode, ei.Functions, ei.FunctionsU, ei.GPYear, ei.GPPeriod, ei.HoursStd, ei.HoursStdOU, ei.InvRec, ei.ManFeeOU, ei.MFree, ei.MFreeU, ei.MWheels, ei.MWheelsU, ei.PurFood, ei.PurFoodOU, ei.PurHO, ei.PurNFood, ei.PurNFoodOU, ei.StockClose, ei.StockOpen, ei.Taxpoint, ei.TDays, ei.TranInDC, ei.TranOutPK, ei.TransIn, ei.TransOut, ei.VatRecPC, ytd.SUMCash, ytd.SUMCashU, ytd.SUMCondemn, ytd.SUMFunctions, ytd.SUMFunctionsU, ytd.SUMHoursStd, ytd.SUMHoursStdOU, ytd.SUMInvRec, ytd.SUMManFeeOU, ytd.SUMMFree, ytd.SUMMFreeU, ytd.SUMMWheels, ytd.SUMMWheelsU, ytd.SUMPurFood, ytd.SUMPurFoodOU, ytd.SUMPurHO, ytd.SUMPurNFoodOU, ytd.SUMPurNFood, ytd.SUMStockClose, ytd.SUMStockOpen, ytd.SUMTranInDC, ytd.SUMTranOutPK, ytd.SUMTransIn, ytd.SUMTransOut, ytd.SUMVat, ytd.SUMVatRecPC, em.EstCodeName, em.ManArea, pc.MonthName, b.bCash, b.bCondemn, b.bFunctions, b.bHoursStd, b.bHoursStdOU, b.bInvRec, b.bManFee, b.bMFree, b.bMWheels, b.bPurcHO, b.bPurFood, b.bPurNFood, b.bStockClose, b.bTDays, b.bTranInDC, b.bTranOutPK, b.bTransIn, b.bTransOut, b.bVAT, b.bVatRecPC, btd.btdCash, btd.btdCondemn, btd.btdFunctions, btd.btdHoursStd, btd.btdHoursStdOU, btd.btdInvRec, btd.btdManFee, btd.btdMFree, btd.btdMWheels, btd.btdPurcHO, btd.btdPurFood, btd.btdPurNFood, btd.btdStockClose, btd.btdTDays, btd.btdTranInDC, btd.btdTranOutPK, btd.btdTransIn, btd.btdTransOut, btd.btdVatRecPCFROM dbo.EISInvoice AS ei INNER JOIN dbo.TSEstMast AS em ON ei.EstDebtorID = em.EstDebtorID COLLATE SQL_Latin1_General_CP1_CI_AS AND ei.EstCode = em.EstCode COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN dbo.EISProcCal AS pc ON ei.GPYear = pc.GpYear AND ei.GPPeriod = pc.GpPeriod INNER JOIN dbo.EISKAM AS ka ON em.ManArea = ka.KAMArea INNER JOIN dbo.EISInvoiceYTD AS ytd ON ei.GPYear = ytd.GPYear AND ei.GPPeriod = ytd.GPPeriod AND ei.EstCode = ytd.EstCode INNER JOIN dbo.EisBudgetGP AS b ON ei.GPYear = b.GPYear AND ei.GPPeriod = b.GPPeriod AND ei.EstCode = b.EstCode INNER JOIN dbo.EISBudgetGPYtd AS btd ON ei.GPYear = btd.GPYear AND ei.GPPeriod = btd.GPPeriod AND ei.EstCode = btd.EstCodeORDER BY ei.GPYear, ei.EstCode, ei.DocumentNo
