Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need some help in solving the below logic of averages Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 11:40 AM
Points: 7, 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')



Post #1468646
Posted Friday, June 28, 2013 1:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 4,482, Visits: 3,937
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
Post #1468694
Posted Saturday, June 29, 2013 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 11:40 AM
Points: 7, 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..
Post #1468805
Posted Sunday, June 30, 2013 3:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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;


 
Post #1468891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse