Calculate Win/Lose Streak

[sheide9]

How would I go about calculating a win/lose streak?

This is the table I have. Home and Visitors.
So Team #17 should be on a 2 game win streak (last 2
games).

id date HID Hscore VID VScore
1 1-Jul 17 3 1 1
2 2-Jul 2 2 17 0
3 3-Jul 3 1 17 0
4 4-Jul 17 3 4 1
5 5-Jul 5 1 17 2

Thanks!

 

[John Verhagen]

I needed 6 queries to get this done, assuming your table was called
tblScores:
Call this query qScores:
Select ID, HID as Team, Hscore - VScore as Win from tblScores
UNION Select ID, VID as Team, Vscore - HScore as Win from tblScores
ORDER BY Team, ID;

Call this query qScoresLose:
SELECT DISTINCT 0 AS xID, qScores.Team, -1 AS xWin
FROM qScores;

Call this query qScoresAll
select * from qScores
UNION SELECT * FROM qScoresLose

Call this query qScoresWinStreak:
SELECT qScoresAll.Team, Count(qScoresAll.ID) AS CountOfID, "Win" AS Streak
FROM qScoresAll
WHERE (((qScoresAll.ID)>(select max(id) from qScoresAll S where
S.Team=qScoresAll.Team and win<=0)))
GROUP BY qScoresAll.Team, "Win";

Call this query qScoresLoseStreak:
SELECT qScoresAll.Team, Count(qScoresAll.ID) AS CountOfID, "Lose" AS Streak
FROM qScoresAll
WHERE (((qScoresAll.ID)>(select max(id) from qScoresAll S where
S.Team=qScoresAll.Team and win>=0)))
GROUP BY qScoresAll.Team, "Lose";

Then the final Query would be:
SELECT * from qScoresWinStreak
UNION Select * from qScoresLoseStreak;

With result:
Team CountOfID Streak
1 1 Lose
2 1 Win
3 1 Win
4 1 Lose
5 1 Lose
17 2 Win

 

[John Verhagen]

Actually, qScoresLose should have been:
SELECT DISTINCT 0 AS xID, qScores.Team, 0 AS xWin
FROM qScores;

 

[sheide9]

Thanks a lot! I tried it and it works!

-----Original Message-----
Actually, qScoresLose should have been:
SELECT DISTINCT 0 AS xID, qScores.Team, 0 AS xWin
FROM qScores;

CountOfID, "Lose" AS
Streak


.