winning streak query

[pat67]

If a player plays twice the same day, the [date] field must have a TIME
value (other than 0), and a DIFFERENT one for each 'game' :
Player      date                isWin
john        2010.1.1       -1
tom         2010.1.1        0
john        2010.1.1        -1
mary        2010.1.1       0
is wrong for John, since (Player, date)  is duplicated (first and third
record).  The following would be nice, though:
Player      date                          isWin
john        2010.1.1 10:00:00       -1
tom         2010.1.1  10:00:00        0
john        2010.1.1   11:00:00     -1
mary        2010.1.1   11:00:00     0
since then, (Player, date)  has no dup anymore: John played at 10AMand at
11AM, that makes 2 different games. Without the time part, the presented
algorithm will see only one game for John, for the first of January. So the
importance to have a time part.
And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK
operator.
Vanderghast, Access MVP
Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is
anything thing not null neither 0, but the result of a comparison
is -1:
? int(3=3)
-1
Vanderghast, Access MVP
Wow. let me clarify more if I can. I just used 3 as an example. If
a
players longest winning streak is 1 or 100, that's what I want to
show.
Then you use the unmodified query (ie, without any HAVING clause;
the use
of
HAVING was only a remark from Clifford)
My data table has these fields:
GameID Date Winner Loser
I then have a union query with these fields:
Date GameID Player Opponent Result
result being Won or Lost
So that means that there are 2 results for each game id.
does that make it easier?
What you need is (at least)
DateOfTheGame, PlayerID, IsPlayerWinOrLost
which probably could be obtained from a query like:
SELECT [date] as gameDateTimeStamp, Winner As PlayerID, true AS
isWin
FROM dataTable
UNION ALL
SELECT [date], Loser, false
FROM dataTable
assuming that, from your original data table, Winner and Loser
return a
PlayerID (who is the winner and who is the loser). Saving that query
as
myData and using the proposed query should return what you want (the
highest
winning-streak for each player).
Vanderghast, Access MVP
Ok. that gives a result of -1 for the winners and 0 for the losers. Is
that correct? Then i use your queries you stated originally?- Hide
quoted text -
- Show quoted text -
That looks like it worked. It takes a while to run, but it looks right-
Hide quoted text -
- Show quoted text -
Ok there is a problem. The queries work however i see at least one
player not right. The first night of the year he lost then won 2 ina
row and lost again so his win streak should at least be 2. it is
showing as 1. Is that because the first query returned a -1?- Hide quoted
text -
- Show quoted text -
I checked the top rated guy who shows 12, which he is currently on. In
reality it should be 15? what could be the problem?- Hide quoted text-
- Show quoted text -

can I use game id as opposed to time?- Hide quoted text -

- Show quoted text -

I mean can i concatenate the date and id to look like this 9/15/2009-1?- Hide quoted text -

- Show quoted text -

ok i concatenated the date and game id. that made some changes but
still not correct. sql now like this

myData

SELECT [Date] & '-' & ID as GameDateTimeStamp, Winner As PlayerID,
true AS isWin
FROM tblResults
UNION ALL SELECT [Date] & '-' & ID, Loser, false
FROM tblResults;

problem is now top player says 13. but should be 15. I think i need to
change the date format to be 09/15/2009. with just 9/15/2009, any 12
or 11 or 10 is sorted before.

 

[vanderghast]

Note that if GameID is already unique, you can use it instead of [Date].
Indeed, if GameID is unique, then (PlayerID, GameID) will also be unique
(unless a player plays against himself!). You will have to change the SQL
statements to replace [Date] by GameID.

Vanderghast, Access MVP

 

[vanderghast]

It is preferable to have date as date_time datatype rather than string. If
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.

You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.


Vanderghast, Access MVP

 

[pat67]

It is preferable to have date as date_time datatype rather than string. If
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.

You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.

Vanderghast, Access MVP

game id is autonumbered so every time i enter a result, a new game id
is created. I changed the query to use game id and it really looks
like it works now. the top guy is showing 15. the problem was the
anything in January was showing up first because of the 1. I was able
to change the original table to show 09 for September, but when i ran
the myData query, it didn't pick it up that way. Do you know why?

 

[pat67]

game id is autonumbered so every time i enter a result, a new game id
is created. I changed the query to use game id and it really looks
like it works now. the top guy is showing 15. the problem was the
anything in January was showing up first because of the 1. I was able
to change the original table to show 09 for September, but when i ran
the myData query, it didn't pick it up that way. Do you know why?

The next question would be to get from the same tblResults, a player's
current streak, whether it's wins or losses.

i.e.
Player Streak
Bob Won 6
Jim Lost 3
Frank Won 2

I am a pain I know.

 

[vanderghast]

You can rebuild a query finding the maximum date stamp (gameID, here) with a
lost,

----------------------------------------
SELECT playerID, MAX(gameID) AS mgame
FROM data
WHERE NOT isWin
GROUP BY playerID
----------------------------------------

saved as qlatestLost

