------------------------------------------------------------------
--                                                              --
--  File:   Isolation2_S1.SQL                                   --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1025      --
--          Copyright Microsoft  2010, 2011, 2012               --
--                                                              --
--  Implements TPC-E Isolation test #2                          --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

DECLARE @num_custs      bigint,
        @acct_id        bigint,
        @cnt            bigint,
        @val            int,
        @symbol         varchar(15),
        @hs_qty         numeric(6)

--- get the number of customer accounts in the database
SELECT  @num_custs      = COUNT(*) FROM CUSTOMER

--- select a random customer account for use in the test
SET     @acct_id        = CONVERT(bigint, RAND() * 10 * @num_custs) + 43000000001
SELECT  TOP 1
        @acct_id        = CA_ID
FROM    CUSTOMER_ACCOUNT
WHERE   CA_ID           <= @acct_id
ORDER   BY 1 DESC

--- select a holding for the customer account
SELECT  @cnt        = COUNT_BIG(*) 
FROM    HOLDING_SUMMARY
WHERE   HS_CA_ID    = @acct_id    			  			      
				
SELECT  @val    = (@cnt + 1) / 2    

SET     rowcount @val

--- now get the symbol
SELECT  @symbol = HS_S_SYMB,
        @hs_qty = HS_QTY
FROM    HOLDING_SUMMARY
WHERE   HS_CA_ID    = @acct_id    

SET     rowcount 0

SELECT  @acct_id As 'Account ID',
        @symbol As 'Symbol',
        @hs_qty As 'Holding Quantity'

------------------------------------------------
--- RUN TRADEORDER_ISO2
------------------------------------------------
EXEC    TradeOrder_Iso2     @acct_id,
                            @symbol

GO
