----------------------------------------------------------------
--                                                              
--  File:   DB_Check.SQL                                        
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
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

SET NOCOUNT ON
GO

PRINT   ''
PRINT   ''
PRINT   '-----------------------------------'
PRINT   '|  TPC-E Database Check - Begin   |'
PRINT   '-----------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO
                                    
DECLARE @NUM_CUST_ROWS              bigint,
        @NUM_CUST_TAX_ROWS          bigint,
        @CUST_TAX_TARGET            bigint,
        @CUST_TAX_DELTA             bigint,
        @CUST_TAX_DELTA_PCT         numeric(7,4),
        @NUM_CUST_ACCT_ROWS         bigint,
        @CUST_ACCT_TARGET           bigint,
        @CUST_ACCT_DELTA            bigint,
        @CUST_ACCT_DELTA_PCT        numeric(7,4),
        @NUM_ACCT_PERM_ROWS         bigint,
        @ACCT_PERM_TARGET           bigint,
        @ACCT_PERM_DELTA            bigint,
        @ACCT_PERM_DELTA_PCT        numeric(7,4),
        @NUM_ADDRESS_ROWS           bigint,
        @ADDRESS_TARGET             bigint,
        @ADDRESS_DELTA              bigint,
        @ADDRESS_DELTA_PCT          numeric(7,4),
        @NUM_BROKER_ROWS            bigint,
        @BROKER_TARGET              bigint,
        @BROKER_DELTA               bigint,
        @BROKER_DELTA_PCT           numeric(7,4),
        @NUM_COMPANY_ROWS           bigint,
        @COMPANY_TARGET             bigint,
        @COMPANY_DELTA              bigint,
        @COMPANY_DELTA_PCT          numeric(7,4),
        @NUM_COMPANY_COMP_ROWS      bigint,
        @COMPANY_COMP_TARGET        bigint,
        @COMPANY_COMP_DELTA         bigint,
        @COMPANY_COMP_DELTA_PCT     numeric(7,4),
        @NUM_DAILY_MARKET_ROWS      bigint,
        @DAILY_MARKET_TARGET        bigint,
        @DAILY_MARKET_DELTA         bigint,
        @DAILY_MARKET_DELTA_PCT     numeric(7,4),
        @NUM_FINANCIAL_ROWS         bigint,
        @FINANCIAL_TARGET           bigint,
        @FINANCIAL_DELTA            bigint,
        @FINANCIAL_DELTA_PCT        numeric(7,4),
        @NUM_LAST_TRADE_ROWS        bigint,
        @LAST_TRADE_TARGET          bigint,
        @LAST_TRADE_DELTA           bigint,
        @LAST_TRADE_DELTA_PCT       numeric(7,4),
        @NUM_NEWS_ITEM_ROWS         bigint,
        @NEWS_ITEM_TARGET           bigint,
        @NEWS_ITEM_DELTA            bigint,
        @NEWS_ITEM_DELTA_PCT        numeric(7,4),
        @NUM_NEWS_XREF_ROWS         bigint,
        @NEWS_XREF_TARGET           bigint,
        @NEWS_XREF_DELTA            bigint,
        @NEWS_XREF_DELTA_PCT        numeric(7,4),
        @NUM_SECURITY_ROWS          bigint,
        @SECURITY_TARGET            bigint,
        @SECURITY_DELTA             bigint,
        @SECURITY_DELTA_PCT         numeric(7,4),
        @NUM_WATCH_LIST_ROWS        bigint,
        @WATCH_LIST_TARGET          bigint,
        @WATCH_LIST_DELTA           bigint,
        @WATCH_LIST_DELTA_PCT       numeric(7,4),
        @NUM_WATCH_ITEM_ROWS        bigint,
        @WATCH_ITEM_TARGET          bigint,
        @WATCH_ITEM_DELTA           bigint,
        @WATCH_ITEM_DELTA_PCT       numeric(7,4),
        @NUM_CASH_TRAN_ROWS         bigint,
        @CASH_TRAN_TARGET           bigint,
        @CASH_TRAN_DELTA            bigint,
        @CASH_TRAN_DELTA_PCT        numeric(7,4),
        @NUM_HOLDING_ROWS           bigint,
        @HOLDING_TARGET             bigint,
        @HOLDING_DELTA              bigint,
        @HOLDING_DELTA_PCT          numeric(7,4),
        @NUM_HOLDING_HIST_ROWS      bigint,
        @HOLDING_HIST_TARGET        bigint,
        @HOLDING_HIST_DELTA         bigint,
        @HOLDING_HIST_DELTA_PCT     numeric(7,4),
        @NUM_HOLDING_SUMM_ROWS      bigint,
        @HOLDING_SUMM_TARGET        bigint,
        @HOLDING_SUMM_DELTA         bigint,
        @HOLDING_SUMM_DELTA_PCT     numeric(7,4),
        @NUM_SETTLEMENT_ROWS        bigint,
        @SETTLEMENT_TARGET          bigint,
        @SETTLEMENT_DELTA           bigint,
        @SETTLEMENT_DELTA_PCT       numeric(7,4),
        @NUM_TRADE_ROWS             bigint,
        @TRADE_TARGET               bigint,
        @TRADE_DELTA                bigint,
        @TRADE_DELTA_PCT            numeric(7,4),
        @NUM_TRADE_HIST_ROWS        bigint,
        @TRADE_HIST_TARGET          bigint,
        @TRADE_HIST_DELTA           bigint,
        @TRADE_HIST_DELTA_PCT       numeric(7,4),
        @NUM_TRADE_REQUEST_ROWS     bigint,
        @TRADE_REQUEST_TARGET       bigint,
        @TRADE_REQUEST_DELTA        bigint,
        @TRADE_REQUEST_DELTA_PCT    numeric(7,4),
        @NUM_CHARGE_ROWS            bigint,
        @CHARGE_TARGET              bigint,
        @CHARGE_DELTA               bigint,
        @CHARGE_DELTA_PCT           numeric(7,4),
        @NUM_COMM_RATE_ROWS         bigint,
        @COMM_RATE_TARGET           bigint,
        @COMM_RATE_DELTA            bigint,
        @COMM_RATE_DELTA_PCT        numeric(7,4),
        @NUM_EXCHANGE_ROWS          bigint,
        @EXCHANGE_TARGET            bigint,
        @EXCHANGE_DELTA             bigint,
        @EXCHANGE_DELTA_PCT         numeric(7,4),
        @NUM_INDUSTRY_ROWS          bigint,
        @INDUSTRY_TARGET            bigint,
        @INDUSTRY_DELTA             bigint,
        @INDUSTRY_DELTA_PCT         numeric(7,4),
        @NUM_SECTOR_ROWS            bigint,
        @SECTOR_TARGET              bigint,
        @SECTOR_DELTA               bigint,
        @SECTOR_DELTA_PCT           numeric(7,4),
        @NUM_STATUS_TYPE_ROWS       bigint,
        @STATUS_TYPE_TARGET         bigint,
        @STATUS_TYPE_DELTA          bigint,
        @STATUS_TYPE_DELTA_PCT      numeric(7,4),
        @NUM_TAXRATE_ROWS           bigint,
        @TAXRATE_TARGET             bigint,
        @TAXRATE_DELTA              bigint,
        @TAXRATE_DELTA_PCT          numeric(7,4),
        @NUM_TRADE_TYPE_ROWS        bigint,
        @TRADE_TYPE_TARGET          bigint,
        @TRADE_TYPE_DELTA           bigint,
        @TRADE_TYPE_DELTA_PCT       numeric(7,4),
        @NUM_ZIP_CODE_ROWS          bigint,
        @ZIP_CODE_TARGET            bigint,
        @ZIP_CODE_DELTA             bigint,
        @ZIP_CODE_DELTA_PCT         numeric(7,4),
        @SUM_CA_BAL                 numeric(18,2),
        @SUM_CT_AMT                 numeric(18,2)

