BOXSCORE_QUERIES.txt

SET NOCOUNT ON 
            SET XACT_ABORT ON  

            DECLARE @GameID INTEGER
            SET @GameID = '2291'
            DECLARE @ScoreAt TABLE (PlayID int PRIMARY KEY, HomeScore int, VisitorScore int)

            INSERT INTO @ScoreAt (PlayID, HomeScore, VisitorScore)
            SELECT p.PlayID, ISNULL(MAX(sp.HomeScore), 0) AS HomeScore, ISNULL(MAX(VisitorScore), 0) AS VisitorScore
            FROM StatPlay p
            LEFT JOIN (
                SELECT ps.PlayID, ps.PlaySequence, ss.HomeScore, ss.VisitorScore 
                FROM StatScore ss 
                INNER JOIN StatPlay ps ON ss.PlayID = ps.PlayID
                WHERE ps.GameID = @GameID
                ) sp ON p.PlaySequence >= sp.PlaySequence
            WHERE p.GameID = @GameID
            GROUP BY p.PlayID 

            DECLARE @PlaySuccess TABLE (PlayID INT, StatTypeID INT, StatTypeDescription NVARCHAR(50)) 
            INSERT INTO @PlaySuccess (PlayID, StatTypeID, StatTypeDescription)
            SELECT DISTINCT spd.PlayID, 
                spd.StatTypeID, 
                CASE 
                    WHEN st.StatTypeDescription = 'Yards (Pass)' THEN 'Success (Pass)' 
                    WHEN st.StatTypeDescription = 'Yards (Rush)' THEN 'Success (Rush)' 
                    WHEN st.StatTypeDescription = 'Yards (Punt)' THEN 'Success (Punt)' 
                    WHEN st.StatTypeDescription = 'Yards (Kick)' THEN 'Success (Kick)' 
                    WHEN st.StatTypeDescription = 'Field Goal Made' THEN 'Success (Field Goal)' 
                    WHEN st.StatTypeDescription = 'Convert (1 point)' THEN 'Success (1 Point Convert)' 
                    WHEN st.StatTypeDescription = 'Convert (2 points)' THEN 'Success (2 Point Convert)' 
                    WHEN st.StatTypeDescription = 'Recovered Convert Score (2 points)' THEN 'Success (2 Point Convert); Recovered' 
                    WHEN st.StatTypeDescription = 'Pass Targeted To/Not Caught' THEN 'Failed (Pass)' 
                    WHEN st.StatTypeDescription = 'Field Goal Blocked' THEN 'Failed (Field Goal)' 
                    WHEN st.StatTypeDescription = 'Single (Missed Field Goal)' THEN 'Failed (Field Goal); Single' 
                    -- WHEN st.StatTypeDescription = 'Touchdown (Missed Field Goal Return)' THEN 'Failed (Field Goal); TD on Missed FG' 
                    -- WHEN st.StatTypeDescription = 'Touchdown (Fumble Return)' THEN 'Failed (Punt); TD on Fumble Return' 
                    WHEN st.StatTypeDescription = 'Missed Convert Kick Return' THEN 'Failed (1 Point Convert)' 
                    WHEN st.StatTypeDescription = 'Convert Kick Blocked' THEN 'Failed (1 Point Convert)' 
                    -- WHEN st.StatTypeDescription = 'Touchdown (Missed FG Recovery - Own Team)' THEN 'Failed (Field Goal); TD on Missed FG' 
                    ELSE st.StatTypeDescription 
                END AS StatTypeDescription
            FROM StatPlay sp 
            LEFT JOIN StatPlayDetail spd ON spd.PlayID = sp.PlayID 
            LEFT JOIN StatType st ON st.StatTypeID = spd.StatTypeID 
            WHERE sp.GameID = @GameID 
            AND 
                ( 
                    ( sp.PlayType = '1' AND spd.StatTypeID = '74' ) OR -- 1 = Rush
                    ( sp.PlayType = '2' AND ( spd.StatTypeID = '47' OR spd.StatTypeID = '98' ) ) OR -- 2 = Pass
                    ( sp.PlayType = '3' AND ( spd.StatTypeID = '3' OR spd.StatTypeID = '6' OR spd.StatTypeID = '111' OR spd.StatTypeID = '116' ) ) OR -- 3 = Convert
                    ( sp.PlayType = '4' AND spd.StatTypeID = '29' ) OR -- 4 = Kickoff
                    ( sp.PlayType = '5' AND ( spd.StatTypeID = '10' OR spd.StatTypeID = '11' OR spd.StatTypeID = '13' OR spd.StatTypeID = '16' OR spd.StatTypeID = '119' ) ) OR -- 5 = Field Goal
                    ( sp.PlayType = '6' AND spd.StatTypeID = '58' ) OR -- 6 = Punt 
                    ( sp.PlayType = '7' AND ( spd.StatTypeID = '110' OR spd.StatTypeID = '24' OR spd.StatTypeID = '78' ) ) OR -- 7 = Team Loss
                    ( sp.PlayType = '8' AND ( spd.StatTypeID = '3' OR spd.StatTypeID = '6' OR spd.StatTypeID = '122' ) ) OR -- 8 = Convert Rush
                    ( sp.PlayType = '9' AND ( spd.StatTypeID = '3' OR spd.StatTypeID = '6' OR spd.StatTypeID = '122' ) ) -- 9 = Convert Pass
                )

            SELECT sp.PlayID AS play_id, 
            sp.PlaySequence AS play_sequence, 
            sp.Quarter AS quarter, 
            sp.GameClock AS play_clock_start, 
            DATEDIFF(MINUTE, '00:00', TRY_PARSE(sp.GameClock AS time USING 'en-US')) AS play_clock_start_in_secs, 
            sp.FieldPosition AS field_position_start, 
            sp.EndFieldPosition AS field_position_end, 
            ISNULL(c.Code, '') AS team_abbreviation, 
            ISNULL(c.FranchiseID, 0) AS team_id, 
            sp.Down AS down, 
            sp.YardsToGo AS yards_to_go, 
            sp.PlayType AS play_type_id, 
            sp.PlayTypeDescr AS play_type_description, 
            sp.PlayResultYards AS play_result_yards, 
            ISNULL(ss.PointsScored, 0) AS play_result_points, 
            ISNULL(sp.ResultType, 0) AS play_result_type_id, 
            ISNULL(sp.ResultTypeDescr, '') AS play_result_type_description, 
            ISNULL(ps.StatTypeID, 0) AS play_success_id, 
            ISNULL(ps.StatTypeDescription, '') AS play_success_description, 
            ISNULL(sp.ChangeOfPossession, '0') AS play_cop_occurred, 
            ISNULL(pos.RedZone, '0') AS is_in_red_zone, 
            player_qb.CFLCentralID AS player_qb_cfl_central_id, 
            player_qb.FirstName AS player_qb_first_name, 
            player_qb.MiddleName AS player_qb_middle_name, 
            player_qb.LastName AS player_qb_last_name, 
            player_qb.BirthDate AS player_qb_birth_date, 
            player_bc.CFLCentralID  AS player_bc_cfl_central_id, 
            player_bc.FirstName AS player_bc_first_name, 
            player_bc.MiddleName AS player_bc_middle_name, 
            player_bc.LastName AS player_bc_last_name,  
            player_bc.BirthDate AS player_bc_birth_date, 
            player_pd.CFLCentralID  AS player_pd_cfl_central_id, 
            player_pd.FirstName AS player_pd_first_name, 
            player_pd.MiddleName AS player_pd_middle_name, 
            player_pd.LastName AS player_pd_last_name,  
            player_pd.BirthDate AS player_pd_birth_date, 
            sc.HomeScore AS team_home_score, 
            sc.VisitorScore AS team_visitor_score,
            sp.Description AS play_summary 
            FROM StatPlay sp
            LEFT OUTER JOIN StatScore ss ON ss.PlayID = sp.PlayID 
            LEFT JOIN @ScoreAt sc ON sp.PlayID = sc.PlayID 
            LEFT OUTER JOIN @PlaySuccess ps ON sp.PlayID = ps.PlayID 
            INNER JOIN StatPossession pos ON sp.StatPossessionID = pos.StatPossessionID 
            LEFT JOIN Club c ON pos.ClubID = c.ClubID 
            LEFT OUTER JOIN GameRoster gr_player_qb ON gr_player_qb.GameRosterID = sp.QBGameRosterID 
            LEFT OUTER JOIN Player player_qb ON player_qb.PlayerID = gr_player_qb.PlayerID 
            LEFT OUTER JOIN GameRoster gr_player_bc ON gr_player_bc.GameRosterID = sp.BallCarrier 
            LEFT OUTER JOIN Player player_bc ON player_bc.PlayerID = gr_player_bc.PlayerID 
            LEFT OUTER JOIN GameRoster gr_player_pd ON gr_player_pd.GameRosterID = sp.PrimaryDefender 
            LEFT OUTER JOIN Player player_pd ON player_pd.PlayerID = gr_player_pd.PlayerID             
            WHERE sp.GameID = @GameID 
            AND sp.PlayType <> 99 
             
            ORDER BY sp.PlaySequence
        ;

/*
Missing Index Details from SQLQuery3.sql - cfl-connect-ro1.c0kml4nyh9a5.us-west-2.rds.amazonaws.com.CFLConnectDataMart (cfldb (79))
The Query Processor estimates that implementing the following index could improve the query cost by 51.2498%.
*/

/*
USE [CFLConnectDataMart]
GO
CREATE NONCLUSTERED INDEX IX_StatGameTackle_TotalTacklesDefensiveSpecialTeams
ON [dbo].[StatGameTackle] ([GameRosterID])
INCLUDE ([TotalTackles],[Defensive],[SpecialTeams])
GO

USE [CFLConnectDataMart]
GO
CREATE NONCLUSTERED INDEX IX_StatPlay_DownPlayResultYardsOffence
ON [dbo].[StatPlay] ([StatPossessionID])
INCLUDE ([Down],[PlayResultYardsOffence])
GO
*/