SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some help in solving the below logic of averages


Need some help in solving the below logic of averages

Author
Message
SQLServeruser2304
SQLServeruser2304
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

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 Forecast

NULL NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL
A NULL NULL NULL NULL
A NULL NULL NULL
A A001 55 32 56.322
A A002 NULL NULL NULL
A A003 66.22 44.21 81.13
A A004 NULL NULL NULL
A B001 NULL NULL NULL
A B002 NULL NULL NULL
R A001 28.423 53.245 82.444
R A003 100 100 100
R A004 NULL NULL NULL
R A023 NULL NULL NULL
R C001 89.3444 52.432 100
S A001 22.22 18.322 65
S C001 67 34 62
S NULL NULL NULL
Z A003 12 22.56 38.43
Z B001 56 43.233 81.69
Z A023 NULL NULL NULL




Table 2 :


Index Type Code

Bact A A001
Bact R A001
Bact Z B001
Bact S C001
Dis1 A A001
Dis1 A A002
Dis1 A A003
Dis1 R C001
Dis1 Z A023
MEX A A004
MEX A B001
MEX R A001
MEX S A001
MEX A B002
TED R A003
TED R A004
TED S A001
TED Z A003
ZES R A023
ZES Z A003
ZES Z A023
ZES 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 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
Ed Wagner
SSC Guru
SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)SSC Guru (112K reputation)

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 Personified
String Splitting with True Performance
Best practices on how to ask questions
SQLServeruser2304
SQLServeruser2304
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

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
Steven Willis
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

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 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;



 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search