--- load the local variables for the FIXED tables
SELECT  @NUM_CHARGE_ROWS        = ( SELECT   COUNT_BIG(*)
                                    FROM    CHARGE),
        @NUM_COMM_RATE_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    COMMISSION_RATE),
        @NUM_EXCHANGE_ROWS      = ( SELECT   COUNT_BIG(*)
                                    FROM    EXCHANGE),
        @NUM_INDUSTRY_ROWS      = ( SELECT   COUNT_BIG(*)
                                    FROM    INDUSTRY),
        @NUM_SECTOR_ROWS        = ( SELECT   COUNT_BIG(*)
                                    FROM    SECTOR),
        @NUM_STATUS_TYPE_ROWS   = ( SELECT   COUNT_BIG(*)
                                    FROM    STATUS_TYPE),
        @NUM_TAXRATE_ROWS       = ( SELECT   COUNT_BIG(*)
                                    FROM    TAXRATE),
        @NUM_TRADE_TYPE_ROWS    = ( SELECT   COUNT_BIG(*)
                                    FROM    TRADE_TYPE),
        @NUM_ZIP_CODE_ROWS      = ( SELECT   COUNT_BIG(*)
                                    FROM    ZIP_CODE)

--- load the local variables for the SCALING tables
SELECT  @NUM_CUST_ROWS          = ( SELECT   COUNT_BIG(*)
                                    FROM    CUSTOMER),
        @NUM_CUST_TAX_ROWS      = ( SELECT   COUNT_BIG(*)
                                    FROM    CUSTOMER_TAXRATE),
        @NUM_CUST_ACCT_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    CUSTOMER_ACCOUNT),
        @NUM_ACCT_PERM_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    ACCOUNT_PERMISSION),
        @NUM_ADDRESS_ROWS       = ( SELECT   COUNT_BIG(*)
                                    FROM    ADDRESS),
        @NUM_BROKER_ROWS        = ( SELECT   COUNT_BIG(*)
                                    FROM    BROKER),
        @NUM_COMPANY_ROWS       = ( SELECT   COUNT_BIG(*)
                                    FROM    COMPANY),
        @NUM_COMPANY_COMP_ROWS  = ( SELECT   COUNT_BIG(*)
                                    FROM    COMPANY_COMPETITOR),
        @NUM_DAILY_MARKET_ROWS  = ( SELECT   COUNT_BIG(*)
                                    FROM    DAILY_MARKET),
        @NUM_FINANCIAL_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    FINANCIAL),
        @NUM_LAST_TRADE_ROWS    = ( SELECT   COUNT_BIG(*)
                                    FROM    LAST_TRADE),
        @NUM_NEWS_ITEM_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    NEWS_ITEM),
        @NUM_NEWS_XREF_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    NEWS_XREF),
        @NUM_SECURITY_ROWS      = ( SELECT   COUNT_BIG(*)
                                    FROM    SECURITY),
        @NUM_WATCH_LIST_ROWS    = ( SELECT   COUNT_BIG(*)
                                    FROM    WATCH_LIST),
        @NUM_WATCH_ITEM_ROWS    = ( SELECT   COUNT_BIG(*)
                                    FROM    WATCH_ITEM)

