------------------------------------------------------------------
--                                                              --
--  File:   BrokerVolume.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    = 'BrokerVolumeFrame1' AND
                    type    = 'P')
DROP PROCEDURE  BrokerVolumeFrame1
GO

---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- BrokerVolumeFrame1 is responsible retrieving 
-- the total volume that could be potentially 
-- generated by each qualifying broker.
CREATE PROCEDURE    BrokerVolumeFrame1
                        @broker1        varchar(49),  /* IN list of brokers */ 
                        @broker2        varchar(49),
                        @broker3        varchar(49),
                        @broker4        varchar(49),
                        @broker5        varchar(49),
                        @broker6        varchar(49),
                        @broker7        varchar(49),
                        @broker8        varchar(49),
                        @broker9        varchar(49),
                        @broker10       varchar(49),
                        @broker11       varchar(49),
                        @broker12       varchar(49),
                        @broker13       varchar(49),
                        @broker14       varchar(49),
                        @broker15       varchar(49),
                        @broker16       varchar(49),
                        @broker17       varchar(49),
                        @broker18       varchar(49),
                        @broker19       varchar(49),
                        @broker20       varchar(49),
                        @broker21       varchar(49),
                        @broker22       varchar(49),
                        @broker23       varchar(49),
                        @broker24       varchar(49),
                        @broker25       varchar(49),
                        @broker26       varchar(49),
                        @broker27       varchar(49),
                        @broker28       varchar(49),
                        @broker29       varchar(49),
                        @broker30       varchar(49),
                        @broker31       varchar(49),
                        @broker32       varchar(49),
                        @broker33       varchar(49),
                        @broker34       varchar(49),
                        @broker35       varchar(49),
                        @broker36       varchar(49),
                        @broker37       varchar(49),
                        @broker38       varchar(49),
                        @broker39       varchar(49),
                        @broker40       varchar(49),
                        @sector_name    char(30)    /* IN sector's name */ 
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    BEGIN TRANSACTION

    SELECT  B_NAME,
            SUM(TR_QTY * TR_BID_PRICE)
    FROM    BROKER,
            TRADE_REQUEST,
            SECURITY,
            COMPANY,
            INDUSTRY,
            SECTOR
    WHERE   B_NAME      IN (@broker1,  @broker2,  @broker3,  @broker4,  @broker5,
                            @broker6,  @broker7,  @broker8,  @broker9,  @broker10,
                            @broker11, @broker12, @broker13, @broker14, @broker15,
                            @broker16, @broker17, @broker18, @broker19, @broker20,
                            @broker21, @broker22, @broker23, @broker24, @broker25,
                            @broker26, @broker27, @broker28, @broker29, @broker30,
                            @broker31, @broker32, @broker33, @broker34, @broker35,
                            @broker36, @broker37, @broker38, @broker39, @broker40) AND
            B_ID        = TR_B_ID   AND
            TR_S_SYMB   = S_SYMB    AND
            S_CO_ID     = CO_ID     AND
            CO_IN_ID    = IN_ID     AND
            IN_SC_ID    = SC_ID     AND
            SC_NAME     = @sector_name
    GROUP   BY B_NAME
    ORDER   BY 2 DESC
    OPTION  (FORCE ORDER, LOOP JOIN)

	
    COMMIT TRANSACTION	
END


