Winning/Losing Streaks

[splint1906]

I have a table with teamID, SchdID, Win, Loss, Tie as its fields. I'm trying to write a query that can give me each team's current winning or losing streak.

Eaxmple
TeamID SchID Win Loss Tie
1 20 1 0 0
1 30 1 0 0
1 40 1 0 0
2 21 1 0 0
2 31 0 1 0
2 41 0 1 0

I'd like for the query results to show TeamID 1 has a 3 win streak of 3 and TeamID 2 has a losing streak of 2.

Any suggestions as to how I can get this to work?

 

[PHV]

Something like this ?
SELECT TeamID, Sum(Win) As [Win#], Sum(Loss) As [Loss#], Sum(Tie) As [Tie#]
From [name of table]
GROUP BY TeamID
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[splint1906]

Not exactly. Your solution would give me each team's overall record. I already have something that does that.

What I need is something that will sum wins, losses or ties only if they are consecutive. Like, if Team 2 has played 5 games but lost its last two, the streak would be 2 losses.

 

[citychap26]

Hi there

I'm not sure if using a SQL query is the best method of doing this as you are looking for winning or losing streaks. I think you might need to use a recordset as you need to compare results for each team.

so possible psedo code:
'/ get value into a var
var1 = rst.field(0)
'/ 2nd recordset looking at next value
'/ could use clone meathod and movenext
var2 = rst2.field(0)
if var1 = var2 then
streak = streak + 1
end if
now set var1= var2 and compare the next one...

Or something along these lines...

Cheers
SK

 

[DanJR]

If you have MS Access 2000 +, then you can do this in one (messy) query, or you can do this over several queries in MS Access 97.

I came up with one approach (I did this just for a win (or not)):
- find schID (A) and Win result for last schedule
- find max schID (B) where win doesn't equal last result
- count records where schid > B and <= A

But, this approach is a little too messy to be that helpful. E.g., if your table is called TEAM then:

SELECT A.TeamID, A.LastWin, iif(A.CountSeries=0,A.CountAll,A.CountSeries) as Series
FROM
(SELECT E.TeamID, E.LastWin, 
                    (SELECT Count(*) 
                            FROM TEAM C 
                            WHERE C.TeamID = E.TeamID AND C.SchID > S.SchIDDifferentResult 
                            AND C.SchID <= E.SchID) AS CountSeries,
                   (SELECT Count(*) 
                           FROM TEAM D
                          WHERE D.TeamID = E.TeamID) as CountAll
FROM (SELECT L.TeamID, L.SchID, L.Win AS LastWin
  FROM TEAM AS L
  WHERE L.SchID = (SELECT max(M.SchID) 
                                                   FROM TEAM M
                                                   WHERE M.TeamID = L.TeamID)) AS E

LEFT  JOIN 
 (SELECT L.TeamID, L.SchID as SchIDDifferentResult
  FROM TEAM AS L
  WHERE L.SchID = (SELECT max(M.SchID) 
                                       FROM TEAM M
                                       WHERE M.TeamID = L.TeamID
                                         AND M.Win <> (SELECT L2.Win
                                                                      FROM TEAM L2
                                                                      WHERE L2.SchID = (SELECT max(M2.SchID) 
                                                                                                           FROM TEAM M2
                                                                                                           WHERE M2.TeamID = L.TeamID ) ))) AS S

ON S.TeamID = E.TeamID) A;

However, I should have a combined the Win/Loss/Tie fields into one field then base the above query on it.

cheers,
dan

 

[splint1906]

Thanks Dan JR. I used your explanation above to craft a couple of queries that got me what I wanted. I first got the last result by team. Then I got the StatID of the last record that didn't match the last result. Then I counted the number of StatIDs between those two records.

Sorry for the late reply.