--- load the local variables for the GROWING tables
SELECT  @NUM_CASH_TRAN_ROWS     = ( SELECT   COUNT_BIG(*)
                                    FROM    CASH_TRANSACTION),
        @NUM_HOLDING_ROWS       = ( SELECT   COUNT_BIG(*)
                                    FROM    HOLDING),
        @NUM_HOLDING_HIST_ROWS  = ( SELECT   COUNT_BIG(*)
                                    FROM    HOLDING_HISTORY),
        @NUM_HOLDING_SUMM_ROWS  = ( SELECT   COUNT_BIG(*)
                                    FROM    HOLDING_SUMMARY),
        @NUM_SETTLEMENT_ROWS    = ( SELECT   COUNT_BIG(*)
                                    FROM    SETTLEMENT),
        @NUM_TRADE_ROWS         = ( SELECT   COUNT_BIG(*)
                                    FROM    TRADE),
        @NUM_TRADE_HIST_ROWS    = ( SELECT   COUNT_BIG(*)
                                    FROM    TRADE_HISTORY),
        @NUM_TRADE_REQUEST_ROWS = ( SELECT   COUNT_BIG(*)
                                    FROM    TRADE_REQUEST)

--- calculate the target values for the SCALING tables
SELECT  @CUST_TAX_TARGET        = @NUM_CUST_ROWS * 2,                                      --- Clause 2.6.1.10 (Customers * 2)
        @CUST_ACCT_TARGET       = @NUM_CUST_ROWS * 5,                                      --- Clause 2.6.1.10 (Customers * 5)
        @ACCT_PERM_TARGET       = @NUM_CUST_ACCT_ROWS * 1.42,                              --- Clause 2.6.1.10 (Accounts * approx. 1.42)
        @ADDRESS_TARGET         = @NUM_COMPANY_ROWS + @NUM_EXCHANGE_ROWS + @NUM_CUST_ROWS, --- Clause 2.6.1.10 (Companies * Exchanges * Customers)
        @BROKER_TARGET          = @NUM_CUST_ROWS * .01,                                    --- Clause 2.6.1.10 (Customers * 0.01)
        @COMPANY_TARGET         = (@NUM_CUST_ROWS / 1000) * 500,                           --- Clause 2.6.1.10 ((Customers/1000) * 500)
        @COMPANY_COMP_TARGET    = @NUM_COMPANY_ROWS * 3,                                   --- Clause 2.6.1.10 (Companies * 3)
        @DAILY_MARKET_TARGET    = @NUM_SECURITY_ROWS * 1305,                               --- Clause 2.6.1.10 (Securities * 1305)
        @FINANCIAL_TARGET       = @NUM_COMPANY_ROWS * 20,                                  --- Clause 2.6.1.10 (Companies * 20)
        @LAST_TRADE_TARGET      = @NUM_SECURITY_ROWS * 1,                                  --- Clause 2.6.1.10 (Securities * 1)
        @NEWS_ITEM_TARGET       = @NUM_COMPANY_ROWS * 2,                                   --- Clause 2.6.1.10 (Companies * 2)
        @NEWS_XREF_TARGET       = @NUM_COMPANY_ROWS * 2,                                   --- Clause 2.6.1.10 (Companies * 2)
        @SECURITY_TARGET        = (@NUM_CUST_ROWS / 1000) * 685,                           --- Clause 2.6.1.10 ((Customers/1000) * 685)
        @WATCH_LIST_TARGET      = @NUM_CUST_ROWS * 1,                                      --- Clause 2.6.1.10 (Customers * 1)
        @WATCH_ITEM_TARGET      = @NUM_CUST_ROWS * 100                                     --- Clause 2.6.1.10 (Customers * Approx. 100)

--- calculate the target values for the GROWING tables
SELECT  @CASH_TRAN_TARGET       = @NUM_TRADE_ROWS * .92,                                --- Clause 2.6.1.11 (Settled * approx. 0.92)
        @HOLDING_TARGET         = @NUM_TRADE_ROWS * .051,                               --- Clause 2.6.1.11 (Settled * approx. 0.051)
        @HOLDING_HIST_TARGET    = @NUM_TRADE_ROWS * 1.340,                              --- Clause 2.6.1.11 (Settled * approx. 1.340)
        @HOLDING_SUMM_TARGET    = @NUM_CUST_ACCT_ROWS * 9.956,                          --- Clause 2.6.1.11 (Accounts * approx. 9.956)
        @SETTLEMENT_TARGET      = @NUM_TRADE_ROWS,                                      --- Clause 2.6.1.11 (Settled * 1)
        @TRADE_TARGET           = (300 * 8 * 3600 * @NUM_CUST_ROWS) / 500,              --- Clause 2.6.1.11 ((ITD*8*3600*Customers)/SF)
        @TRADE_HIST_TARGET      = @NUM_TRADE_ROWS * 2.4,                                --- Clause 2.6.1.11 (Trades * approx. 2.4)
        @TRADE_REQUEST_TARGET   = 0                                                     --- Clause 2.6.1.11 (0)

