------------------------------------------------------------------
--                                                              --
--  File:   TradeLookup.SQL                                     --
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      --
--          Copyright Microsoft, 2008-2015                  --
--                                                              --
------------------------------------------------------------------
USE tpce
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'TradeLookupFrame1' AND
                    type    = 'P')
DROP PROCEDURE  TradeLookupFrame1
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'TradeLookupFrame2' AND
                    type    = 'P')
DROP PROCEDURE  TradeLookupFrame2
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'TradeLookupFrame3' AND
                    type    = 'P')
DROP PROCEDURE  TradeLookupFrame3
GO

IF EXISTS(  SELECT  name
            FROM    sysobjects
            WHERE   name    = 'TradeLookupFrame4' AND
                    type    = 'P')
DROP PROCEDURE  TradeLookupFrame4
GO

--
-- Trade Lookup Frame 1 is responsible for retrieving
-- information about the specified maximum of 20 trade IDs.
--
CREATE  PROCEDURE   TradeLookupFrame1
                        @max_trades    int,         
                        @trade_id1     bigint,
                        @trade_id2     bigint,
                        @trade_id3     bigint,
                        @trade_id4     bigint,
                        @trade_id5     bigint,
                        @trade_id6     bigint,
                        @trade_id7     bigint,
                        @trade_id8     bigint,
                        @trade_id9     bigint,
                        @trade_id10    bigint,
                        @trade_id11    bigint,
                        @trade_id12    bigint,
                        @trade_id13    bigint,
                        @trade_id14    bigint,
                        @trade_id15    bigint,
                        @trade_id16    bigint,
                        @trade_id17    bigint,
                        @trade_id18    bigint,
                        @trade_id19    bigint,
                        @trade_id20    bigint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @t_exec_name        varchar(49),
            @t_bid_price        numeric(8,2),
            @t_trade_price      numeric(8,2),
            @t_is_cash          tinyint,
            @t_is_market        tinyint,
            @se_cash_type       varchar(40),
            @se_cash_due_date   date,
            @se_amount          numeric(10,2),
            @ct_amount          numeric(10,2),
            @ct_name            varchar(100),
            @ct_dts             datetime,
            @num_found          int

    DECLARE @trades TABLE   (SEQ    tinyint         not NULL,
                             T_ID   bigint          NULL,
                             PRIMARY KEY (SEQ))

    INSERT INTO @trades 
            SELECT 1, @trade_id1
            UNION ALL
            SELECT 2, @trade_id2
            UNION ALL
            SELECT 3, @trade_id3
            UNION ALL
            SELECT 4, @trade_id4
            UNION ALL
            SELECT 5, @trade_id5
            UNION ALL
            SELECT 6, @trade_id6
            UNION ALL
            SELECT 7, @trade_id7
            UNION ALL
            SELECT 8, @trade_id8
            UNION ALL
            SELECT 9, @trade_id9
            UNION ALL
            SELECT 10, @trade_id10
            UNION ALL
            SELECT 11, @trade_id11
            UNION ALL
            SELECT 12, @trade_id12
            UNION ALL
            SELECT 13, @trade_id13
            UNION ALL
            SELECT 14, @trade_id14
            UNION ALL
            SELECT 15, @trade_id15
            UNION ALL
            SELECT 16, @trade_id16
            UNION ALL
            SELECT 17, @trade_id17
            UNION ALL
            SELECT 18, @trade_id18
            UNION ALL
            SELECT 19, @trade_id19
            UNION ALL
            SELECT 20, @trade_id20

    DECLARE Trade_List CURSOR LOCAL FORWARD_ONLY SCROLL_LOCKS
        FOR SELECT  T_ID         /* should be the same as maximum posssible max_trades */
            FROM    @trades
            WHERE   SEQ <= @max_trades
            ORDER   BY SEQ

    SET     @num_found  = 0

    OPEN    Trade_List 
	
    -- initial fetch before the loop
    FETCH FROM Trade_List INTO @trade_id 

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- set cash transaction local variables to NULL
        SELECT  @ct_amount  = NULL,
                @ct_name    = NULL,
                @ct_dts     = NULL,
                @num_found  = @num_found + 1,
                @max_trades = @max_trades - 1

        SELECT  @t_exec_name    = T_EXEC_NAME,
                @t_bid_price    = T_BID_PRICE,
                @t_trade_price  = T_TRADE_PRICE,
                @t_is_cash      = T_IS_CASH,
                @t_is_market    = TT_IS_MRKT
        FROM    TRADE,
                TRADE_TYPE
        WHERE   T_ID            = @trade_id AND
                T_TT_ID         = TT_ID

        SELECT  @se_cash_type       = SE_CASH_TYPE,
                @se_cash_due_date   = SE_CASH_DUE_DATE,
                @se_amount          = SE_AMT
        FROM    SETTLEMENT
        WHERE   SE_T_ID             = @trade_id

        -- if this is a cash transaction,
        -- get information from CASH_TRANSACTION
        IF (@t_is_cash <> 0)
            SELECT  @ct_amount  = CT_AMT,
                    @ct_name    = CT_NAME,
                    @ct_dts     = CT_DTS
            FROM    CASH_TRANSACTION
            WHERE   CT_T_ID     = @trade_id

         -- return as a rowset
        SELECT  @t_bid_price,
                @ct_amount,
                @ct_dts,
                @ct_name,
                @t_exec_name,
                @t_is_cash,
                @t_is_market,
                @se_amount,
                @se_cash_due_date,
                @se_cash_type,
                @t_trade_price
			
        -- get the trade history 2 or 3 rows for the trade
        SELECT  TOP 3
                TH_DTS,
                TH_ST_ID
        FROM    TRADE_HISTORY
        WHERE   TH_T_ID = @trade_id
        ORDER   BY TH_DTS

        FETCH FROM Trade_List INTO @trade_id
    END -- WHILE

    SELECT  @num_found
        
    CLOSE       Trade_List 
    DEALLOCATE  Trade_List
	
    COMMIT TRANSACTION
