Week 17 NFL Lines and Least Squares Predictions

If you have not read any of my previous Least Squared posting, please refer to the initial post here.

Week 16 in review: Only one game was in the range where we have been fairly confident on the selections. New England was a favorite in the system, but getting 2.5 points from Vegas. New England destroyed the Ravens, so the >100% difference between the expected and Vegas lines, bring that rule to 12-3 over the past four weeks.

Overall, the LS method went 8-7 (the Green Bay game again did not have a line, due to the unexpected status of Aaron Rodgers). This brings the Least Squares method to 35-24-1 over the last four weeks.

In games where the absolute raw difference was greater than 2.5 went 4-2, bringing its totals to 17-10 for the past four weeks.

Week 17 schedule: Five games fulfill the greater than 100% difference between expected and Vegas lines. Miami (-6) is a heavy favorite over the New York Jets. Detroit (+3) is considered a favorite over Minnesota, despite getting points. Washington (+4) is in the same position, as they are getting points at the New York Giants, despite being the favorite. The Kansas City Chiefs (+9.5) are getting huge points, despite being straight up favorites against the San Diego Chargers. Dallas (+6.5) is getting points as well, although the system is considering them a favorite.

Why these lines are probably so different
Being the final weekend of the season, there are obvious reasons for the dramatic differences in the lines. One is that some games have almost zero playoff implications to one team, but mean everything to the other (Kansas City at San Diego, NY Jets at Miami). The other main reason is probably injuries and general chaos among one of the teams (Washington at NY Giants, Philly at Dallas).

Both Washington and Dallas are disasters. In Washington, Shanahan has benched RGIII and virtually ticked off everyone, including his son and OC, Kyle. Talk out of Dallas is that Tony Romo and maybe Dez Bryant are out or probable. With a playoff spot in the line, who knows who will actually play for the Cowboys? I suspect they will make a game of it, but then again, they may just be ready to implode. I am also surprised that Washington game is so out of whack, since the Giants have not been too great, despite how bad Washington may tank this game. Again, gamble at your own risk.

I have listed the data below.

Visitor Home Line Expected Diff (raw) Diff/line
Carolina Panthers Atlanta Falcons -6.5 -10.28 -3.78 58%
Baltimore Ravens Cinncinati Bengals 6.5 8.30 1.80 28%
Houston Texans Tennessee Titans 7 9.88 2.88 41%
Jacksonville Jaguars Indianapolis Colts 11.5 16.83 5.33 46%
New York Jets Miami Dolphins 6 12.93 6.93 116%
Detroit Lions Minnesota Vikings 3 -1.90 -4.90 -163%
Washington Redksins New York Giants 4 -2.46 -6.46 -161%
Cleveland Browns Pittsburgh Steelers 7 7.01 0.01 0%
Denver Broncos Oakland Raiders -13 -15.39 -2.39 18%
Buffalo Bills New England Patriots 9.5 11.19 1.69 18%
Tampa Bay Buccaneers New Orleans Saints 13 13.42 0.42 3%
San Francisco 49ers Arizona Cardinals -1 -1.99 -0.99 99%
Kansas City Chiefs San Diego Chargers 9.5 -2.11 -11.61 -122%
St. Louis Rams Seattle Seahawks 10.5 14.53 4.03 38%
Philadelphia Eagles Dallas Cowboys -6.5 3.73 10.23 -157%

College Bowl Season Predictions Based on Least Squared Non-Linear Programming Model

There’s no shortage of data out there when it comes to college football, so I decided to take the time to create a least squares model, based on the same principals I have been using for the NFL, and outlined here.

The idea was to take all 752 schools that played college football, cross that with all 4138 games that were played (up to last weekend) and see how they predict the games (especially versus the Vegas lines).

When you try to create a model with 753 variables (I also included home field advantage for the regular season games as a variable), you quickly begin to test the limitations of Excel and Solver. After a little digging, I discovered that Frontline Solver, the company that developed Solver for Excel, limits the version included with the Microsoft product to 200 variables.

Not to be stopped, I found they have some advanced pro models and engines that will handle significantly more and downloaded a 16-day trial of these.

You set it to pick the best engine and it runs. For this particular problem, it happened to run the Gurobi Optimization engine.