and counting the number of records coming after that date stamp, for that
player,


-------------------------------
SELECT playerID,
COUNT(qlatestLost.PlayerID) AS actualWinStreak
FROM data LEFT JOIN qlatestLost
ON data.playerID = qlatestLost.playerID
AND data.gameID > qlatestLost.mgame
-------------------------------


Sure, if a player has lost his last game, the actualWinStreak is 0, thanks
to the outer join and the COUNT(field) behavior,


but


there is a problem: if a player has not lost a single game, qlatestLoast
will return nothing too and so, the final query will also return 0 for this
player. To correct that problem, we can modify the last query to (untested)
:

---------------------------
SELECT playerID,

iif(
playerID IN(SELECT playerID FROM qlatestLost),
COUNT(qlatestLost.PlayerID),
(SELECT COUNT(*)
FROM data as b
WHERE b.playerID = a.playerID)
) AS actualWinStreak

FROM data AS a LEFT JOIN qlatestLost
ON data.playerID = qlatestLost.playerID
AND data.gameID > qlatestLost.mgame
------------------------------

or, much much less verbose, only modify the first query into:

-----------------------------
SELECT playerID, MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM data
GROUP BY playerID
----------------------------

and keep the second query unchanged. This modification simply mark the
winning games as negative (for the purpose of that query) so that if a
player never lost a game, the returned MAX will be negative and all the
games, for that player, will be counted by the final query, as we want. If
the player lost a game, the queries behave as before. So, less
modifications, but less "self documented" , enven if the first solution
(modifying the second query) is hardly what I call 'self documented' either.



Vanderghast, Access MVP

 

[vanderghast]

You were probably using string rather than date_time as DATA TYPE for that
field (check the table design).

Vanderghast, Access MVP

It is preferable to have date as date_time datatype rather than string. If
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.

You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.

Vanderghast, Access MVP

game id is autonumbered so every time i enter a result, a new game id
is created. I changed the query to use game id and it really looks
like it works now. the top guy is showing 15. the problem was the
anything in January was showing up first because of the 1. I was able
to change the original table to show 09 for September, but when i ran
the myData query, it didn't pick it up that way. Do you know why?

 

[pat67]

It is preferable to have date as date_time datatype rather than string. If
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.

You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.

Vanderghast, Access MVP

Just so you know, I have a union query from my original table showing
gameID, date, player, opponent, and result. either won or lost. I need
to rank the results by player and gameID to get what is the current
streak. I am unsure how to do that. Can you help? Thanks

 

[vanderghast]