END
GO

--
-- Trade Lookup Frame 2 is responsible for retrieving information
-- for up to max_trades trades executed for the specified account 
-- at or after the specified time.
--
CREATE  PROCEDURE   TradeLookupFrame2
                        @max_trades             int,
                        @acct_id                bigint,
                        @start_trade_dts        datetime,
                        @end_trade_dts          datetime
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @t_exec_name        varchar(49),
            @t_bid_price        numeric(8,2),
            @t_trade_price      numeric(8,2),
            @t_is_cash          tinyint,
            @se_cash_type       varchar(40),
            @se_cash_due_date   date,
            @se_amount          numeric(10,2),
            @ct_amount          numeric(10,2),
            @ct_name            varchar(100),
            @ct_dts             datetime,
            @num_found          int

    DECLARE Trade_List CURSOR LOCAL FORWARD_ONLY SCROLL_LOCKS
        FOR SELECT  TOP 20         /* should be the same as maximum posssible max_trades */
                    T_ID,
                    T_EXEC_NAME,
                    T_BID_PRICE,
                    T_TRADE_PRICE,
                    T_IS_CASH
            FROM    TRADE
            WHERE   T_CA_ID     =  @acct_id AND
                    T_DTS       BETWEEN @start_trade_dts AND @end_trade_dts
            ORDER   BY T_DTS

    SET     @num_found  = 0

    OPEN    Trade_List 
	
    -- initial fetch before the loop
    FETCH   FROM    Trade_List
            INTO    @trade_id,
                    @t_exec_name,
                    @t_bid_price,
                    @t_trade_price,
                    @t_is_cash

    WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)
    BEGIN
        -- set cash transaction local variables to NULL
        SELECT  @ct_amount  = NULL,
                @ct_name    = NULL,
                @ct_dts     = NULL,
                @num_found  = @num_found + 1,
                @max_trades = @max_trades - 1

        SELECT  @se_cash_type       = SE_CASH_TYPE,
                @se_cash_due_date   = SE_CASH_DUE_DATE,
                @se_amount          = SE_AMT
        FROM    SETTLEMENT
        WHERE   SE_T_ID             = @trade_id

        -- if this is a cash transaction,
        -- get information from CASH_TRANSACTION
        IF (@t_is_cash <> 0)
            SELECT  @ct_amount  = CT_AMT,
                    @ct_name    = CT_NAME,
                    @ct_dts     = CT_DTS
            FROM    CASH_TRANSACTION
            WHERE   CT_T_ID     = @trade_id

        -- return as a rowset
        SELECT  @t_bid_price,
                @ct_amount,
                @ct_dts,
                @ct_name,
                @t_exec_name,
                @t_is_cash,
                @se_amount,
                @se_cash_due_date,
                @se_cash_type,                        
                @trade_id,      
                @t_trade_price
  	
         -- get the trade history of 2-3 rows for this trade
        SELECT  TOP 3
                TH_DTS,
                TH_ST_ID
        FROM    TRADE_HISTORY
        WHERE   TH_T_ID = @trade_id
        ORDER   BY TH_DTS

        FETCH   FROM    Trade_List
                INTO    @trade_id,
                        @t_exec_name,
                        @t_bid_price,
                        @t_trade_price,
                        @t_is_cash
    END -- WHILE

    CLOSE       Trade_List 
    DEALLOCATE  Trade_List

    SELECT  @num_found

    IF  (@num_found = 0)
    BEGIN   --  Show that earlier initial trade exists for this account
        INSERT  INTO TL_TU_WARNINGS
        SELECT  TOP 1
                getdate(), 'TL2', 
                @max_trades, @acct_id, NULL, @start_trade_dts, @end_trade_dts,
                T_ID, T_CA_ID, T_S_SYMB, T_DTS
        FROM    TRADE
        WHERE   T_CA_ID = @acct_id AND
                T_DTS   < @start_trade_dts
        ORDER   BY T_DTS DESC
    END

    COMMIT TRANSACTION
