------------------------------------------------------------------
--                                                              --
--  File:   Isolation4_S1.SQL                                   --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1025      --
--          Copyright Microsoft  2010, 2011, 2012               --
--                                                              --
--  Implements TPC-E Isolation test #4                          --
--  P1 Test in Read-Write                                       --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

DECLARE @num_custs      bigint,
        @num_companies  bigint,
        @symbol         varchar(15),
        @acct_id        bigint,
        @cust_id        bigint,
        @company_id     bigint,
        @sort_dir       tinyint,
        @cnt            bigint

--- get the number of customers in the database
SELECT  @num_custs      = COUNT_BIG(*) FROM CUSTOMER

--- select a random customer ID for use in the test
SET     @cust_id        = CONVERT(bigint, RAND() * @num_custs) + 4300000001
--- select a customer account for the customer
SELECT  @cnt        = COUNT_BIG(*) 
FROM    CUSTOMER_ACCOUNT
WHERE   CA_C_ID    = @cust_id    			  			      

SELECT  @sort_dir   = CONVERT(bigint, RAND() * 20 * @cnt) % 2
				
--- now get the customer account ID
IF @sort_dir = 0
    SELECT  TOP 1
            @acct_id    = CA_ID
    FROM    CUSTOMER_ACCOUNT
    WHERE   CA_C_ID    = @cust_id    
    ORDER   BY CA_ID ASC
ELSE
    SELECT  TOP 1
            @acct_id    = CA_ID
    FROM    CUSTOMER_ACCOUNT
    WHERE   CA_C_ID    = @cust_id    
    ORDER   BY CA_ID DESC

--- execute Customer Posistion_Iso4
EXEC CustomerPosition_Iso4    @cust_id, ' ', @acct_id

--- get the number of companies
SELECT  @num_companies  = COUNT(*) FROM COMPANY

--- generate a random company ID
SET     @company_id     = CONVERT(bigint, RAND() * @num_companies) + 4300000001

SELECT  TOP 1
        @symbol = S_SYMB
FROM    SECURITY JOIN COMPANY ON S_CO_ID = CO_ID
WHERE   CO_ID   = @company_id

--- to facilitate the rest of the test, we need to enter a trade order for this customer account
EXEC TradeOrder_Iso4    @acct_id,
                        @symbol

--- get the CA_BAL for the customer account we used
SELECT  'Isolation 4 - S1 Output:',
        CA_BAL As 'CA Balance'
FROM    CUSTOMER_ACCOUNT
WHERE   CA_ID = @acct_id

GO