You just need the records with GameID ( positive values, increasing as time
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.

SELECT PlayerID,
MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM data
GROUP BY PlayerID


saved as q1. I assume your original data is in table (or query) called
data. Then a second query:

SELECT a.PlayerID,
COUNT(c.gameID) AS actualWinStreak

FROM (ListOfPlayers AS a
LEFT JOIN data AS b
ON a.playerID = b.playerID)
LEFT JOIN q1 AS c
ON b.playerID = c.playerID
AND b.mgame < c.gameID

GROUP BY a.PlayerID

should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".



Vanderghast, Access MVP

 

[pat67]

You just need the records with  GameID ( positive values, increasing astime
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.

SELECT PlayerID,
    MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM  data
GROUP BY PlayerID

saved as q1.  I assume your original data is in table (or query) called
data. Then a second query:

SELECT a.PlayerID,
    COUNT(c.gameID)  AS actualWinStreak

FROM (ListOfPlayers AS a
    LEFT JOIN data AS b
        ON a.playerID = b.playerID)
    LEFT JOIN q1 AS c
        ON b.playerID = c.playerID
            AND b.mgame < c.gameID

GROUP BY a.PlayerID

should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".

Vanderghast, Access MVP

my data is in a union query qryUnion with the field Player and Result.
i have a tblRosters with a Player Name field. Can you substitue those
into your query so I don't screw it up?

 

[pat67]

You just need the records with  GameID ( positive values, increasing astime
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.

SELECT PlayerID,
    MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM  data
GROUP BY PlayerID

saved as q1.  I assume your original data is in table (or query) called
data. Then a second query:

SELECT a.PlayerID,
    COUNT(c.gameID)  AS actualWinStreak

FROM (ListOfPlayers AS a
    LEFT JOIN data AS b
        ON a.playerID = b.playerID)
    LEFT JOIN q1 AS c
        ON b.playerID = c.playerID
            AND b.mgame < c.gameID

GROUP BY a.PlayerID

should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".

Vanderghast, Access MVP

Let me show you my qryUnion example

GameID Player Oponnent Result
1 Bob Steve Won
2 Joe Frank Won
3 Jim Al Won
1 Steve Bob Lost
2 Frank Joe Lost
3 Al Jim Lost


Obviously it is much larger but you get the gist.What I am looking for
is this

Player W L Current Streak
Bob 1 0 Won 1
Joe 1 0 Won 1
Jim 1 0 Won 1
Steve 0 1 Lost 1
Frank 0 1 Lost 1
Al 0 1 Lost 1

Or something similar

 

[pat67]

You can rebuild a query finding the maximum date stamp (gameID, here) with a
lost,

----------------------------------------
SELECT playerID, MAX(gameID) AS mgame
FROM  data
WHERE NOT isWin
GROUP BY playerID
----------------------------------------

saved as qlatestLost

and counting the number of records coming after that date stamp, for that
player,

-------------------------------
SELECT playerID,
    COUNT(qlatestLost.PlayerID)  AS actualWinStreak
FROM data LEFT JOIN qlatestLost
    ON data.playerID = qlatestLost.playerID
    AND data.gameID > qlatestLost.mgame
-------------------------------

Sure, if a player has lost his last game, the actualWinStreak is 0, thanks
to the outer join and the COUNT(field) behavior,

but

there is a problem:  if a player has not lost a single game, qlatestLoast
will return nothing too and so, the final query will also return 0 for this
player. To correct that problem, we can modify the last query to  (untested)
:

---------------------------
SELECT playerID,

    iif(
         playerID IN(SELECT playerID FROM qlatestLost),
         COUNT(qlatestLost.PlayerID),
        (SELECT COUNT(*)
                FROM data as b
                WHERE b.playerID = a.playerID)
         )         AS actualWinStreak

FROM data AS a LEFT JOIN qlatestLost
    ON data.playerID = qlatestLost.playerID
    AND data.gameID > qlatestLost.mgame
------------------------------

or, much much less verbose, only modify the first query into:

-----------------------------
SELECT playerID, MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM  data
GROUP BY playerID
----------------------------

and keep the second query unchanged. This modification simply mark the
winning games as negative (for the purpose of that query) so that if a
player never lost a game, the returned MAX will be negative and all the
games, for that player, will be counted by the final query, as we want. If
the player lost a game, the queries behave as before. So, less
modifications, but less  "self documented" , enven if the first solution
(modifying the second query) is hardly what I call 'self documented' either.

Vanderghast, Access MVP

the top query tells me the specified field playerID could refer to
more than one table in the from clause

 

[vanderghast]

SELECT PlayerID,
MAX( iif(Result="win", -1, 1) * gameID ) AS mgame
FROM data
GROUP BY PlayerID

saved as q1, then


SELECT a.PlayerID,
COUNT(c.gameID) AS actualWinStreak

FROM (ListOfPlayers AS a
LEFT JOIN data AS b
ON a.playerID = b.playerID)
LEFT JOIN q1 AS c
ON b.playerID = c.playerID
AND b.mgame < c.gameID

GROUP BY a.PlayerID



where ListOfPlayers is a query returning all players, once.



Vanderghast, Access MVP

 

[Clifford Bass via AccessMonster.com]

Hi,

So here is another way to try:

qryGame Results

SELECT GameID, [Date] AS GameDate, Winner AS Player, True AS IsWinner
FROM tblResults
UNION ALL SELECT GameID, [Date], Loser, False
FROM tblResults;


qryLongest Streaks-Part A

SELECT A.GameDate, A.Player, A.GameID AS StartGameID, B.GameID AS EndGameID,
A.IsWinner, (select count(*) from [qryGame Results] as C where C.GameDate = A.
GameDate and C.Player = A.Player and C.GameID between A.GameID and B.GameID)
AS WinLoseCount
FROM [qryGame Results] AS A INNER JOIN [qryGame Results] AS B ON (A.IsWinner
= B.IsWinner) AND (A.Player = B.Player) AND (A.GameDate = B.GameDate)
WHERE (((B.GameID)>=[A].[GameID]) AND ((Not Exists (select * from [qryGame
Results] as D where D.GameDate = A.GameDate and D.Player = A.Player and D.
GameID between A.GameID and B.GameID and D.IsWinner <> A.IsWinner))=True))
ORDER BY A.GameDate, A.Player, A.GameID, B.GameID;


qryLongest Streaks-Part B

SELECT E.GameDate, E.Player, Max(E.WinLoseCount) AS LongestStreakLength
FROM [qryLongest Streaks-Part A] AS E
GROUP BY E.GameDate, E.Player;

qryLongest Streaks-Part C

SELECT F.GameDate, F.Player, G.LongestStreakLength AS StreakLength, F.
StartGameID, F.EndGameID, IIf([IsWinner],"Winning","Losing") AS StreakType
FROM [qryLongest Streaks-Part A] AS F INNER JOIN [qryLongest Streaks-Part B]
AS G ON (F.GameDate = G.GameDate) AND (F.Player = G.Player) AND (F.
WinLoseCount = G.LongestStreakLength)
ORDER BY F.GameDate, F.Player, F.StartGameID;

This will report the length of the longest streak for each person on a
particular day. It will display the start and end GameIDs of the streak and
whether or not the streak was a winning or losing streak. If the person had
several streaks of that longest streak length, all of those streaks will show.


Clifford Bass