----------------------------------------------------------------
--                                                              
--  File:   CONSISTENCY.SQL                                     
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
--          Please use the -e option to echo these SQL          
--          statements into the output files                    
--                                                              
----------------------------------------------------------------
USE tpce
GO 

PRINT   'CONSISTENCY TESTS START'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '
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

SELECT  COUNT_BIG(*) As 'Number of Customers'
FROM    CUSTOMER
PRINT   ' '
GO

--
-- Consistency Condition 1
-- Entries in the BROKER and TRADE tables must satisfy the relatiinship:
--      B_NUM_TRADES = COUNT(*)
-- For each broker defined by:
--      (B_ID = CA_B_ID) AND
--      (CA_ID = T_CA_ID) AND
--      (T_ST_ID = 'CMPT'
--
--
-- Consistency Condition 2
-- Entries in the BROKER and TRADE tables must satisfy the relatiinship:
--      B_COM_TOTAL = COUNT(*)
-- For each broker defined by:
--      (B_ID = CA_B_ID) AND
--      (CA_ID = T_CA_ID) AND
--      (T_ST_ID = 'CMPT'
--
-- Combine Tests 1 and 2

DECLARE @start_dts  DATETIME,
        @end_dts    DATETIME

SET     @start_dts  = GETDATE()

PRINT   'CONSISTENCY CONDITION 1 & 2 START'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '

SELECT  B_ID,
        B_NUM_TRADES,
        COUNT_BIG(*) As 'Trades',
        (B_NUM_TRADES - COUNT_BIG(*)) As 'Trades Diff',
        B_COMM_TOTAL,
        SUM(T_COMM) As 'Sum of T_COMM',
        (B_COMM_TOTAL - SUM(T_COMM)) As 'T_COMM Diff'
FROM    BROKER JOIN
        CUSTOMER_ACCOUNT ON B_ID = CA_B_ID JOIN
        TRADE ON CA_ID = T_CA_ID
WHERE   T_ST_ID = 'CMPT'
GROUP   BY B_ID, B_NUM_TRADES, B_COMM_TOTAL
ORDER   BY B_ID

SET     @end_dts    = GETDATE()

PRINT   'CONSISTENCY CONDITION 1 & 2 END'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '
PRINT   'CONSISTENCY CONDITION 1 & 2 Elapsed Seconds'
SELECT  DATEDIFF(ss, @start_dts, @end_dts)
PRINT   ' '
PRINT   ' '
GO

--
-- Consistency Condition 3
-- Entries in the HOLDING_SUMMARY and HOLDING tables must satisfy the relatiinship:
--      HS_QTY = SUM(H_QTY)
-- For each holding summary defined by:
--      (HS_CA_ID = H_CA_ID) AND
--      (HS_S_SYMB = H_S_SYMB)
--
DECLARE @start_dts  DATETIME,
        @end_dts    DATETIME

SET     @start_dts  = GETDATE()

PRINT   'CONSISTENCY CONDITION 3 START'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '

SELECT  CA_B_ID As Broker_ID, 
        SUM(Holding_Quantity) As Sum_Holding_Quantities,
        SUM(Holding_Summary_Quantity) As Sum_Holding_Summary_Quantites,
        SUM(Delta) As Sum_Deltas, SUM(ABS(Delta)) As Sum_Absolute_Value_Deltas
FROM    CUSTOMER_ACCOUNT, 
(
SELECT  H_CA_ID As Customer_Account_ID,
        H_S_SYMB As Symbol,
        SUM(H_QTY) As Holding_Quantity,
        HS_QTY As Holding_Summary_Quantity,
        SUM(H_QTY) - HS_QTY As Delta
FROM    HOLDING,
        HOLDING_SUMMARY
WHERE   H_CA_ID     = HS_CA_ID AND
        H_S_SYMB    = HS_S_SYMB
GROUP   BY H_CA_ID, H_S_SYMB, HS_QTY
) AS HS
WHERE   CA_ID = Customer_Account_ID
GROUP   BY CA_B_ID
ORDER   BY CA_B_ID

SET     @end_dts    = GETDATE()

PRINT   'CONSISTENCY CONDITION 3 END'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '
PRINT   'CONSISTENCY CONDITION 3 Elapsed Seconds'
SELECT  DATEDIFF(ss, @start_dts, @end_dts)
PRINT   ' '
PRINT   ' '
GO

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

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

PRINT   'CONSISTENCY TESTS END'
SELECT  CONVERT(char(30), GETDATE(), 21)
PRINT   ' '
GO