END
GO

--
-- Trade Lookup Frame 3 returns up to max_trades trades for a given
-- security symbol on or after a specified point in time.
--
CREATE  PROCEDURE   TradeLookupFrame3
                        @max_trades         int,
                        @symbol             varchar(15),
                        @start_trade_dts    datetime,
                        @end_trade_dts      datetime,
                        @max_acct_id        bigint
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @t_ca_id            bigint,
            @t_exec_name        varchar(49),
            @t_is_cash          tinyint,
            @t_trade_price      numeric(8,2),
            @t_qty              numeric(6),
            @t_dts              datetime,
            @t_tt_id            char(3),
            @se_cash_type       varchar(40),
            @se_cash_due_date   date,
            @se_amount          numeric(10,2),
            @ct_amount          numeric(10,2),
            @ct_name            varchar(100),
            @ct_dts             datetime,
            @num_found          int

    DECLARE Trade_List CURSOR 
        LOCAL FORWARD_ONLY SCROLL_LOCKS
        FOR SELECT  TOP 20         /* should be the same as maximum posssible max_trades */
                    T_ID,
                    T_CA_ID,
                    T_EXEC_NAME,
                    T_IS_CASH,
                    T_TRADE_PRICE,
                    T_QTY,
                    T_DTS,
                    T_TT_ID
            FROM    TRADE 
            WHERE   T_S_SYMB    =  @symbol AND
                    T_DTS       BETWEEN @start_trade_dts AND @end_trade_dts
            ORDER   BY T_DTS

    SET     @num_found  = 0

    OPEN    Trade_List 
	
    -- initial fetch before the loop
    FETCH FROM  Trade_List 
          INTO  @trade_id, 
                @t_ca_id,
                @t_exec_name,
                @t_is_cash,
                @t_trade_price,
                @t_qty,
                @t_dts,
                @t_tt_id

    WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)
    BEGIN
        -- set cash transaction local variables to NULL
        SELECT  @ct_amount  = NULL,
                @ct_name    = NULL,
                @num_found  = @num_found + 1,
                @max_trades = @max_trades - 1

        SELECT  @se_cash_type       = SE_CASH_TYPE,
                @se_cash_due_date   = SE_CASH_DUE_DATE,
                @se_amount          = SE_AMT
        FROM    SETTLEMENT
        WHERE   SE_T_ID             = @trade_id

        -- if this is a cash transaction,
        -- get information from CASH_TRANSACTION
        IF (@t_is_cash <> 0)
            SELECT  @ct_amount  = CT_AMT,
                    @ct_name    = CT_NAME,
                    @ct_dts     = CT_DTS
            FROM    CASH_TRANSACTION
            WHERE   CT_T_ID     = @trade_id

        -- return as a rowset
        SELECT  @t_ca_id,
                @ct_amount,
                @ct_dts,
                @ct_name,
                @t_exec_name,
                @t_is_cash,
                @t_trade_price,
                @t_qty,
                @se_amount,
                @se_cash_due_date,
                @se_cash_type,
                @t_dts,
                @trade_id,
                @t_tt_id
  	
        -- get the trade history of 2-3 rows for this trade
        SELECT  TOP 3
                TH_DTS,
                TH_ST_ID
        FROM    TRADE_HISTORY
        WHERE   TH_T_ID = @trade_id
        ORDER   BY TH_DTS

        IF (@max_trades > 0)
            FETCH FROM  Trade_List 
                  INTO  @trade_id, 
                        @t_ca_id,
                        @t_exec_name,
                        @t_is_cash,
                        @t_trade_price,
                        @t_qty,
                        @t_dts,
                        @t_tt_id
    END -- WHILE (@@FETCH_STATUS = 0 AND @max_trades > 0)

    CLOSE       Trade_List 
    DEALLOCATE  Trade_List
	
    SELECT  @num_found

    IF  (@num_found = 0)
    BEGIN   --  Show that earlier initial trade exists for this symbol
        INSERT  INTO TL_TU_WARNINGS
        SELECT  TOP 1
                getdate(), 'TL3', 
                @max_trades, NULL, @symbol, @start_trade_dts, @end_trade_dts,
                T_ID, T_CA_ID, T_S_SYMB, T_DTS
        FROM    TRADE
        WHERE   T_S_SYMB = @symbol AND
                T_DTS    < @start_trade_dts
        ORDER   BY T_DTS DESC
    END

    COMMIT TRANSACTION
