Hockey Stats Database Total Help

[Jason Lepack]

So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number. If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP, then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack

 

[Jason Lepack]

I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.

 

[Michel Walsh]

Hi,


SELECT a.skaterID, a.GP, Nz(a.g-b.g, a.g) AS G, Nz(a.a-b.a, a.a) AS A
FROM tbl_skater_stats AS a LEFT JOIN
JOIN tbl_skater_stats AS b
ON a.skaterID = b.skaterID AND a.GP=b.gp+1


should do, wihtout having to add a dummy record, either with Jet, either
with MS SQL Server


Hoping it may help,
Vanderghast, Access MVP

I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.

So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number. If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP, then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack

 

[Jason Lepack]

Thanks so much Vanderghast!

That's great, I never really used Nz so that's good to learn!

That will really help me next season and in the playoffs but it won't
right now. The problem is that I started collecting the stats this
week, so my first entry is almost 40 games in, so the NZ would give
Sidney Crosby a one game total of 17 goals and 41 assists (yikes!)

Cheers,
Jason Lepack

Hi,


SELECT a.skaterID, a.GP, Nz(a.g-b.g, a.g) AS G, Nz(a.a-b.a, a.a) AS A
FROM tbl_skater_stats AS a LEFT JOIN
JOIN tbl_skater_stats AS b
ON a.skaterID = b.skaterID AND a.GP=b.gp+1


should do, wihtout having to add a dummy record, either with Jet, either
with MS SQL Server


Hoping it may help,
Vanderghast, Access MVP

I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.

So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number. If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP, then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack

 

[Michel Walsh]

Hi,



but Crosby *is* good!


Now that you mentioned I used Nz (was too sleepy to see it by myself),
replace it by COALESCE in MS SQL Server.

The same extraordinary score would have been obtained by adding a dummy
zero-row. There is no real solution short of having ALL the weeks in the
basic data, of calling the first entry as "previous weeks, with an s"


Hoping it may help,
Vanderghast, Access MVP

Thanks so much Vanderghast!

That's great, I never really used Nz so that's good to learn!

That will really help me next season and in the playoffs but it won't
right now. The problem is that I started collecting the stats this
week, so my first entry is almost 40 games in, so the NZ would give
Sidney Crosby a one game total of 17 goals and 41 assists (yikes!)

Cheers,
Jason Lepack

Hi,


SELECT a.skaterID, a.GP, Nz(a.g-b.g, a.g) AS G, Nz(a.a-b.a, a.a) AS A
FROM tbl_skater_stats AS a LEFT JOIN
JOIN tbl_skater_stats AS b
ON a.skaterID = b.skaterID AND a.GP=b.gp+1


should do, wihtout having to add a dummy record, either with Jet, either
with MS SQL Server


Hoping it may help,
Vanderghast, Access MVP

I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.


Jason Lepack wrote:
So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number. If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP, then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack

 

[Jason Lepack]

Thanks for the bit about COALESCE.

Here's how I handle the data.

Raw Import into import table
Massage the data
Add any players that didn't exist previously
If this is not the very first load then add a stat record that has all
stats = 0 for all new player
Add the new stat records.

The only frustrating thing is that there are players who have the same
name, first and last.

Based on the data I have, I can't figure out how to differentiate the
two players. Players can be traded, so I can't use First, Last, Team.
I noticed that they play different positions, but the positions in the
stats can change from day to day as well. (Found this out today)

Hopefully I won't be back to the drawing board because of such a simple
thing. Anyways, thanks for all your help!

Jason Lepack

Hi,



but Crosby *is* good!


Now that you mentioned I used Nz (was too sleepy to see it by myself),
replace it by COALESCE in MS SQL Server.

The same extraordinary score would have been obtained by adding a dummy
zero-row. There is no real solution short of having ALL the weeks in the
basic data, of calling the first entry as "previous weeks, with an s"


Hoping it may help,
Vanderghast, Access MVP

Thanks so much Vanderghast!

That's great, I never really used Nz so that's good to learn!

That will really help me next season and in the playoffs but it won't
right now. The problem is that I started collecting the stats this
week, so my first entry is almost 40 games in, so the NZ would give
Sidney Crosby a one game total of 17 goals and 41 assists (yikes!)