From there, it is fairly plain and simple. You run the solver and it calculates a ranking for each team. I just plugged in the bowl games and here’s what we get.

Team A Team B Exp Vegas Diff % Diff
Colorado State Washington St 7.68 5.5 2.18 40%
Southern Cal Fresno State -8.22 -5 -3.22 64%
Buffalo San Diego St -6.86 -1.5 -5.36 357%
Louisiana-Lafayette Tulane -0.67 1.5 -2.17 -145%
Ohio U. East Carolina 16.48 14 2.48 18%
Oregon State Boise State -0.74 -3.5 2.76 -79%
Pittsburgh Bowling Green 5.69 5 0.69 14%
Utah State Northern Illinois -5.46 2 -7.46 -373%
Marshall Maryland -5.11 -2 -3.11 156%
Syracuse Minnesota 6.41 5 1.41 28%
Washington Brigham Young -8.75 -3 -5.75 192%
Rutgers Notre Dame 20.75 14 6.75 48%
Cincinnati North Carolina 5.93 3 2.93 98%
Miami FL Louisville 3.60 3.5 0.10 3%
Michigan Kansas State 3.23 3.5 -0.27 -8%
Middle Tennessee St. Navy 9.60 6 3.60 60%
Mississippi Georgia Tech 3.71 -3.5 7.21 -206%
Texas Oregon 18.33 14 4.33 31%
Texas Tech Arizona St 20.19 14.5 5.69 39%
Boston College Arizona 12.46 7.5 4.96 66%
Virginia Tech UCLA 11.22 7.5 3.72 50%
Mississippi State Rice -9.02 -7 -2.02 29%
Duke Texas A&M 9.69 13 -3.31 -25%
Nebraska Georgia 10.82 9 1.82 20%
Nevada-Las Vegas North Texas 7.91 6.5 1.41 22%
Wisconsin South Carolina -2.33 0 -2.33 -233%
Iowa LSU 7.21 8 -0.79 -10%
Michigan State Stanford 9.24 6 3.24 54%
Central Florida Baylor 24.64 17 7.64 45%
Oklahoma Alabama 14.31 16 -1.69 -11%
Oklahoma State Missouri -1.74 1.5 -3.24 -216%
Clemson Ohio State 2.49 2.5 -0.01 0%
Houston Vanderbilt -5.73 3 -8.73 -291%
Arkansas St Ball St 10.52 9.5 1.02 11%
Auburn Florida State 19.81 7.5 12.31 164%

Here’s what the table means. Vegas has Auburn as a (+7.5) underdog to Florida State, however, the model shows FSU as an almost 20-point favorite, an almost 12-point difference.

One of the most interesting lines is Clemson as a 2.5-point underdog to Ohio State. The LS model has that game as a 2.49-point spread, almost exactly spot on.

Several others are extremely close like that: Miami-Louisville (3%), Michigan-Kansas State (8%), Iowa-LSU (10%), Oklahoma-Alabama (11%) and Arkansas State-Ball State (11%).

Here’s a quick rundown of which side the computer is on for each game (including the Vegas lines): Washington State (-5.5), USC (-5), Buffalo (-1.5), Louisiana-Lafayette (+1.5), East Carolina (-14), Boise State (+3.5), Bowling Green (-5), Utah State (+2), Marshall (-2), Minnesota (-5), Washington (-3), Notre Dame (-14), North Carolina (-3), Louisville (-3.5), Michigan (+3.5), Navy (-6), Georgia Tech (+3.5), Oregon (-14), Arizona State (-14.5), Arizona (-7.5), UCLA (-7.5), Mississippi State (-7), Duke (+13), Georgia (-9), North Texas (-6.5), Wisconsin (0), Iowa (+8), Stanford (-6), Baylor (-17), Oklahoma (+16), Oklahoma State (+1.5), Clemson (+2.5), Houston (+3), Ball State (-9.5) and Florida State (-7.5).

Of course bowl games are strange for a variety of reasons, (hitting the buffet circuit, poor practices, lack of focus…) so there always seems to be a lack of consistency.

We will check back in a few weeks on how well this did.

Least Squares Method Perfect in Week 15 and the Art of Slowing Down

