----------------------------------------------------------------
--                                                              
--  File:   Load_TPCE_Info.SQL                                  
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
SET NOCOUNT ON
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
PRINT   ' '

USE	tpce
GO

-- set the MAX Degree of Parallelism to 0 for this test
sp_configure    'max degree of parallelism',0
GO

-- reconfigure the server so the MAXDOP will take effect
RECONFIGURE WITH OVERRIDE
GO

IF EXISTS (SELECT  name
           FROM    sysobjects
           WHERE   name    = 'TPCE_INFO' AND
                   type    = 'U')
    DROP TABLE TPCE_INFO
GO

CREATE TABLE TPCE_INFO
     (  INFO_DATE                   datetime,
        EGEN_VERSION                varchar(6),
        MS_KIT_VERSION              varchar(12),

        NUM_BROKER                  numeric(15),
        NUM_CUSTOMER                numeric(15),
        NUM_CUSTOMER_TAXRATE        numeric(15),
        NUM_CUSTOMER_ACCOUNT        numeric(15),
        NUM_ACCOUNT_PERMISSION      numeric(15),
        NUM_WATCH_LIST              numeric(15),
        NUM_WATCH_ITEM              numeric(15),

        NUM_CHARGE                  numeric(15),
        NUM_COMMISSION_RATE         numeric(15),
        NUM_CASH_TRANSACTION        numeric(15),
        NUM_HOLDING                 numeric(15),
        NUM_HOLDING_HISTORY         numeric(15),
        NUM_HOLDING_SUMMARY         numeric(15),
        NUM_SETTLEMENT              numeric(15),
        NUM_TRADE                   numeric(15),
        NUM_TRADE_HISTORY           numeric(15),
        NUM_TRADE_REQUEST           numeric(15),
        NUM_TRADE_TYPE              numeric(15),

        NUM_COMPANY                 numeric(15),
        NUM_COMPANY_COMPETITOR      numeric(15),
        NUM_DAILY_MARKET            numeric(15),
        NUM_EXCHANGE                numeric(15),
        NUM_FINANCIAL               numeric(15),
        NUM_INDUSTRY                numeric(15),
        NUM_LAST_TRADE              numeric(15),
        NUM_NEWS_ITEM               numeric(15),
        NUM_NEWS_XREF               numeric(15),
        NUM_SECTOR                  numeric(15),
        NUM_SECURITY                numeric(15),

        NUM_ADDRESS                 numeric(15),
        NUM_STATUS_TYPE             numeric(15),
        NUM_TAXRATE                 numeric(15),
        NUM_ZIP_CODE                numeric(15),

        MAX_T_ID                    bigint )
GO

DECLARE @EGEN_VERSION               varchar(6),
        @MS_KIT_VERSION             varchar(12),

        @NUM_BROKER                 numeric(15),
        @NUM_CUSTOMER               numeric(15),
        @NUM_CUSTOMER_TAXRATE       numeric(15),
        @NUM_CUSTOMER_ACCOUNT       numeric(15),
        @NUM_ACCOUNT_PERMISSION     numeric(15),
        @NUM_WATCH_LIST             numeric(15),
        @NUM_WATCH_ITEM             numeric(15),

        @NUM_CHARGE                 numeric(15),
        @NUM_COMMISSION_RATE        numeric(15),
        @NUM_CASH_TRANSACTION       numeric(15),
        @NUM_HOLDING                numeric(15),
        @NUM_HOLDING_HISTORY        numeric(15),
        @NUM_HOLDING_SUMMARY        numeric(15),
        @NUM_SETTLEMENT             numeric(15),
        @NUM_TRADE                  numeric(15),
        @NUM_TRADE_HISTORY          numeric(15),
        @NUM_TRADE_REQUEST          numeric(15),
        @NUM_TRADE_TYPE             numeric(15),

        @NUM_COMPANY                numeric(15),
        @NUM_COMPANY_COMPETITOR     numeric(15),
        @NUM_DAILY_MARKET           numeric(15),
        @NUM_EXCHANGE               numeric(15),
        @NUM_FINANCIAL              numeric(15),
        @NUM_INDUSTRY               numeric(15),
        @NUM_LAST_TRADE             numeric(15),
        @NUM_NEWS_ITEM              numeric(15),
        @NUM_NEWS_XREF              numeric(15),
        @NUM_SECTOR                 numeric(15),
        @NUM_SECURITY               numeric(15),

        @NUM_ADDRESS                numeric(15),
        @NUM_STATUS_TYPE            numeric(15),
        @NUM_TAXRATE                numeric(15),
        @NUM_ZIP_CODE               numeric(15),

        @MAX_T_ID                   bigint   

