Need some help in solving the below logic of averages

• SQLServeruser2304

  SSC Enthusiast

  Points: 129

  More actions

  June 28, 2013 at 11:27 am

  #276785

  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 :

  TypeCodeCurrentBPOForecast

  NULLNULLNULLNULLNULL

  NULLNULLNULLNULL

  NULLNULLNULLNULL

  NULLNULLNULL

  ANULLNULLNULLNULL

  ANULLNULLNULL

  AA001553256.322

  AA002NULLNULLNULL

  AA00366.2244.2181.13

  AA004NULLNULLNULL

  AB001NULLNULLNULL

  AB002NULLNULLNULL

  RA00128.42353.24582.444

  RA003100100100

  RA004NULLNULLNULL

  RA023NULLNULLNULL

  RC00189.344452.432100

  SA00122.2218.32265

  SC001673462

  SNULLNULLNULL

  ZA0031222.5638.43

  ZB0015643.23381.69

  ZA023NULLNULLNULL

  Table 2 :

  IndexTypeCode

  BactAA001

  BactRA001

  BactZB001

  BactSC001

  Dis1AA001

  Dis1AA002

  Dis1AA003

  Dis1RC001

  Dis1ZA023

  MEXAA004

  MEXAB001

  MEXRA001

  MEXSA001

  MEXAB002

  TEDRA003

  TEDRA004

  TEDSA001

  TEDZA003

  ZESRA023

  ZESZA003

  ZESZA023

  ZESZB001

  I need the below columns as my output :

  Index CurrentBPOForecast

  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 table

  Also, 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

  Points: 287024

  More actions

  June 28, 2013 at 1:38 pm

  #1628413

  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 Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• SQLServeruser2304

  SSC Enthusiast

  Points: 129

  More actions

  June 29, 2013 at 10:03 am

  #1628501

  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

  SSCrazy Eights

  Points: 9893

  More actions

  June 30, 2013 at 3:04 pm

  #1628567

  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 NULL

  DROP TABLE #TempTable1

  IF OBJECT_ID('tempdb..#TempTable2') IS NOT NULL

  DROP 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 INT

  AS

  BEGIN

  DECLARE @RandomInt INT

  SELECT TOP(1) @RandomInt = N FROM dbo.itvfTally(1,100) ORDER BY (SELECT MyNewID FROM dbo.iFunction)

  RETURN @RandomInt

  END

  CREATE FUNCTION [dbo].[itvfTally]

  (

  @pMin BIGINT

  ,@pMax BIGINT

  )

  RETURNS TABLE

  WITH SCHEMABINDING

  AS

  RETURN

  /* 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 T

  WHERE

  T.N BETWEEN @pMin AND @pMax;