Using a query and joining tables to create a streak count

[msaz87]

Hey all,

 

I was hoping to see if anyone had an idea on how to go about creating a query that would join two tables and develop a "streak" of the same occurrence. The best example for what I mean is basically in any sporting event, if a team wins four games in a row, it would be a streak of four, lost two in a row, streak of two losses, etc.

 

The two tables are as such:

 

The below defines the game, what week it is and the two teams (team_1 and team_2)

 

The second table defines the score and who won or lost (loose is lost, original programmer spoke poor English)

 

This task would require the two tables joined and then somehow counting from the highest (most recent) week backwards, taking the most recent result (1 = win, 0 = loss) and then counting the amount of times it's the same until it switches... so in the images above, using team_id 15 as an example, starting at week 6, the streak is "Won 1". If you started with week 4, it would be "Won 2" and so on...

 

Any help or guidance for how this could be accomplished (if it can be with the current table structure) would be greatly appreciated...

 

Thanks in advance!

[fenway]

This is basically a variant on counting rows based a given column value -- you'll have to use user variables.