Need some help in solving the below logic of averages

 Author Message SQLServeruser2304 SSC Rookie Group: General Forum Members Points: 37 Visits: 35 I have a requirement to work with the below 2 tables and get the last table as my output. Any help would be great.Table 1 : `Type Code Current BPO ForecastNULL NULL NULL NULL NULL NULL NULL NULL NULLNULL NULL NULL NULL NULL NULL NULLA NULL NULL NULL NULLA NULL NULL NULLA A001 55 32 56.322A A002 NULL NULL NULLA A003 66.22 44.21 81.13A A004 NULL NULL NULLA B001 NULL NULL NULLA B002 NULL NULL NULLR A001 28.423 53.245 82.444R A003 100 100 100R A004 NULL NULL NULLR A023 NULL NULL NULLR C001 89.3444 52.432 100S A001 22.22 18.322 65S C001 67 34 62S NULL NULL NULLZ A003 12 22.56 38.43Z B001 56 43.233 81.69Z A023 NULL NULL NULL ` Table 2 : `Index Type CodeBact A A001Bact R A001Bact Z B001Bact S C001Dis1 A A001Dis1 A A002Dis1 A A003Dis1 R C001Dis1 Z A023MEX A A004MEX A B001MEX R A001MEX S A001MEX A B002TED R A003TED R A004TED S A001TED Z A003ZES R A023ZES Z A003ZES Z A023ZES Z B001 `I need the below columns as my output :` Index Current BPO Forecast Bact (Avg of A A001,A B001,A C001,R A001,R B001,R C001,S A001,S B001,S C001,Z A001,Z B001,Z C001) Dis1 MEX TED ZES`Here Current , BPO and Forecast should be average of the combination of Type and Code for a particular Index.We are also eliminating all the rows which do not match with the Table 2. I am also putting Null values in Table 1 combination as these were the actual values in my tableAlso, Please note that my Table1 is not a table but an output of some other query formed using some logic.Also below are my scripts : Table 1 Create and Insert Scripts :-` CREATE TABLE [dbo].[Table1]( [Type] [char](1) NULL, [Code] [varchar](5) NULL, [Current] [decimal](18, 8) NULL, [BPO] [decimal](18, 8) NULL, [Forecast] [decimal](18, 8) NULL ) ON [PRIMARY] INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES(NULL,NULL,NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('',NULL,NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES(NULL,'',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('','',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A',NULL,NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','',NULL,NULL,NULL) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO [Table1] ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',NULL,NULL,NULL)`Table 2 Create and Insert Scripts :- ` INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','A','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','R','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','Z','B001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Bact','S','C001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','A','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','A','A002') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','A','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','R','C001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('Dis 1','Z','A023') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','A','A004') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','A','B001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','R','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','S','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('MEX','A','B002') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','R','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','R','A004') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','S','A001') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('TED','Z','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','R','A023') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','Z','A003') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','Z','A023') INSERT INTO [Table2] ([Index],[Type],[Code])VALUES('ZES','Z','B001')` Ed Wagner SSC Guru Group: General Forum Members Points: 112179 Visits: 11266 Is this what you're looking for?`SELECT t2.[index], t1.[type], t1.code, AVG([current]) avg_current, AVG([bpo]) avg_bpo, AVG([forecast]) avg_forecast FROM table1 t1 INNER JOIN table2 t2 ON t2.code = t1.code GROUP BY t2.[index], t1.[type], t1.code ORDER BY t2.[index], t1.[type], t1.code;` Tally Tables - Performance PersonifiedString Splitting with True PerformanceBest practices on how to ask questions SQLServeruser2304 SSC Rookie Group: General Forum Members Points: 37 Visits: 35 This is not the correct solution as it won't check the combination of my type and will only check for the Code. For every index in Table 2 , I need to get the combination of Type and Code and check for the same values in Table 1 to map it and get my output.. Steven Willis Hall of Fame Group: General Forum Members Points: 3957 Visits: 1721 Maybe this will take you in the right direction. I generated a bunch of random numbers to make a larger sample.Sample data:`-- [You will need to create some functions to create this test data, which I've included at the bottom.]IF OBJECT_ID('tempdb..#TempTable1') IS NOT NULLDROP TABLE #TempTable1IF OBJECT_ID('tempdb..#TempTable2') IS NOT NULLDROP TABLE #TempTable2 CREATE TABLE #TempTable1( [Type] [char](1) NULL, [Code] [varchar](5) NULL, [Current] [decimal](18, 8) NULL, [BPO] [decimal](18, 8) NULL, [Forecast] [decimal](18, 8) NULL ) ON [PRIMARY] CREATE TABLE #TempTable2( [Index] [varchar](5) NULL, [Type] [char](1) NULL, [Code] [varchar](5) NULL, ) ON [PRIMARY] INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A001',55.00000000,32.00000000,56.32200000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A003',66.22000000,44.21000000,81.13000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('A','B002',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A001',28.42300000,53.24500000,82.44400000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A003',100.00000000,100.00000000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A004',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('R','C001',89.34440000,52.43200000,100.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','A001',22.22000000,18.32200000,65.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',67.00000000,34.00000000,62.00000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('S','C001',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A003',12.00000000,22.56000000,38.43000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','B001',56.00000000,43.23300000,81.69000000) INSERT INTO #TempTable1 ([Type],[Code],[Current],[BPO],[Forecast])VALUES('Z','A023',(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger()),(SELECT dbo.svfGenerateRandomInteger())) --SELECT * FROM #TempTable1 INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Bact','A','A001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Bact','R','A001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Bact','Z','B001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Bact','S','C001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Dis 1','A','A001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Dis 1','A','A002') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Dis 1','A','A003') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Dis 1','R','C001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('Dis 1','Z','A023') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('MEX','A','A004') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('MEX','A','B001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('MEX','R','A001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('MEX','S','A001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('MEX','A','B002') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('TED','R','A003') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('TED','R','A004') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('TED','S','A001') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('TED','Z','A003') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('ZES','R','A023') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('ZES','Z','A003') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('ZES','Z','A023') INSERT INTO #TempTable2 ([Index],[Type],[Code])VALUES('ZES','Z','B001')`The query to return mean and std dev for each group.` SELECT DISTINCT tt1.[Type] ,tt1.[Code] ,tt2.[Index] ,COUNT(tt1.[Current]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS CurrentSampleSize ,AVG(tt1.[Current]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS CurrentMean ,STDEV(tt1.[Current]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS CurrentStdDev ,COUNT(tt1.[BPO]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS BPOSampleSize ,AVG(tt1.[BPO]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS BPOMean ,STDEV(tt1.[BPO]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS BPOStdDev ,COUNT(tt1.[Forecast]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS ForecastSampleSize ,AVG(tt1.[Forecast]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS ForecastMean ,STDEV(tt1.[Forecast]) OVER (PARTITION BY tt2.[Type],tt1.[Code]) AS ForecastStdDev FROM #TempTable1 tt1 INNER JOIN #TempTable2 tt2 ON tt1.[Type] = tt2.[Type] AND tt1.code = tt2.Code ORDER BY tt2.[Index] ,tt1.[Type] ,tt1.[Code]`The result`SELECT * FROM (VALUES ('A','A001','Bact',48,55.50000000,28.2526594492881,48,47.20833333,28.360864804908,48,52.30225000,23.5810645876115),('R','A001','Bact',16,28.42300000,0,16,53.24500000,0,16,82.44400000,0),('S','C001','Bact',16,58.68750000,14.3977718183521,16,48.06250000,26.3804694171022,16,52.75000000,19.8511124793213),('Z','B001','Bact',16,56.00000000,0,16,43.23300000,1.20631319433913E-06,16,81.69000000,0),('A','A001','Dis 1',48,55.50000000,28.2526594492881,48,47.20833333,28.360864804908,48,52.30225000,23.5810645876115),('A','A002','Dis 1',24,46.08333333,28.7310113726049,24,57.37500000,26.491692709389,24,55.45833333,31.3922992033932),('A','A003','Dis 1',8,66.22000000,0,8,44.21000000,1.01952072873087E-06,8,81.13000000,0),('R','C001','Dis 1',8,89.34440000,1.76586170153151E-06,8,52.43200000,0,8,100.00000000,0),('Z','A023','Dis 1',16,66.50000000,26.4222128772995,16,28.12500000,29.4457127609437,16,36.50000000,26.9666460650931),('A','A004','MEX',8,48.00000000,30.5473870755398,8,39.12500000,27.5340698045167,8,55.50000000,24.465719457466),('A','B001','MEX',8,37.62500000,27.650561864609,8,39.75000000,30.9088521763126,8,42.87500000,29.2205284982029),('A','B002','MEX',8,64.50000000,25.4221500720477,8,35.50000000,35.7810803957303,8,40.00000000,28.4102597162162),('R','A001','MEX',16,28.42300000,0,16,53.24500000,0,16,82.44400000,0),('S','A001','MEX',16,22.22000000,0,16,18.32200000,0,16,65.00000000,0),('R','A003','TED',8,100.00000000,0,8,100.00000000,0,8,100.00000000,0),('R','A004','TED',8,35.62500000,21.023371348505,8,60.50000000,23.0836986390211,8,51.62500000,24.9109126976225),('S','A001','TED',16,22.22000000,0,16,18.32200000,0,16,65.00000000,0),('Z','A003','TED',16,12.00000000,0,16,22.56000000,0,16,38.43000000,9.84950598705906E-07),('R','A023','ZES',24,51.62500000,30.6619896401987,24,56.33333333,25.8131524727165,24,53.08333333,26.0115637606693),('Z','A003','ZES',16,12.00000000,0,16,22.56000000,0,16,38.43000000,9.84950598705906E-07),('Z','A023','ZES',16,66.50000000,26.4222128772995,16,28.12500000,29.4457127609437,16,36.50000000,26.9666460650931),('Z','B001','ZES',16,56.00000000,0,16,43.23300000,1.20631319433913E-06,16,81.69000000,0)) AS vtable ([Type],[Code],[Index],[CurrentSampleSize],[CurrentMean],[CurrentStdDev],[BPOSampleSize],[BPOMean],[BPOStdDev],[ForecastSampleSize],[ForecastMean],[ForecastStdDev])`Functions (only necessary for generating the random test data)`CREATE VIEW [dbo].[iFunction] AS SELECT MyNewID = NEWID(), MyDate = GETDATE();CREATE FUNCTION [dbo].[svfGenerateRandomInteger]()RETURNS INTASBEGIN DECLARE @RandomInt INT SELECT TOP(1) @RandomInt = N FROM dbo.itvfTally(1,100) ORDER BY (SELECT MyNewID FROM dbo.iFunction) RETURN @RandomInt ENDCREATE FUNCTION [dbo].[itvfTally] ( @pMin BIGINT ,@pMax BIGINT )RETURNS TABLEWITH SCHEMABINDINGASRETURN /* Uses the Bates algorithm for generating the rows from */ /* http://www.sqlservercentral.com/scripts/Tally+Table/99617/ */ /* Usage: SELECT N FROM [dbo].[itvfTally](1,10000) SELECT N FROM [dbo].[itvfTally](20001,30000) */ WITH T1(F) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), T2(F) AS (SELECT 1 FROM T1 A, T1 B), T3(F) AS (SELECT 1 FROM T2 A, T2 B), T4(F) AS (SELECT 1 FROM T3 A, T3 B), cteTally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM T4 )SELECT N FROM cteTally TWHERE T.N BETWEEN @pMin AND @pMax;`