Work out the latest sequence of results
I have a query as below
Which simply works out a result for each team and puts the results in date order.
I want to count the last result from each team, and if the result before that was the same result then add one to the count and so on, unless the sequence is broken then exit the count.
so if a team drew-drew their last 2 games but drew-lost their 3rd previous they return a result of draw-draw 2. all
Im trying to figure out how to do this, but the only way I can figure is using a cursor for each team and each result, which doesnt seem like it will be efficient?
Code:
SELECT * FROM(
Select ht.[Name]
,CASE WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN 'DRAW - DRAW'
WHEN HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN 'LOSE - DRAW'
WHEN HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN 'WIN - DRAW'
WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN 'DRAW - LOSE'
WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN 'DRAW - WIN'
WHEN HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN 'WIN - WIN'
WHEN HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN 'WIN - LOSE'
WHEN HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN 'LOSE - WIN'
WHEN HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN 'LOSE - LOSE'
END AS Result
,Date
,'H' as 'HomeAway'
from matches
INNER JOIN Teams t on Away_ID = t.id
INNER JOIN Teams ht on Home_ID = ht.id
--where ht.[Name] = @TeamID
UNION
Select ht.[Name]
,CASE WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN 'DRAW - DRAW'
WHEN HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN 'LOSE - DRAW'
WHEN HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals = FT_AWAY_Goals THEN 'WIN - DRAW'
WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN 'DRAW - LOSE'
WHEN HT_Home_Goals = HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN 'DRAW - WIN'
WHEN HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN 'WIN - WIN'
WHEN HT_Home_Goals < HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN 'WIN - LOSE'
WHEN HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals < FT_AWAY_Goals THEN 'LOSE - WIN'
WHEN HT_Home_Goals > HT_AWAY_Goals AND FT_Home_Goals > FT_AWAY_Goals THEN 'LOSE - LOSE'
END AS Result
,Date
,'A' as 'Home/Away'
from matches
INNER JOIN dbo.Teams ht on Away_ID = ht.id
--where ht.[Name] = @TeamID
) results
ORDER BY Date DESC
Re: Work out the latest sequence of results
I am looking to be able to produce streak results similar to below
Quote:
STREAKS Home Away Overall
Current winning streak 2 1 3
Current drawing streak - - -
Current losing streak - - -
Re: Work out the latest sequence of results