--- calculate the target values for the FIXED tables
SELECT  @CHARGE_TARGET          = 15,           --- Clause 2.6.1.9 (5 Trade Types * 3 Customer Tiers)
        @COMM_RATE_TARGET       = 240,          --- Clause 2.6.1.9 (4 Rates * 4 Exchanges * 5 Trade Types * 3 Customer Tiers)
        @EXCHANGE_TARGET        = 4,            --- Clause 2.6.1.9 (4 Exchanges)
        @INDUSTRY_TARGET        = 102,          --- Clause 2.6.1.9 (102 Industries)
        @SECTOR_TARGET          = 12,           --- Clause 2.6.1.9 (12 Sectors)
        @STATUS_TYPE_TARGET     = 5,            --- Clause 2.6.1.9 (5 Status Types)
        @TAXRATE_TARGET         = 320,          --- Clause 2.6.1.9 (320 Tax Rates)
        @TRADE_TYPE_TARGET      = 5,            --- Clause 2.6.1.9 (5 Trade Types)
        @ZIP_CODE_TARGET        = 14741         --- Clause 2.6.1.9 (14,741 Zip Codes)


--- insert into the temporary SCALING table
INSERT INTO  TPCE_SCALING_TABLES VALUES(    @NUM_CUST_ROWS,
                                            @NUM_CUST_TAX_ROWS,
                                            @CUST_TAX_TARGET,
                                            @NUM_CUST_ACCT_ROWS,
                                            @CUST_ACCT_TARGET,
                                            @NUM_ACCT_PERM_ROWS,
                                            @ACCT_PERM_TARGET,
                                            @NUM_ADDRESS_ROWS,
                                            @ADDRESS_TARGET,
                                            @NUM_BROKER_ROWS,
                                            @BROKER_TARGET,
                                            @NUM_COMPANY_ROWS,
                                            @COMPANY_TARGET,
                                            @NUM_COMPANY_COMP_ROWS,
                                            @COMPANY_COMP_TARGET,
                                            @NUM_DAILY_MARKET_ROWS,
                                            @DAILY_MARKET_TARGET,
                                            @NUM_FINANCIAL_ROWS,
                                            @FINANCIAL_TARGET,
                                            @NUM_LAST_TRADE_ROWS,
                                            @LAST_TRADE_TARGET,
                                            @NUM_NEWS_ITEM_ROWS,
                                            @NEWS_ITEM_TARGET,
                                            @NUM_NEWS_XREF_ROWS,
                                            @NEWS_XREF_TARGET,
                                            @NUM_SECURITY_ROWS,
                                            @SECURITY_TARGET,
                                            @NUM_WATCH_LIST_ROWS,
                                            @WATCH_LIST_TARGET,
                                            @NUM_WATCH_ITEM_ROWS,
                                            @WATCH_ITEM_TARGET)
                                
--- insert into the temporary GROWING table
INSERT INTO  TPCE_GROWING_TABLES VALUES(    @NUM_CASH_TRAN_ROWS,
                                            @CASH_TRAN_TARGET,
                                            @NUM_HOLDING_ROWS,
                                            @HOLDING_TARGET,
                                            @NUM_HOLDING_HIST_ROWS,
                                            @HOLDING_HIST_TARGET,
                                            @NUM_HOLDING_SUMM_ROWS,
                                            @HOLDING_SUMM_TARGET,
                                            @NUM_SETTLEMENT_ROWS,
                                            @SETTLEMENT_TARGET,
                                            @NUM_TRADE_ROWS,
                                            @TRADE_TARGET,
                                            @NUM_TRADE_HIST_ROWS,
                                            @TRADE_HIST_TARGET,
                                            @NUM_TRADE_REQUEST_ROWS,
                                            @TRADE_REQUEST_TARGET)

--- insert into the temporary FIXED table
INSERT INTO  TPCE_FIXED_TABLES VALUES(      @NUM_CHARGE_ROWS,
                                            @CHARGE_TARGET,
                                            @NUM_COMM_RATE_ROWS,
                                            @COMM_RATE_TARGET,
                                            @NUM_EXCHANGE_ROWS,
                                            @EXCHANGE_TARGET,
                                            @NUM_INDUSTRY_ROWS,
                                            @INDUSTRY_TARGET,
                                            @NUM_SECTOR_ROWS,
                                            @SECTOR_TARGET,
                                            @NUM_STATUS_TYPE_ROWS,
                                            @STATUS_TYPE_TARGET,
                                            @NUM_TAXRATE_ROWS,
                                            @TAXRATE_TARGET,
                                            @NUM_TRADE_TYPE_ROWS,
                                            @TRADE_TYPE_TARGET,
                                            @NUM_ZIP_CODE_ROWS,
                                            @ZIP_CODE_TARGET)    

SELECT  NUM_CUST_ROWS As 'Number of Customers',
        NUM_CUST_ROWS/1000 As 'Number of Load Units'
FROM    TPCE_SCALING_TABLES

---
--- SCALING TABLES
---
SELECT  @NUM_ACCT_PERM_ROWS         = NUM_ACCT_PERM_ROWS,
        @ACCT_PERM_TARGET           = ACCT_PERM_TARGET,
        @ACCT_PERM_DELTA            = NUM_ACCT_PERM_ROWS - ACCT_PERM_TARGET,
        @ACCT_PERM_DELTA_PCT        = (CAST(@ACCT_PERM_DELTA As Numeric(38,0))/ACCT_PERM_TARGET) * 100.
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('ACCOUNT PERMISSION',@NUM_ACCT_PERM_ROWS,@ACCT_PERM_TARGET,@ACCT_PERM_DELTA,@ACCT_PERM_DELTA_PCT,'S')