Cheers,
Jason Lepack

Hi,


SELECT a.skaterID, a.GP, Nz(a.g-b.g, a.g) AS G, Nz(a.a-b.a, a.a) AS A
FROM tbl_skater_stats AS a LEFT JOIN
JOIN tbl_skater_stats AS b
ON a.skaterID = b.skaterID AND a.GP=b.gp+1


should do, wihtout having to add a dummy record, either with Jet, either
with MS SQL Server


Hoping it may help,
Vanderghast, Access MVP


I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.


Jason Lepack wrote:
So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number. If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP, then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack

 

[Michel Walsh]

Hi,


Well, if the 'data' does not know, it surely cannot read what I have in my
mind


A possible solution could be to identify the players through their (first
name, last name, team, season). An extra table can then list those, and BY
YOUR MEANS, associate a 5-tuple to an ID of your own:


fname, lname, team, fromseason, toseason, id
joe smoe cannuk winter07, null, joeSmoe01
....
joe smoe habs winter07 spring07 joeSmoe02
....
joe smoe maple spring07 null joeSmoe02
....



indicates that joeSmoe01 is a different player than joeSmoe02, and who is
who is identifiable by looking at the team and the season (here I use name,
only for illustration, but definitively, dates are preferable to "season
names" )



and now, your data 'knows' as much as you do... well, as for
players/teams/seasons is concerned




Hoping it may help,
Vanderghast, Access MVP

Thanks for the bit about COALESCE.

Here's how I handle the data.

Raw Import into import table
Massage the data
Add any players that didn't exist previously
If this is not the very first load then add a stat record that has all
stats = 0 for all new player
Add the new stat records.

The only frustrating thing is that there are players who have the same
name, first and last.

Based on the data I have, I can't figure out how to differentiate the
two players. Players can be traded, so I can't use First, Last, Team.
I noticed that they play different positions, but the positions in the
stats can change from day to day as well. (Found this out today)

Hopefully I won't be back to the drawing board because of such a simple
thing. Anyways, thanks for all your help!

Jason Lepack

Hi,



but Crosby *is* good!


Now that you mentioned I used Nz (was too sleepy to see it by myself),
replace it by COALESCE in MS SQL Server.

The same extraordinary score would have been obtained by adding a dummy
zero-row. There is no real solution short of having ALL the weeks in the
basic data, of calling the first entry as "previous weeks, with an s"


Hoping it may help,
Vanderghast, Access MVP

Thanks so much Vanderghast!

That's great, I never really used Nz so that's good to learn!

That will really help me next season and in the playoffs but it won't
right now. The problem is that I started collecting the stats this
week, so my first entry is almost 40 games in, so the NZ would give
Sidney Crosby a one game total of 17 goals and 41 assists (yikes!)

Cheers,
Jason Lepack


Michel Walsh wrote:
Hi,


SELECT a.skaterID, a.GP, Nz(a.g-b.g, a.g) AS G, Nz(a.a-b.a, a.a) AS A
FROM tbl_skater_stats AS a LEFT JOIN
JOIN tbl_skater_stats AS b
ON a.skaterID = b.skaterID AND a.GP=b.gp+1


should do, wihtout having to add a dummy record, either with Jet,
either
with MS SQL Server


Hoping it may help,
Vanderghast, Access MVP


I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add
a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.


Jason Lepack wrote:
So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new
players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number.
If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP,
then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack

 

[Jason Lepack]

Michel,

Your idea was a good one, but players get traded mid season, and that
doesn't work.

However, I figured out how to do it. Through making my data pretty I
overlooked the fact that the data already had a key built into it. The
player name/team field in the input looks like this:

Picard, Alexandre, ClBJ
Picard, Alexandre, Phi.

Now I was thinking that it would suck if one got traded to the other
team and then both would be on the same team and we couldn't tell them
apart. However, with USA Today's stats, if someone gets traded their
name looks like this:

Perrault, Joel, Pho.-St.L-Pho.

Joel Perrault started the season with Phoenix, got traded to St Louis,
and then traded back to Phoenix.

That field right there, that's my primary key

Cheers,
Jason Lepack

Hi,


Well, if the 'data' does not know, it surely cannot read what I have in my
mind


A possible solution could be to identify the players through their (first
name, last name, team, season). An extra table can then list those, and BY
YOUR MEANS, associate a 5-tuple to an ID of your own:


fname, lname, team, fromseason, toseason, id
joe smoe cannuk winter07, null, joeSmoe01
...
joe smoe habs winter07 spring07 joeSmoe02
...
joe smoe maple spring07 null joeSmoe02
...



indicates that joeSmoe01 is a different player than joeSmoe02, and who is
who is identifiable by looking at the team and the season (here I use name,
only for illustration, but definitively, dates are preferable to "season
names" )



and now, your data 'knows' as much as you do... well, as for
players/teams/seasons is concerned




Hoping it may help,
Vanderghast, Access MVP

Thanks for the bit about COALESCE.

Here's how I handle the data.

Raw Import into import table
Massage the data
Add any players that didn't exist previously
If this is not the very first load then add a stat record that has all
stats = 0 for all new player
Add the new stat records.

The only frustrating thing is that there are players who have the same
name, first and last.

Based on the data I have, I can't figure out how to differentiate the
two players. Players can be traded, so I can't use First, Last, Team.
I noticed that they play different positions, but the positions in the
stats can change from day to day as well. (Found this out today)

Hopefully I won't be back to the drawing board because of such a simple
thing. Anyways, thanks for all your help!

Jason Lepack

Hi,



but Crosby *is* good!


Now that you mentioned I used Nz (was too sleepy to see it by myself),
replace it by COALESCE in MS SQL Server.

The same extraordinary score would have been obtained by adding a dummy
zero-row. There is no real solution short of having ALL the weeks in the
basic data, of calling the first entry as "previous weeks, with an s"


Hoping it may help,
Vanderghast, Access MVP


Thanks so much Vanderghast!

That's great, I never really used Nz so that's good to learn!

That will really help me next season and in the playoffs but it won't
right now. The problem is that I started collecting the stats this
week, so my first entry is almost 40 games in, so the NZ would give
Sidney Crosby a one game total of 17 goals and 41 assists (yikes!)

Cheers,
Jason Lepack


Michel Walsh wrote:
Hi,


SELECT a.skaterID, a.GP, Nz(a.g-b.g, a.g) AS G, Nz(a.a-b.a, a.a) AS A
FROM tbl_skater_stats AS a LEFT JOIN
JOIN tbl_skater_stats AS b
ON a.skaterID = b.skaterID AND a.GP=b.gp+1


should do, wihtout having to add a dummy record, either with Jet,
either
with MS SQL Server


Hoping it may help,
Vanderghast, Access MVP


I came up with this:
SELECT A.skaterID, A.GP AS GP, ([A].[G]-.[G]) AS G,
([A].[A]-.[A]) AS A
FROM tbl_skater_stats AS A INNER JOIN tbl_skater_stats AS B ON
A.skaterID = B.skaterID
WHERE A.GP=.[gp]+1;

I then noticed that to get the result of the first game I had to add
a
record for 0 games played.

This data will give the expected result.
playerid,GP,G,A
1,0,0,0
1,1,2,3
1,2,3,4
1,3,5,4

If anyone else has something more elegant, I'm looking to learn more
about subqueries.
Also, will this syntax work on SQL Server? I'm eventually hoping to
port it from Access to ASP.Net with SQL Server backend.


Jason Lepack wrote:
So here's what I'm up to.

I save the webpage from USA Today.
http://www.usatoday.com/sports/hockey/nhl/skaters.htm

I click the button and it checks to see if there are any new
players,
any players have changed teams, etc.

Then the stats get imported into the table.

Fields: playerid, GP, G, A, PTS, etc.

Now, a players stats only get imported if the GP is a new number.
If
Darcy Tucker's last entry had 10 GP and the new record has 10 GP,
then
it doesn't create a new record.

What I need is a query to calculate a players stats for each game.

We'll work with this data:
playerid,GP,G,A
1,1,2,3
1,2,3,4
1,3,5,4

The result should be:
playerid,GP,G,A
1,1,2,3
1,2,1,1
1,3,2,0

Cheers,
Jason Lepack