END
GO

--
-- Trade Lookup Frame 4 identifies the first trade for
-- the specified account on or after the specified time.
-- Up to 20 rows in the HOLDING_HISTORY with a matching 
-- trade ID are returned.
--
CREATE  PROCEDURE   TradeLookupFrame4
                        @acct_id            bigint,  
                        @trade_dts          datetime
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRANSACTION

    DECLARE @trade_id           bigint,
            @num_trades_found	int

    SELECT  TOP 1
            @trade_id   = T_ID
    FROM    TRADE
    WHERE   T_CA_ID     =  @acct_id AND
            T_DTS       >= @trade_dts 
    ORDER   BY T_DTS ASC
    
    SET		@num_trades_found	= @@ROWCOUNT
    
    IF (@num_trades_found = 0)
        SET @trade_id = 0
        
    SELECT  @trade_id,
            @num_trades_found

    SELECT  TOP 20
            HH_H_T_ID,
            HH_T_ID,
            HH_BEFORE_QTY,
            HH_AFTER_QTY
    FROM    HOLDING_HISTORY
    WHERE   HH_H_T_ID IN (SELECT    HH_H_T_ID
                          FROM      HOLDING_HISTORY
                          WHERE     HH_T_ID = @trade_id)

    IF (@trade_id = 0) 
    BEGIN   --  Show that earlier initial trade exists for this account
        INSERT  INTO TL_TU_WARNINGS
        SELECT  TOP 1
                getdate(), 'TL4', 
                NULL, @acct_id, NULL, @trade_dts, NULL,
                T_ID, T_CA_ID, T_S_SYMB, T_DTS
        FROM    TRADE
        WHERE   T_CA_ID = @acct_id AND
                T_DTS   < @trade_dts
        ORDER   BY T_DTS DESC
    END

    COMMIT TRANSACTION	
END
GO