SELECT  @NUM_ADDRESS_ROWS           = NUM_ADDRESS_ROWS,
        @ADDRESS_TARGET             = ADDRESS_TARGET,
        @ADDRESS_DELTA              = NUM_ADDRESS_ROWS - ADDRESS_TARGET,
        @ADDRESS_DELTA_PCT          = (CAST(@ADDRESS_DELTA As Numeric(38,0))/ADDRESS_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('ADDRESS',@NUM_ADDRESS_ROWS,@ADDRESS_TARGET,@ADDRESS_DELTA,@ADDRESS_DELTA_PCT,'S')

SELECT  @NUM_BROKER_ROWS            = NUM_BROKER_ROWS,
        @BROKER_TARGET              = BROKER_TARGET,
        @BROKER_DELTA               = NUM_BROKER_ROWS - BROKER_TARGET,
        @BROKER_DELTA_PCT           = (CAST(@BROKER_DELTA As Numeric(38,0))/BROKER_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('BROKER',@NUM_BROKER_ROWS,@BROKER_TARGET,@BROKER_DELTA,@BROKER_DELTA_PCT,'S')

SELECT  @NUM_COMPANY_ROWS           = NUM_COMPANY_ROWS,
        @COMPANY_TARGET             = COMPANY_TARGET,
        @COMPANY_DELTA              = NUM_COMPANY_ROWS - COMPANY_TARGET,
        @COMPANY_DELTA_PCT          = (CAST(@COMPANY_DELTA As Numeric(38,0))/COMPANY_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('COMPANY',@NUM_COMPANY_ROWS,@COMPANY_TARGET,@COMPANY_DELTA,@COMPANY_DELTA_PCT,'S')

SELECT  @NUM_COMPANY_COMP_ROWS      = NUM_COMPANY_COMP_ROWS,
        @COMPANY_COMP_TARGET        = COMPANY_COMP_TARGET,
        @COMPANY_COMP_DELTA         = NUM_COMPANY_COMP_ROWS - COMPANY_COMP_TARGET,
        @COMPANY_COMP_DELTA_PCT     = (CAST(@COMPANY_COMP_DELTA As Numeric(38,0))/COMPANY_COMP_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('COMPANY COMPETITOR',@NUM_COMPANY_COMP_ROWS,@COMPANY_COMP_TARGET,@COMPANY_COMP_DELTA,@COMPANY_COMP_DELTA_PCT,'S')

SELECT  @NUM_CUST_ACCT_ROWS         = NUM_CUST_ACCT_ROWS,
        @CUST_ACCT_TARGET           = CUST_ACCT_TARGET,
        @CUST_ACCT_DELTA            = NUM_CUST_ACCT_ROWS - CUST_ACCT_TARGET,
        @CUST_ACCT_DELTA_PCT        = (CAST(@CUST_ACCT_DELTA As Numeric(38,0))/CUST_ACCT_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('CUSTOMER ACCOUNT',@NUM_CUST_ACCT_ROWS,@CUST_ACCT_TARGET,@CUST_ACCT_DELTA,@CUST_ACCT_DELTA_PCT,'S')

SELECT  @NUM_CUST_TAX_ROWS          = NUM_CUST_TAX_ROWS,
        @CUST_TAX_TARGET            = CUST_TAX_TARGET,
        @CUST_TAX_DELTA             = NUM_CUST_TAX_ROWS - CUST_TAX_TARGET,
        @CUST_TAX_DELTA_PCT         = (CAST(@CUST_TAX_DELTA As Numeric(38,0))/CUST_TAX_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('CUSTOMER TAXRATE',@NUM_CUST_TAX_ROWS,@CUST_TAX_TARGET,@CUST_TAX_DELTA,@CUST_TAX_DELTA_PCT,'S')

SELECT  @NUM_DAILY_MARKET_ROWS      = NUM_DAILY_MARKET_ROWS,
        @DAILY_MARKET_TARGET        = DAILY_MARKET_TARGET,
        @DAILY_MARKET_DELTA         = NUM_DAILY_MARKET_ROWS - DAILY_MARKET_TARGET,
        @DAILY_MARKET_DELTA_PCT     = (CAST(@DAILY_MARKET_DELTA As Numeric(38,0))/DAILY_MARKET_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('DAILY MARKET',@NUM_DAILY_MARKET_ROWS,@DAILY_MARKET_TARGET,@DAILY_MARKET_DELTA,@DAILY_MARKET_DELTA_PCT,'S')

SELECT  @NUM_FINANCIAL_ROWS         = NUM_FINANCIAL_ROWS,
        @FINANCIAL_TARGET           = FINANCIAL_TARGET,
        @FINANCIAL_DELTA            = NUM_FINANCIAL_ROWS - FINANCIAL_TARGET,
        @FINANCIAL_DELTA_PCT        = (CAST(@FINANCIAL_DELTA As Numeric(38,0))/FINANCIAL_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('FINANCIAL',@NUM_FINANCIAL_ROWS,@FINANCIAL_TARGET,@FINANCIAL_DELTA,@FINANCIAL_DELTA_PCT,'S')

SELECT  @NUM_LAST_TRADE_ROWS        = NUM_LAST_TRADE_ROWS,
        @LAST_TRADE_TARGET          = LAST_TRADE_TARGET,
        @LAST_TRADE_DELTA           = NUM_LAST_TRADE_ROWS - LAST_TRADE_TARGET,
        @LAST_TRADE_DELTA_PCT       = (CAST(@LAST_TRADE_DELTA As Numeric(38,0))/LAST_TRADE_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('LAST TRADE',@NUM_LAST_TRADE_ROWS,@LAST_TRADE_TARGET,@LAST_TRADE_DELTA,@LAST_TRADE_DELTA_PCT,'S')

SELECT  @NUM_NEWS_ITEM_ROWS         = NUM_NEWS_ITEM_ROWS,
        @NEWS_ITEM_TARGET           = NEWS_ITEM_TARGET,
        @NEWS_ITEM_DELTA            = NUM_NEWS_ITEM_ROWS - NEWS_ITEM_TARGET,
        @NEWS_ITEM_DELTA_PCT        = (CAST(@NEWS_ITEM_DELTA As Numeric(38,0))/NEWS_ITEM_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('NEWS ITEM',@NUM_NEWS_ITEM_ROWS,@NEWS_ITEM_TARGET,@NEWS_ITEM_DELTA,@NEWS_ITEM_DELTA_PCT,'S')

SELECT  @NUM_NEWS_XREF_ROWS         = NUM_NEWS_XREF_ROWS,
        @NEWS_XREF_TARGET           = NEWS_XREF_TARGET,
        @NEWS_XREF_DELTA            = NUM_NEWS_XREF_ROWS - NEWS_XREF_TARGET,
        @NEWS_XREF_DELTA_PCT        = (CAST(@NEWS_XREF_DELTA As Numeric(38,0))/NEWS_XREF_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('NEWS XREF',@NUM_NEWS_XREF_ROWS,@NEWS_XREF_TARGET,@NEWS_XREF_DELTA,@NEWS_XREF_DELTA_PCT,'S')

SELECT  @NUM_SECURITY_ROWS          = NUM_SECURITY_ROWS,
        @SECURITY_TARGET            = SECURITY_TARGET,
        @SECURITY_DELTA             = NUM_SECURITY_ROWS - SECURITY_TARGET,
        @SECURITY_DELTA_PCT         = (CAST(@SECURITY_DELTA As Numeric(38,0))/SECURITY_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('SECURITY',@NUM_SECURITY_ROWS,@SECURITY_TARGET,@SECURITY_DELTA,@SECURITY_DELTA_PCT,'S')

SELECT  @NUM_WATCH_ITEM_ROWS        = NUM_WATCH_ITEM_ROWS,
        @WATCH_ITEM_TARGET          = WATCH_ITEM_TARGET,
        @WATCH_ITEM_DELTA           = NUM_WATCH_ITEM_ROWS - WATCH_ITEM_TARGET,
        @WATCH_ITEM_DELTA_PCT       = (CAST(@WATCH_ITEM_DELTA As Numeric(38,0))/WATCH_ITEM_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('WATCH ITEM',@NUM_WATCH_ITEM_ROWS,@WATCH_ITEM_TARGET,@WATCH_ITEM_DELTA,@WATCH_ITEM_DELTA_PCT,'S')

SELECT  @NUM_WATCH_LIST_ROWS        = NUM_WATCH_LIST_ROWS,
        @WATCH_LIST_TARGET          = WATCH_LIST_TARGET,
        @WATCH_LIST_DELTA           = NUM_WATCH_LIST_ROWS - WATCH_LIST_TARGET,
        @WATCH_LIST_DELTA_PCT       = (CAST(@WATCH_LIST_DELTA As Numeric(38,0))/WATCH_LIST_TARGET) * 100
FROM    TPCE_SCALING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('WATCH LIST',@NUM_WATCH_LIST_ROWS,@WATCH_LIST_TARGET,@WATCH_LIST_DELTA,@WATCH_LIST_DELTA_PCT,'S')

---
--- GROWING TABLES
---
SELECT  @NUM_CASH_TRAN_ROWS         = NUM_CASH_TRAN_ROWS,
        @CASH_TRAN_TARGET           = CASH_TRAN_TARGET,
        @CASH_TRAN_DELTA            = NUM_CASH_TRAN_ROWS - CASH_TRAN_TARGET,
        @CASH_TRAN_DELTA_PCT        = (CAST(@CASH_TRAN_DELTA As Numeric(38,0))/CASH_TRAN_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('CASH TRANSACTION',@NUM_CASH_TRAN_ROWS,@CASH_TRAN_TARGET,@CASH_TRAN_DELTA,@CASH_TRAN_DELTA_PCT,'G')

SELECT  @NUM_HOLDING_ROWS           = NUM_HOLDING_ROWS,
        @HOLDING_TARGET             = HOLDING_TARGET,
        @HOLDING_DELTA              = NUM_HOLDING_ROWS - HOLDING_TARGET,
        @HOLDING_DELTA_PCT          = (CAST(@HOLDING_DELTA As Numeric(38,0))/HOLDING_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('HOLDING',@NUM_HOLDING_ROWS,@HOLDING_TARGET,@HOLDING_DELTA,@HOLDING_DELTA_PCT,'G')

SELECT  @NUM_HOLDING_HIST_ROWS      = NUM_HOLDING_HIST_ROWS,
        @HOLDING_HIST_TARGET        = HOLDING_HIST_TARGET,
        @HOLDING_HIST_DELTA         = NUM_HOLDING_HIST_ROWS - HOLDING_HIST_TARGET,
        @HOLDING_HIST_DELTA_PCT     = (CAST(@HOLDING_HIST_DELTA As Numeric(38,0))/HOLDING_HIST_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('HOLDING HISTORY',@NUM_HOLDING_HIST_ROWS,@HOLDING_HIST_TARGET,@HOLDING_HIST_DELTA,@HOLDING_HIST_DELTA_PCT,'G')

SELECT  @NUM_HOLDING_SUMM_ROWS      = NUM_HOLDING_SUMM_ROWS,
        @HOLDING_SUMM_TARGET        = HOLDING_SUMM_TARGET,
        @HOLDING_SUMM_DELTA         = NUM_HOLDING_SUMM_ROWS - HOLDING_SUMM_TARGET,
        @HOLDING_SUMM_DELTA_PCT     = (CAST(@HOLDING_SUMM_DELTA As Numeric(38,0))/HOLDING_SUMM_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('HOLDING SUMMARY',@NUM_HOLDING_SUMM_ROWS,@HOLDING_SUMM_TARGET,@HOLDING_SUMM_DELTA,@HOLDING_SUMM_DELTA_PCT,'G')

SELECT  @NUM_SETTLEMENT_ROWS        = NUM_SETTLEMENT_ROWS,
        @SETTLEMENT_TARGET          = SETTLEMENT_TARGET,
        @SETTLEMENT_DELTA           = NUM_SETTLEMENT_ROWS - SETTLEMENT_TARGET,
        @SETTLEMENT_DELTA_PCT       = (CAST(@SETTLEMENT_DELTA As Numeric(38,0))/SETTLEMENT_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('SETTLEMENT',@NUM_SETTLEMENT_ROWS,@SETTLEMENT_TARGET,@SETTLEMENT_DELTA,@SETTLEMENT_DELTA_PCT,'G')

SELECT  @NUM_TRADE_ROWS             = NUM_TRADE_ROWS,
        @TRADE_TARGET               = TRADE_TARGET,
        @TRADE_DELTA                = NUM_TRADE_ROWS - TRADE_TARGET,
        @TRADE_DELTA_PCT            = (CAST(@TRADE_DELTA As Numeric(38,0))/TRADE_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('TRADE',@NUM_TRADE_ROWS,@TRADE_TARGET,@TRADE_DELTA,@TRADE_DELTA_PCT,'G')

SELECT  @NUM_TRADE_HIST_ROWS        = NUM_TRADE_HIST_ROWS,
        @TRADE_HIST_TARGET          = TRADE_HIST_TARGET,
        @TRADE_HIST_DELTA           = NUM_TRADE_HIST_ROWS - TRADE_HIST_TARGET,
        @TRADE_HIST_DELTA_PCT       = (CAST(@TRADE_HIST_DELTA As Numeric(38,0))/TRADE_HIST_TARGET) * 100
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('TRADE HISTORY',@NUM_TRADE_HIST_ROWS,@TRADE_HIST_TARGET,@TRADE_HIST_DELTA,@TRADE_HIST_DELTA_PCT,'G')

SELECT  @NUM_TRADE_REQUEST_ROWS     = NUM_TRADE_REQUEST_ROWS,
        @TRADE_REQUEST_TARGET       = TRADE_REQUEST_TARGET,
        @TRADE_REQUEST_DELTA        = NUM_TRADE_REQUEST_ROWS - TRADE_REQUEST_TARGET,
        @TRADE_REQUEST_DELTA_PCT    = 0
FROM    TPCE_GROWING_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('TRADE REQUEST',@NUM_TRADE_REQUEST_ROWS,@TRADE_REQUEST_TARGET,@TRADE_REQUEST_DELTA,@TRADE_REQUEST_DELTA_PCT,'G')

---
--- GROWING TABLES
---
SELECT  @NUM_CHARGE_ROWS        = NUM_CHARGE_ROWS,
        @CHARGE_TARGET          = CHARGE_TARGET,
        @CHARGE_DELTA           = NUM_CHARGE_ROWS - CHARGE_TARGET,
        @CHARGE_DELTA_PCT       = (NUM_CHARGE_ROWS - CHARGE_TARGET)/CHARGE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('CHARGE',@NUM_CHARGE_ROWS,@CHARGE_TARGET,@CHARGE_DELTA,@CHARGE_DELTA_PCT,'F')

SELECT  @NUM_COMM_RATE_ROWS     = NUM_COMM_RATE_ROWS,
        @COMM_RATE_TARGET       = COMM_RATE_TARGET,
        @COMM_RATE_DELTA        = NUM_COMM_RATE_ROWS - COMM_RATE_TARGET,
        @COMM_RATE_DELTA_PCT    = (NUM_COMM_RATE_ROWS - COMM_RATE_TARGET)/COMM_RATE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('COMMISSION RATE',@NUM_COMM_RATE_ROWS,@COMM_RATE_TARGET,@COMM_RATE_DELTA,@COMM_RATE_DELTA_PCT,'F')

SELECT  @NUM_EXCHANGE_ROWS      = NUM_EXCHANGE_ROWS,
        @EXCHANGE_TARGET        = EXCHANGE_TARGET,
        @EXCHANGE_DELTA         = NUM_EXCHANGE_ROWS - EXCHANGE_TARGET,
        @EXCHANGE_DELTA_PCT     = (NUM_EXCHANGE_ROWS - EXCHANGE_TARGET)/EXCHANGE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('EXCHANGE',@NUM_EXCHANGE_ROWS,@EXCHANGE_TARGET,@EXCHANGE_DELTA,@EXCHANGE_DELTA_PCT,'F')

SELECT  @NUM_INDUSTRY_ROWS      = NUM_INDUSTRY_ROWS,
        @INDUSTRY_TARGET        = INDUSTRY_TARGET,
        @INDUSTRY_DELTA         = NUM_INDUSTRY_ROWS - INDUSTRY_TARGET,
        @INDUSTRY_DELTA_PCT     = (NUM_INDUSTRY_ROWS - INDUSTRY_TARGET)/INDUSTRY_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('INDUSTRY',@NUM_INDUSTRY_ROWS,@INDUSTRY_TARGET,@INDUSTRY_DELTA,@INDUSTRY_DELTA_PCT,'F')

SELECT  @NUM_SECTOR_ROWS        = NUM_SECTOR_ROWS,
        @SECTOR_TARGET          = SECTOR_TARGET,
        @SECTOR_DELTA           = NUM_SECTOR_ROWS - SECTOR_TARGET,
        @SECTOR_DELTA_PCT       = (NUM_SECTOR_ROWS - SECTOR_TARGET)/SECTOR_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('SECTOR',@NUM_SECTOR_ROWS,@SECTOR_TARGET,@SECTOR_DELTA,@SECTOR_DELTA_PCT,'F')

SELECT  @NUM_STATUS_TYPE_ROWS   = NUM_STATUS_TYPE_ROWS,
        @STATUS_TYPE_TARGET     = STATUS_TYPE_TARGET,
        @STATUS_TYPE_DELTA      = NUM_STATUS_TYPE_ROWS - STATUS_TYPE_TARGET,
        @STATUS_TYPE_DELTA_PCT  = (NUM_STATUS_TYPE_ROWS - STATUS_TYPE_TARGET)/STATUS_TYPE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('STATUS TYPE',@NUM_STATUS_TYPE_ROWS,@STATUS_TYPE_TARGET,@STATUS_TYPE_DELTA,@STATUS_TYPE_DELTA_PCT,'F')

SELECT  @NUM_TAXRATE_ROWS       = NUM_TAXRATE_ROWS,
        @TAXRATE_TARGET         = TAXRATE_TARGET,
        @TAXRATE_DELTA          = NUM_TAXRATE_ROWS - TAXRATE_TARGET,
        @TAXRATE_DELTA_PCT      = (NUM_TAXRATE_ROWS - TAXRATE_TARGET)/TAXRATE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('TAX RATE',@NUM_TAXRATE_ROWS,@TAXRATE_TARGET,@TAXRATE_DELTA,@TAXRATE_DELTA_PCT,'F')

SELECT  @NUM_TRADE_TYPE_ROWS    = NUM_TRADE_TYPE_ROWS,
        @TRADE_TYPE_TARGET      = TRADE_TYPE_TARGET,
        @TRADE_TYPE_DELTA       = NUM_TRADE_TYPE_ROWS - TRADE_TYPE_TARGET,
        @TRADE_TYPE_DELTA_PCT   = (NUM_TRADE_TYPE_ROWS - TRADE_TYPE_TARGET)/TRADE_TYPE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('TRADE TYPE',@NUM_TRADE_TYPE_ROWS,@TRADE_TYPE_TARGET,@TRADE_TYPE_DELTA,@TRADE_TYPE_DELTA_PCT,'F')

SELECT  @NUM_ZIP_CODE_ROWS      = NUM_ZIP_CODE_ROWS,
        @ZIP_CODE_TARGET        = ZIP_CODE_TARGET,
        @ZIP_CODE_DELTA         = NUM_ZIP_CODE_ROWS - ZIP_CODE_TARGET,
        @ZIP_CODE_DELTA_PCT     = (NUM_ZIP_CODE_ROWS - ZIP_CODE_TARGET)/ZIP_CODE_TARGET
FROM    TPCE_FIXED_TABLES
INSERT INTO TPCE_AUDIT_OUTPUT VALUES ('ZIP CODE',@NUM_ZIP_CODE_ROWS,@ZIP_CODE_TARGET,@ZIP_CODE_DELTA,@ZIP_CODE_DELTA_PCT,'F')


PRINT   ''
PRINT   ''
PRINT   '--------------------------------'
PRINT   '|        Scaling Tables        |'
PRINT   '--------------------------------'
PRINT   ' '

SELECT  TABLE_NAME As 'Table Name',
        ROWS_ACTUAL As 'Rows Loaded',
        ROWS_TARGET As 'Target Rows',
        DELTA As 'Rows Delta',
        PCT_DELTA As 'Delta Percentage'
FROM    TPCE_AUDIT_OUTPUT
WHERE   TABLE_TYPE  = 'S'

PRINT   ''
PRINT   ''
PRINT   '--------------------------------'
PRINT   '|        Growing Tables        |'
PRINT   '--------------------------------'
PRINT   ' '

SELECT  TABLE_NAME As 'Table Name',
        ROWS_ACTUAL As 'Rows Loaded',
        ROWS_TARGET As 'Target Rows',
        DELTA As 'Rows Delta',
        PCT_DELTA As 'Delta Percentage'
FROM    TPCE_AUDIT_OUTPUT
WHERE   TABLE_TYPE  = 'G'

PRINT   ''
PRINT   ''
PRINT   '--------------------------------'
PRINT   '|         Fixed Tables         |'
PRINT   '--------------------------------'
PRINT   ' '

SELECT  TABLE_NAME As 'Table Name',
        ROWS_ACTUAL As 'Rows Loaded',
        ROWS_TARGET As 'Target Rows',
        DELTA As 'Rows Delta',
        PCT_DELTA As 'Delta Percentage'
FROM    TPCE_AUDIT_OUTPUT
WHERE   TABLE_TYPE  = 'F'

PRINT   ''
PRINT   ''
PRINT   '-----------------------------------'
PRINT   '|   TPC-E Database Check - End    |'
PRINT   '-----------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
