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

-- MarketWatchFrame1 is responsible for retrieving 
-- the current price of each symbol on the list 
-- and computing the average percentage of change 
-- in price over the list. 
CREATE PROCEDURE    MarketWatchFrame1
                        @acct_id        bigint,     /* IN customer account ID */ 
                        @industry_name  char(50),   /* IN industry name */ 
                        @starting_co_id bigint,     /* IN starting company ID */
                        @ending_co_id   bigint,     /* IN ending company ID */
                        @cust_id        bigint,     /* IN customer ID */
                        @start_date     date        /* IN start date */
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRANSACTION

    DECLARE -- local variables
            @old_mkt_cap    numeric(17,2),
            @new_mkt_cap    numeric(17,2)

	SELECT @old_mkt_cap = 0.0
	SELECT @new_mkt_cap = 0.0

    IF (@cust_id <> 0)  
	BEGIN         
        SELECT  @old_mkt_cap    = SUM (S_NUM_OUT * DM_CLOSE)
        FROM    DAILY_MARKET,
                SECURITY,
                WATCH_ITEM,
                WATCH_LIST
        WHERE   WI_S_SYMB       = S_SYMB AND
                WI_S_SYMB       = DM_S_SYMB AND
                DM_DATE         = @start_date AND
                WI_WL_ID        = WL_ID AND
                WL_C_ID         = @cust_id

        SELECT  @new_mkt_cap    = SUM(S_NUM_OUT * LT_PRICE)
        FROM    LAST_TRADE,
                SECURITY,
                WATCH_ITEM,
                WATCH_LIST
        WHERE   WI_S_SYMB       = S_SYMB AND
                WI_S_SYMB       = LT_S_SYMB AND						 
                WI_WL_ID        = WL_ID AND
                WL_C_ID         = @cust_id
                   
	END
    ELSE
        IF (@acct_id <> 0)
		BEGIN
			SELECT  @old_mkt_cap    = SUM (S_NUM_OUT * DM_CLOSE)
			FROM    DAILY_MARKET,
			        SECURITY,
			        HOLDING_SUMMARY
            WHERE   HS_S_SYMB       = S_SYMB AND
					HS_S_SYMB       = DM_S_SYMB AND
					DM_DATE         = @start_date AND
                    HS_CA_ID        = @acct_id

			SELECT  @new_mkt_cap    = SUM(S_NUM_OUT * LT_PRICE)
			FROM    LAST_TRADE,
			        SECURITY,
			        HOLDING_SUMMARY
			WHERE   HS_S_SYMB       = S_SYMB AND
					HS_S_SYMB       = LT_S_SYMB AND						 
					HS_CA_ID        = @acct_id
		END
        ELSE
            IF (@industry_name <> '')
			BEGIN
				SELECT  @old_mkt_cap    = SUM (S_NUM_OUT * DM_CLOSE)
				FROM    INDUSTRY,
				        COMPANY,
				        SECURITY,
				        DAILY_MARKET
				WHERE   S_SYMB          = DM_S_SYMB AND
						DM_DATE         = @start_date AND
						CO_IN_ID        = IN_ID AND
						S_CO_ID         = CO_ID AND
						IN_NAME         = @industry_name AND
                        CO_ID           BETWEEN @starting_co_id AND @ending_co_id				
                OPTION  (LOOP JOIN, FORCE ORDER)

				SELECT  @new_mkt_cap    = SUM(S_NUM_OUT * LT_PRICE)
				FROM    INDUSTRY,
				        COMPANY,
				        SECURITY,
				        LAST_TRADE 
				WHERE   CO_IN_ID        = IN_ID AND
						S_CO_ID         = CO_ID AND
						S_SYMB          = LT_S_SYMB AND
						IN_NAME         = @industry_name AND
                        CO_ID           BETWEEN @starting_co_id AND @ending_co_id				
                OPTION  (LOOP JOIN, FORCE ORDER)
			END
            ELSE
                RETURN (5)	-- return bad input data code

    IF (@old_mkt_cap <> 0)
        SELECT  100 * ((@new_mkt_cap / @old_mkt_cap) - 1)
    ELSE
        SELECT	0

	COMMIT TRANSACTION	
END