Sometimes you just need to slow down and look at the data a little closer.

That was the case last week when I mistakenly posted the wrong side of the New England-Miami line. I mentioned it was probably best to stay away from it all together due to the raw difference being so small, but I also stated the wrong side to take. Oh well. Lesson learned.

Week 15 review – The least-squares method and choosing only those lines where the percentage difference of expected and actual Vegas line over the actual line was greater than 100% went a shocking 4-0. Did I bet it this way? Nope. The three lines between 80-100% went 1-2. In games where the absolute raw was greater than 2.5 went 3-2. Overall, the LS method went an incredible 11-4-1.

Three week totals – Overall the LS method is 27-17-1. It is 11-3 where the percentage expected vs Vegas line is greater than 100% and 13-8 where the absolute raw is greater than 2.5 points.

Week 16 lines and Least Squares

Not a whole lot to look at here, just the Patriots-Ravens game. Is Vegas realizing that LS is hot?

New England Patriots Baltimore Ravens 2.5 -1.06 -3.56 -142%

The Patriots are getting 2.5 on the road. LS seems to think they should be a 1 point favorite, even on the road. The way the Patriots have played, who knows? Heck, if it weren’t for Justin Tucker (#hookem), the Ravens would have about half as many wins.

Since this week is fairly boring, I will go ahead and list the games where the absolute raw is greater than 2.5 points.

Dallas Cowboys Washington Redskins -3 -5.87 -2.87 96%
Minnesota Vikings Cincinnati Bengals 7.5 12.55 5.05 67%
Denver Broncos Houston Texans -10.5 -14.71 -4.21 40%
Oakland Raiders San Diego Chargers 10 13.52 3.52 35%
New England Patriots Baltimore Ravens 2.5 -1.06 -3.56 -142%
Atlanta Falcons San Francisco 49ers 13 17.53 4.53 35%

In order of raw strength, it appears LS prefers: Cincinnati, San Francisco, Denver, New England, San Diego and Dallas. Several big lines there. I think I will likely just stay away… but if I were to do one of my stupid parlays, I’d take New England, Dallas and Cincinnati. That should get you about 6-1 odds, but remember, bet at your own risk.

NFL Week 15 Lines, Week 14 update and Least Squares NLP

Just a quick update on last week’s post where I use Non-Linear Programming methods to predict the NFL lines. Let’s rehash. You can also read the explanation post HERE, where I dive into Non-Linear Programming and the methods involved.

For Week 13 games, the least squares approach went 10-5 overall, 3-1 where the percentage of expected vs. Vegas line was greater than 100% and 6-4 when the absolute raw difference was greater than 2.5.

For week 14, it was not very good overall. It went 6-8, but went 4-2 when the percentage of expected vs. Vegas was greater than 100% and 4-2 when the absolute raw was greater than 2.5. Note: we did this experiment AFTER the Thursday night game.

We had a $5 parlay bet with four games and we went 3-1. Ugh. Should’ve of just bet straight up. Oh well, not as much fun.

Totals for two weeks: 16-13 overall, 7-3 when the percentage of expected vs. Vegas line was greater than 100% and 10-6 when the absolute raw is greater than 2.5.

So that give us a few games to really look at for WEEK 15:

Seattle Seahawks New York Giants -7 -15.54 -8.54 122%
Houston Texans Indianapolis Colts 6 11.98 5.98 100%
Buffalo Bills Jacksonville Jaguars -1 -3.25 -2.25 225%
New England Patriots Miami Dolphins -1 0.41 1.41 -141%

It recommends taking Seattle (-7), Indianapolis (-6), Buffalo (-1) and New England (-1). Of course that small difference and line for the NE-MIA game is suspicious, but those are the pure suggestions.

How about this? A Seahawks, Bills, Colts and Bengals parlay. $5->$61.73 and maybe a Bengals, Chiefs, Seahawks parlay, $5->$30.69…

These three games are getting dangerously close to the 100%-zone, so may be considered.

New York Jets Carolina Panthers 11.5 22.63 11.1 97%
Kansas City Chiefs Oakland Raiders -5.5 -10.9 -5.4 97%
Cincinnati Bengals Pittsburgh Steelers -3 -5.41 -2.4 80%

The plays it suggests here are Carolina (-11.5), Kansas City (-5.5) and Cincinnati (-3). I like these as well, but hate huge lines like the Carolina game. We will see. Parlays are a bigger gamble, but more fun.

NFL Week 14 Predictions, Ratings, Optimization and Non-Linear Programming

We finished classes yesterday, so all that is left for the semester is a homework assignment, three projects and three more exams. I have whittled this away to only needing to complete one last project and study for the exams. But, instead of finishing my database project, which is due Sunday, I elected to take a deeper dive into a classroom example for an exam I had yesterday.

They are not totally unrelated. The third db project involves converting the current NFL season data (mostly scores and teams) into a graph database model, but more on that at a later date.

While formatting the data, I was reminded of an example we worked in Optimization class Monday. The problem involved calculating NFL team ratings using Non-Linear Programming methods (with Excel). The example comes from our textbook, Practical Management Science by Winston and Albright (South-Western Cengage Learning, 4th Ed.).

What is Non-Linear Programming?

Almost verbatim from the book: It is the solving of an optimization problem where the objective or constraints are non-linear functions of the decision variables.

For the layman: It is a problem where there is an objective, but it is constrained by variables, which cannot be represented by simple straight lines. If you are interested or confused, there is plenty out there to help you understand Linear Programming (start with, http://en.wikipedia.org/wiki/Linear_programming).

The jump from linear to non-linear can be something simple. I like their revenue example, where revenue is a price multiplied by quantity sold. Well, quantity sold is a function of price, via a demand function. So revenue is price multiplied by a function of price. Even if demand is linear in price, the product of price and demand is quadratic because it includes a squared price.

BACK TO FOOTBALL-THE PROBLEM
The objective was to use NLP to create ratings for NFL teams that best predict the actual point spreads. By point spreads, they mean the actual score differential, not the betting lines.

As you may know, I have a little experience in this. In 1993, I created my own version of this and have applied it to both high school football (www.sixmanfootball.com) and college tennis (www.texascollegetennis.com). The basics of my programs use much of the same logic, but are more iterative and detailed.

In the classroom example, we set up the spreadsheet the following way.

First you create a table of team names and ratings. The ratings can be left blank or given any number, as they will be the values the Solver will change to meet our constraints. Somewhere near there, you can also add a home field advantage cell and make that also a variable cell to be tested in the Solver.

Next you create a table of played games, including home team, visiting team, home score and visitor score. From this you get what they call the point spread (score differential).

Extending that table, you create an expected point spread = home team rating – visiting team rating + home field advantage. Remember these are the variables that will be manipulated.

Again you extend that table, adding a squared predicted error value = (actual point spread (score differential) – predicted point spread) ^ 2 (squared).

Now you need to create a few constraints. The first is the objective. Yes, the ratings are our objective, but we want them to be optimized. To do this, we set the objective as being the sum of errors or to be more precise, the sum of squared errors. We want to minimize this.

Why squared errors? I think they gloss over this in the book, stating this is just has a long tradition in statistics. Yes, you can use the sum of absolute (value) errors as well. Like the authors, I prefer squared errors.

Think of it this way, when you get a big difference (error), like 10, the squared error is 100. When you get an error of 5, the squared error is only 25. I look at squared errors as a way to ‘punish’ the larger errors, bringing the results even tighter.

The final part of setting up the model is to set a boundary or what the authors term normalizing the data, so you only get one result. If you do not do this, there will not be a single unique answer. The idea is to bound the mean of the ratings to some number. They use 85, as that is what Jeff Sagarin does. Part of the logic is that a perfect team would approach 100, which seems like a pretty logical thing to people to understand. In my example, I used 20. This makes the results look more like football scores. I wouldn’t normally do this, but the variance in NFL scores is pretty tight.

THE SOLUTION-2013 WEEK 14
Instead of running the 2003 or 2009 data, as we did in class. I came across the current 2013 data. Once you have the data, it really only takes a second and boom, you get ratings. I even added last night’s Jags-Texans game.

Here’s a sample of what you get.

1 Seattle Seahawks 33.28
2 Carolina Panthers 31.55
3 Denver Broncos 31.37
4 San Francisco 49ers 29.56
5 New Orleans Saints 27.49

So what does this mean?

If the Seahawks played the Saints on a neutral field, they should be about a 5.5-point favorite (33.28-27.49).

An even better question – How can we use this?

Well, I just happen to have the current betting lines (from sportbook.com), so let’s compare.

Visitor V-Rat Home H-Rat Line Exp

Diff (raw)

Diff/Line

Kansas City Chiefs

25.24

Washington Redskins

12.55

-3.5

-9.75

6.25

-179%

Minnesota Vikings

13.30

Baltimore Ravens

18.61

7

8.26

-1.26

-18%

Cleveland Browns

12.72

New England Patriots

24.62

13

14.84

-1.84

-14%

Oakland Raiders

13.73

New York Jets

10.07

2.5

-0.72

3.22

129%

Indianapolis Colts

22.23

Cincinnati Bengals

24.20

6.5

4.92

1.58

24%

Carolina Panthers

31.55

New Orleans Saints

27.49

3.5

-1.11

4.61

132%

Detroit Lions

21.09

Philadelphia Eagles

20.60

3

2.45

0.55

18%

Miami Dolphins

20.43

Pittsburgh Steelers

16.77

3.5

-0.71

4.21

120%

Buffalo Bills

16.00

Tampa Bay Buccaneers

17.43

3

4.37

-1.37

-46%

Tennessee Titans

19.51

Denver Broncos

31.37

13

14.80

-1.80

-14%

St Louis Rams

22.39

Arizona Cardinals

24.27

6

4.83

1.17

19%

New York Giants

15.71

San Diego Chargers

20.12

3.5

7.35

-3.85

-110%

Seattle Seahawks

33.28

San Francisco 49ers

29.56

2.5

-0.78

3.28

131%

Dallas Cowboys

22.11

Chicago Bears

17.92

-1

-1.25

0.25

-25%

OK, now what does this mean?

Take it for what you want, but it appears to show us a few big differences between the betting lines and our programs’ expected lines. So we should bet, right? Not so fast my friends.

It appears the guys making the lines are fading on Kansas City, based on three straight losses. A team that looks like almost a 10-point favorite (even on the road) in our system, is only giving 3.5 points.

Another scary game would be Carolina getting 3.5 points at New Orleans. The system says the Panthers SHOULD actually be giving a point, instead of getting 3.5. I think the line of thinking goes that maybe the New Orleans home field advantage is worth a little more than the 2.9 points our system. New Orleans is also what the bettors term a very ‘Public’ team, meaning they are very popular among the casual bettors, compared to Carolina. This may skew the line even further.

Now to really get into evaluation a bit more (and avoid my project as well) I decided to go back a week and see how things worked out last week, based on last week’s ratings.

Visitor V-Rat Home

H-Rat

Line

Exp

Diff (raw)

Diff/line

V

H

Correct

Green Bay Packers

19.65

Detroit Lions

19.44

6

2.59

3.41

57%

10

40

0

Oakland Raiders

12.96

Dallas Cowboys

22.35

8

12.18

-4.18

-52%

24

31

0

Pittsburgh Steelers

16.89

Baltimore Ravens

19.52

3

5.42

-2.42

-81%

20

22

0

Tennessee Titans

19.25

Indianapolis Colts

21.25

3.5

4.79

-1.29

-37%

14

22

1

Denver Broncos

30.86

Kansas City Chiefs

25.41

-6

-2.65

-3.35

56%

35

28

1

Jacksonville Jaguars

6.31

Cleveland Browns

14.05

7.5

10.54

-3.04

-40%

32

28

0

Tampa Bay Buccaneers

17.70

Carolina Panthers

30.95

6.5

16.05

-9.55

-147%

6

27

1

Chicago Bears

18.47

Minnesota Vikings

13.05

1

-2.62

3.62

362%

20

23

0

Arizona Cardinals

23.99

Philadelphia Eagles

20.40

3.5

-0.80

4.30

123%

21

24

1

Miami Dolphins

19.78

New York Jets

11.89

2

-5.09

7.09

355%

23

3

1

Atlanta Falcons

15.88

Buffalo Bills

17.29

4.5

4.20

0.30

7%

34

31

1

St Louis Rams

21.78

San Francisco 49ers

29.29

7.5

10.30

-2.80

-37%

13

23

1

New England Patriots

25.66

Houston Texans

12.56

-6.5

-10.30

3.80

-58%

34

31

1

Cincinnati Bengals

24.41

San Diego Chargers

20.14

-1.5

-1.47

-0.03

2%

17

10

1

New York Giants

15.19

Washington Redskins

13.07

1

0.68

0.32

32%

24

17

1

New Orleans Saints

29.49

Seattle Seahawks

31.07

5

4.38

0.62

12%

7

34

0

Now we are getting somewhere. The system actually went 10-5. In games where the expected vs line was greater (or less) than 100%, it went 3-1. Hmmm. In games where the absolute raw was greater than 2.5, it was 6-4.

So, the system says we should probably look again at the big differences.

Maybe we should consider taking Kansas City, Oakland, Carolina, Miami, San Diego and Seattle? A $5 wager would bring you $242.92 in winnings, if they ALL beat the spread.

I’m a little concerned about too many road teams. How about we drop it to KC, Oakland, Miami and San Diego? If you bet $5 here, you win $61.52 if they all win. Sounds good. Done. We will send Prof. Muthuraman a little something if we hit.

Converting College Football Data for a Simple Website, using PHP, SQL and MySQL

The pages discussed here can be found at: http://www.sixmanguru.com/ncaa13.php

The idea of this project came from a thought that there does not exist a single place where you can see NCAA college football teams’ records and schedules easily, without having to make a bunch of clicks on very ‘graphically full’ websites.

The first step was to see if the data could be found easily. This was done easily, as Peter Wolfe keeps this information updated daily. (http://prwolfe.bol.ucla.edu/cfootball/scores.htm). Wolfe is a member of the official BCS rankings and also includes a comprehensive listing of conferences as well.

I initially broke the data we collected into four tables, listed on the top of the tables list (p2-tables-2.pdf). These tables are essentially the data in its raw form with some ‘cleaning’ taking place.

Next was to build the website. Based on this blog, a template was created to host the site. The entry page is http://www.sixmanguru.com/ncaa13.php.

The thought was to have a way to separate the various divisions of college football (NCAA I, NCAA I FBS, NCAA II, NCAA III and so on). This was accomplished by having a link for each at the top of the page that will pass a variable back to the site if clicked.

Once a division variable is passed (NCAA I BCS is assumed if there is a direct click to the site), there is a drop-down menu to filter the teams by conferences. ($q = “SELECT Conf_ID,conference_names FROM Conferences WHERE Div_ID='”.$division_id.”‘ ORDER BY conference_names”;)

Once a selection is made, the website show the conference standings, including conference wins, conference losses, wins and losses. This query only access the ‘cached’ table, big_team_records, we created, but more on this later. ($q = “SELECT * FROM big_team_records JOIN Conferences ON big_team_records.conference=Conferences.conference_names WHERE Conferences.Conf_ID='”.$conf_id.”‘ ORDER BY conf_wins DESC, wins DESC”;)

All teams shown in the results are ‘clickable’. If clicked, they link to another page (teams.php), passing the team ID. The team page then shows the data from the game file, including date, team, W/L and result. ($q = “SELECT * FROM big_game_table WHERE team_a='”.$team_name.”‘ OR team_b='”.$team_name.”‘ ORDER BY game_date ASC”;)

The first big problem was to create SQL code that would take what we have and create team records, including conference records. Since you have to create separate joins for the two sides of the scores when calculating wins/losses for a team, this can put a strain on the server, especially one that is online. When we got into also trying to calculate the conference wins/losses as well, it really made no sense with the MySQL server we were running.

The solution was to create ‘leftside’ and ‘rightside’ views, then combine them in another view, whole_game. This was all done on the Oracle SQL server. The view for the team_record was also done on the Oracle SQL server. Both of the finish product views were then imported to tables. These tables were then exported to Excel files and imported to the MySQL server.

This may be a little more work than initially anticipated, but the end result creates a much faster and flexible solution. The large game table that was created can be used to create much more interactivity with website visitors in the future, since all games are coded with each team’s individual and conference ID.

One other issue we had was that the date the initial data included worked fine for Oracle SQL, but not MySQL. This is shown below, but we took care of this by added some formatting for the initial view, so this would be invisible.

TO_CHAR(game_date, ‘YYYY-MM-DD’) AS game_date
This little bit of code had to be added since the date format used by the website is was scraped form uses a format compatible in our Oracle SQL db, BUT NOT in our MySQL db.

After the main four tables are created, we create master tables to essentially be used as cache in the MySQL database for the website. Their implementation is good, but allows for expansion of searches.

THIS MAKES THE ENTIRE VISITOR SIDE WORK

CREATE VIEW leftside AS SELECT ncaascores2013.game_ID, TO_CHAR(game_date, ‘YYYY-MM-DD’) AS game_date, fbteams_2013.team_ID AS VID, FBConferences.Conf_ID AS V_CID, ncaascores2013.team_a, ncaascores2013.score_a

FROM ncaascores2013
INNER JOIN fbteams_2013
ON ncaascores2013.team_a = fbteams_2013.team_name
INNER JOIN FBConferences
ON FBConferences.conference_names = fbteams_2013.conference

THIS MAKES THE ENTIRE HOME SIDE WORK

CREATE VIEW rightside AS SELECT ncaascores2013.game_ID, fbteams_2013.team_ID AS HID, FBConferences.Conf_ID AS H_CID, ncaascores2013.team_b, ncaascores2013.score_b, ncaascores2013.site
FROM ncaascores2013
INNER JOIN fbteams_2013
ON ncaascores2013.team_b = fbteams_2013.team_name
INNER JOIN FBConferences
ON FBConferences.conference_names = fbteams_2013.conference

COMBINE THEM

CREATE VIEW whole_game AS SELECT leftside.game_ID, leftside.game_date, leftside.VID, leftside.V_CID, leftside.team_a, leftside.score_a,
rightside.HID, rightside.H_CID, rightside.team_b, rightside.score_b, rightside.site
FROM leftside
JOIN rightside
ON leftside.game_ID=rightside.game_ID

MAKE A TABLE TO THEM PUT THE VIEW DATA IN

create the new table from the view
CREATE TABLE tblWholeGame AS SELECT *
FROM whole_game;

IMPORT THE DATA TO THE TABLE

INSERT INTO tblwholegame
SELECT *
FROM whole_game;

THIS DATA IS THEN EXPORTED TO USE IN THE OTHER ONLINE MYSQL DATABASE

THIS CREATES THE INITIAL TEAM_RECORD VIEW

CREATE view team_record AS SELECT
team_name,
SUM(CASE WHEN (team_ID=VID AND score_a>score_b) OR
(team_ID=HID AND score_b>score_a) THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN (team_ID=VID AND score_a<score_b) OR
(team_ID=HID AND score_b<score_a) THEN 1 ELSE 0 END) AS losses, SUM(CASE WHEN (team_ID=VID AND score_a>score_b AND whole_game.V_CID=whole_game.H_CID) OR
(team_ID=HID AND score_b>score_a AND whole_game.H_CID=whole_game.V_CID) THEN 1 ELSE 0 END) AS conf_wins,
SUM(CASE WHEN (team_ID=VID AND score_a<score_b AND whole_game.V_CID=whole_game.H_CID) OR
(team_ID=HID AND score_b<score_a AND whole_game.V_CID=whole_game.H_CID) THEN 1 ELSE 0 END) AS conf_losses
FROM fbteams_2013,whole_game
GROUP BY team_name
ORDER BY team_name

WE THEN ADD SOME ADDITONAL DATA TO THIS

CREATE VIEW big_team_record AS SELECT fbteams_2013.team_id, fbteams_2013.division,fbteams_2013.conference,team_record.*
FROM fbteams_2013, team_record
WHERE fbteams_2013.team_name=team_record.team_name;

CREATING THE TABLE

CREATE TABLE tblBigTeamRecord AS SELECT *
FROM big_team_record;

IMPORTING THE DATA TO THE TABLE

INSERT INTO tblbigteamrecord
SELECT *
FROM big_team_record;

AGAIN, THIS DATA MUST BE EXPORTED TO THE ONLINE MYSQL DATABASE