select distinct aafp.date_coverage_value as event_key, max(cast(aafp.sequence_number as int)) as last_play_sequence, isnull(scores.score_type, 'none') as score_type from action_american_football_play aafp left outer join (select date_coverage_value, cast(sequence_number as int) as sequence_number, score_type from action_american_football_score where isnumeric(sequence_number) = 1) scores on scores.date_coverage_value = aafp.date_coverage_value and scores.sequence_number = cast(aafp.sequence_number as int) where isnumeric(aafp.sequence_number) = 1 group by aafp.date_coverage_value, scores.score_type