-- get the EGen and Kit version from the TPCE_VERSIONS Table
SELECT  TOP 1
        @EGEN_VERSION       = EGEN_VERSION,
        @MS_KIT_VERSION     = MS_KIT_VERSION
FROM    TPCE_VERSIONS
WHERE   BUILD_PHASE         = 'Load/Generate Data'
ORDER   BY BUILD_PHASE_START_TIME DESC

-- set the local variables prior to inserting them into the TPCE_INFO table
SELECT  @NUM_BROKER                 = COUNT_BIG(*)
FROM    BROKER

SELECT  @NUM_CUSTOMER_ACCOUNT       = COUNT_BIG(*)
FROM    CUSTOMER_ACCOUNT

SELECT  @NUM_CUSTOMER               = ( SELECT  COUNT_BIG(*)
                                        FROM    CUSTOMER),
        @NUM_CUSTOMER_TAXRATE       = ( SELECT  COUNT_BIG(*)
                                        FROM    CUSTOMER_TAXRATE),
        @NUM_ACCOUNT_PERMISSION     = ( SELECT  COUNT_BIG(*)
                                        FROM    ACCOUNT_PERMISSION),
        @NUM_WATCH_LIST             = ( SELECT  COUNT_BIG(*)
                                        FROM    WATCH_LIST),
        @NUM_WATCH_ITEM             = ( SELECT  COUNT_BIG(*)
                                        FROM    WATCH_ITEM)

SELECT  @NUM_CASH_TRANSACTION       = COUNT_BIG(*)
FROM    CASH_TRANSACTION

SELECT  @NUM_TRADE                  = COUNT_BIG(*),
        @MAX_T_ID                   = MAX(T_ID)
FROM    TRADE

SELECT  @NUM_CHARGE                 = ( SELECT  COUNT(*)
                                        FROM    CHARGE),
        @NUM_COMMISSION_RATE        = ( SELECT  COUNT(*)
                                        FROM    COMMISSION_RATE),
        @NUM_HOLDING                = ( SELECT  COUNT_BIG(*)
                                        FROM    HOLDING),
        @NUM_HOLDING_HISTORY        = ( SELECT  COUNT_BIG(*)
                                        FROM    HOLDING_HISTORY),
        @NUM_HOLDING_SUMMARY        = ( SELECT  COUNT_BIG(*)
                                        FROM    HOLDING_SUMMARY),
        @NUM_SETTLEMENT             = ( SELECT  COUNT_BIG(*)
                                        FROM    SETTLEMENT),
        @NUM_TRADE_HISTORY          = ( SELECT  COUNT_BIG(*)
                                        FROM    TRADE_HISTORY),
        @NUM_TRADE_REQUEST          = ( SELECT  COUNT_BIG(*)
                                        FROM    TRADE_REQUEST),
        @NUM_TRADE_TYPE             = ( SELECT  COUNT(*)
                                        FROM    TRADE_TYPE)

SELECT  @NUM_COMPANY                = (	SELECT  COUNT_BIG(*)
                                        FROM    COMPANY),
        @NUM_COMPANY_COMPETITOR     = ( SELECT  COUNT_BIG(*)
                                        FROM    COMPANY_COMPETITOR),
        @NUM_DAILY_MARKET           = ( SELECT  COUNT_BIG(*)
                                        FROM    DAILY_MARKET),
        @NUM_EXCHANGE               = ( SELECT  COUNT(*)
                                        FROM    EXCHANGE),
        @NUM_FINANCIAL              = ( SELECT  COUNT_BIG(*)
                                        FROM    FINANCIAL),
        @NUM_INDUSTRY               = ( SELECT  COUNT(*)
                                        FROM    INDUSTRY),
        @NUM_LAST_TRADE             = ( SELECT  COUNT_BIG(*)
                                        FROM    LAST_TRADE),
        @NUM_NEWS_ITEM              = ( SELECT  COUNT_BIG(*)
                                        FROM    NEWS_ITEM),
        @NUM_NEWS_XREF              = ( SELECT  COUNT_BIG(*)
                                        FROM    NEWS_XREF),
        @NUM_SECTOR                 = ( SELECT  COUNT(*)
                                        FROM    SECTOR),
        @NUM_SECURITY               = ( SELECT  COUNT_BIG(*)
                                        FROM    SECURITY)

SELECT  @NUM_ADDRESS                = ( SELECT  COUNT_BIG(*)
                                        FROM    ADDRESS),
        @NUM_STATUS_TYPE            = ( SELECT  COUNT(*)
                                        FROM    STATUS_TYPE),
        @NUM_TAXRATE                = ( SELECT  COUNT(*)
                                        FROM    TAXRATE),
        @NUM_ZIP_CODE               = ( SELECT  COUNT(*)
                                        FROM    ZIP_CODE)

--- insert the values into TPCE_INFO
INSERT INTO TPCE_INFO VALUES   (GETDATE(),
                                @EGEN_VERSION,
                                @MS_KIT_VERSION,
                                @NUM_BROKER,
                                @NUM_CUSTOMER,
                                @NUM_CUSTOMER_TAXRATE,
                                @NUM_CUSTOMER_ACCOUNT,
                                @NUM_ACCOUNT_PERMISSION,
                                @NUM_WATCH_LIST,
                                @NUM_WATCH_ITEM,

                                @NUM_CHARGE,
                                @NUM_COMMISSION_RATE,
                                @NUM_CASH_TRANSACTION,
                                @NUM_HOLDING,
                                @NUM_HOLDING_HISTORY,
                                @NUM_HOLDING_SUMMARY,
                                @NUM_SETTLEMENT,
                                @NUM_TRADE,
                                @NUM_TRADE_HISTORY,
                                @NUM_TRADE_REQUEST,
                                @NUM_TRADE_TYPE,

                                @NUM_COMPANY,
                                @NUM_COMPANY_COMPETITOR,
                                @NUM_DAILY_MARKET,
                                @NUM_EXCHANGE,
                                @NUM_FINANCIAL,
                                @NUM_INDUSTRY,
                                @NUM_LAST_TRADE,
                                @NUM_NEWS_ITEM,
                                @NUM_NEWS_XREF,
                                @NUM_SECTOR,
                                @NUM_SECURITY,

                                @NUM_ADDRESS,
                                @NUM_STATUS_TYPE,
                                @NUM_TAXRATE,
                                @NUM_ZIP_CODE,

                                @MAX_T_ID )
GO

--- output the row counts from the last build
PRINT   ''
PRINT   ''
PRINT   '---------------------------'
PRINT   '| EGEN/KIT VERSION & DATE  |'
PRINT   '---------------------------'
SELECT  TOP 1
        CONVERT(CHAR(30),INFO_DATE,21)  AS	'TPCE Info Date',
        EGEN_VERSION                    AS  'EGen Version',
        MS_KIT_VERSION                  AS  'MS Kit Version'
FROM    TPCE_INFO
ORDER   BY INFO_DATE  DESC
GO

PRINT   ''
PRINT   ''
PRINT   '---------------------------'
PRINT   '| CUSTOMER/ACCOUNT TABLES |'
PRINT   '---------------------------'
SELECT  TOP 1
        CONVERT(CHAR(30),INFO_DATE,21)  AS	'TPCE Info Date',
        NUM_BROKER                      AS	'Broker Rows',
        NUM_CUSTOMER                    AS	'Customer Rows',
        NUM_CUSTOMER_TAXRATE            AS	'Customer Taxrate_Rows',
        NUM_CUSTOMER_ACCOUNT            AS	'Customer Account_Rows',
        NUM_ACCOUNT_PERMISSION          AS	'Account Permission Rows',
        NUM_WATCH_LIST                  AS	'Watch List Rows',
        NUM_WATCH_ITEM                  AS	'Watch Item Rows'
FROM    TPCE_INFO
ORDER   BY INFO_DATE  DESC
GO

PRINT   ''
PRINT   ''
PRINT   '---------------------------'
PRINT   '|  TRADE RELATED TABLES   |'
PRINT   '---------------------------'
SELECT  TOP 1
        CONVERT(CHAR(30),INFO_DATE,21)  AS	'TPCE Info Date',
        MAX_T_ID                        AS  'Max Initial T_ID',
        NUM_CHARGE                      AS	'Charge Rows',
        NUM_COMMISSION_RATE             AS	'Commission Rate Rows',
        NUM_CASH_TRANSACTION            AS	'Cash Transaction Rows',
        NUM_HOLDING                     AS	'Holding Rows',
        NUM_HOLDING_HISTORY             AS	'Holding History Rows',
        NUM_HOLDING_SUMMARY             AS	'Holding Summary Rows',
        NUM_SETTLEMENT                  AS	'Settlement Rows',
        NUM_TRADE                       AS	'Trade Rows',
        NUM_TRADE_HISTORY               AS	'Trade History Rows',
        NUM_TRADE_REQUEST               AS	'Trade Request Rows',
        NUM_TRADE_TYPE                  AS	'Trade Type Rows'
FROM    TPCE_INFO
ORDER   BY INFO_DATE  DESC
GO

PRINT   ''
PRINT   ''
PRINT   '---------------------------'
PRINT   '|  MARKET RELATED TABLES  |'
PRINT   '---------------------------'
SELECT  TOP 1
        CONVERT(CHAR(30),INFO_DATE,21)  AS	'TPCE Info Date',
        NUM_COMPANY                     AS	'Company Rows',
        NUM_COMPANY_COMPETITOR          AS	'Company Competitor Rows',
        NUM_DAILY_MARKET                AS	'Daily Market Rows',
        NUM_EXCHANGE                    AS	'Exchange Rows',
        NUM_FINANCIAL                   AS	'Financial Rows',
        NUM_INDUSTRY                    AS	'Industry Rows',
        NUM_LAST_TRADE                  AS	'Last Trade Rows',
        NUM_NEWS_ITEM                   AS	'News Item Rows',
        NUM_NEWS_XREF                   AS	'News Xref Rows',
        NUM_SECTOR                      AS	'Sector Rows',
        NUM_SECURITY                    AS	'Security Rows'
FROM    TPCE_INFO
ORDER   BY INFO_DATE  DESC
GO

PRINT   ''
PRINT   ''
PRINT   '---------------------------'
PRINT   '|    DIMENSION TABLES     |'
PRINT   '---------------------------'
SELECT  TOP 1
        CONVERT(CHAR(30),INFO_DATE,21)  AS	'TPCE Info Date',
        NUM_ADDRESS                     AS	'Address Rows',
        NUM_STATUS_TYPE                 AS	'Status Type Rows',
        NUM_TAXRATE                     AS	'Taxrate Rows',
        NUM_ZIP_CODE                    AS	'Zip Code Rows'
FROM    TPCE_INFO
ORDER   BY INFO_DATE  DESC
GO

-- set the MAX Degree of Parallelism to 1 for the runtime
sp_configure    'max degree of parallelism',1
GO

-- reconfigure the server so the MAXDOP will take effect
RECONFIGURE WITH OVERRIDE
GO
