*** VD 10 Commentary Thread ***

[mjl]

The google drive link let's me download a LahmanCP.SQL file which doesn't contain any data. Any thoughts?

that's interesting. It works for me. Maybe you have an overzealous antivirus system?

Here are the queries if you want them:

LahmanCH.sql

SELECT t1.Taken, t1.KO, t1.playerID, t1.Last, t1.First, t1.Start, t1.Finish, t1.Period, t1.AB, t1.H, t1.HR, t1.RBI, t1.R, t1.SB, t1.BB, t1.HB, t1.SF, t1.PA, t1.AVG,
c.G as C, ss.G as SS, 2b.G as 2B, of.G as OF, 3b.G as 3B, 1b.G as 1B,
t1.x7 as "7", t1.x8 as "8", t1.x9 as "9", t1.x0 as "0", t1.x1 as "1", t1.x2 as "2", t1.x3 as "3", t1.x4 as "4", t1.x5 as "5", t1.x6 as "6", t1.x70 as "70", t1.x80 as "80", t1.x9 as "90", t1.x00 as "00", t1.x10 as "10"
FROM
(SELECT
  '' as Taken,
  0 as KO,
  m.playerID as playerID,
  m.nameLast as Last,
  m.nameFirst as First,
  MIN(b.yearID) as Start,
  MAX(b.yearID) as Finish,
  IF(MAX(b.yearID) < 1899, "Deadball", 
    IF(MIN(b.yearID) > 1998, "Contemporary", "Normal")) as Period,
  SUM(b.AB) as AB,
  SUM(b.H) as H,
  SUM(b.HR) as HR,
  SUM(b.RBI) as RBI,
  SUM(b.R) as R,
  SUM(b.SB) as SB,
  SUM(b.BB) as BB,
  SUM(b.HBP) as HB,
  SUM(b.SF) as SF,
  SUM(b.AB) + SUM(b.BB) + SUM(b.HBP) + SUM(b.SF) + SUM(b.SH) as PA,
  SUM(b.H) / sum(b.AB) as AVG,
  IF(MIN(b.yearID) < 1880, 1, 0) AS x7,
  IF(MIN(b.yearID) < 1890, IF(MAX(b.yearID) > 1879, 1, 0), 0) AS x8,
  IF(MIN(b.yearID) < 1900, IF(MAX(b.yearID) > 1889, 1, 0), 0) AS x9,
  IF(MIN(b.yearID) < 1910, IF(MAX(b.yearID) > 1899, 1, 0), 0) AS x0,
  IF(MIN(b.yearID) < 1920, IF(MAX(b.yearID) > 1909, 1, 0), 0) AS x1,
  IF(MIN(b.yearID) < 1930, IF(MAX(b.yearID) > 1919, 1, 0), 0) AS x2,
  IF(MIN(b.yearID) < 1940, IF(MAX(b.yearID) > 1929, 1, 0), 0) AS x3,
  IF(MIN(b.yearID) < 1950, IF(MAX(b.yearID) > 1939, 1, 0), 0) AS x4,
  IF(MIN(b.yearID) < 1960, IF(MAX(b.yearID) > 1949, 1, 0), 0) AS x5,
  IF(MIN(b.yearID) < 1970, IF(MAX(b.yearID) > 1959, 1, 0), 0) AS x6,
  IF(MIN(b.yearID) < 1980, IF(MAX(b.yearID) > 1969, 1, 0), 0) AS x70,
  IF(MIN(b.yearID) < 1990, IF(MAX(b.yearID) > 1979, 1, 0), 0) AS x80,
  IF(MIN(b.yearID) < 2000, IF(MAX(b.yearID) > 1989, 1, 0), 0) AS x90,
  IF(MIN(b.yearID) < 2010, IF(MAX(b.yearID) > 1999, 1, 0), 0) AS x00,
  IF(MIN(b.yearID) < 2020, IF(MAX(b.yearID) > 2009, 1, 0), 0) AS x10
  FROM
  batting b
  INNER JOIN master m ON m.playerID = b.playerID
  GROUP BY b.playerID) t1
LEFT JOIN
(SELECT
  playerID,
  IF(sum(G) > 200, 1, 0) as G
  FROM
  fielding
  GROUP BY playerID, POS
  HAVING POS = 'C') c ON t1.playerID = c.playerID
LEFT JOIN
(SELECT
  playerID,
  IF(sum(G) > 200, 1, 0) as G
  FROM
  fielding
  GROUP BY playerID, POS
  HAVING POS = '1B') 1b ON t1.playerID = 1b.playerID
LEFT JOIN
(SELECT
  playerID,
  IF(sum(G) > 200, 1, 0) as G
  FROM
  fielding
  GROUP BY playerID, POS
  HAVING POS = '2B') 2b ON t1.playerID = 2b.playerID
LEFT JOIN
(SELECT
  playerID,
  IF(sum(G) > 200, 1, 0) as G
  FROM
  fielding
  GROUP BY playerID, POS
  HAVING POS = 'SS') ss ON t1.playerID = ss.playerID
LEFT JOIN
(SELECT
  playerID,
  IF(sum(G) > 200, 1, 0) as G
  FROM
  fielding
  GROUP BY playerID, POS
  HAVING POS = '3B') 3b ON t1.playerID = 3b.playerID
LEFT JOIN
(SELECT
  playerID,
  IF(sum(G) > 200, 1, 0) as G
  FROM
  fielding
  GROUP BY playerID, POS
  HAVING POS = 'OF') of ON t1.playerID = of.playerID;

LahmanCP.sql:

SELECT
  '' as Taken,
  0 as KO,
  m.playerID as playerID,
  m.nameLast as Last,
  m.nameFirst as First,
  MIN(p.yearID) as Start,
  MAX(p.yearID) as Finish,
  IF(MAX(p.yearID) < 1914, "Deadball", "Normal") as Period,
  SUM(p.IPouts) / 3 as IP,
  SUM(p.H) as H,
  SUM(p.ER) as ER,
  SUM(p.BB) as BB,
  SUM(p.SO) as SO,
  SUM(p.W) as W,
  SUM(p.G) as G,
  SUM(p.SV) as SV,
  SUM(p.ER) * 27 / SUM(p.IPouts) as ERA,
  (SUM(p.H) + SUM(p.BB)) * 3 / SUM(p.IPouts) as WHIP,
  SUM(p.L) as L,
  IF(MIN(p.yearID) < 1890, 1, 0) AS "78",
  IF(MIN(p.yearID) < 1900, IF(MAX(p.yearID) > 1889, 1, 0), 0) AS "9",
  IF(MIN(p.yearID) < 1910, IF(MAX(p.yearID) > 1899, 1, 0), 0) AS "0",
  IF(MIN(p.yearID) < 1920, IF(MAX(p.yearID) > 1909, 1, 0), 0) AS "1",
  IF(MIN(p.yearID) < 1930, IF(MAX(p.yearID) > 1919, 1, 0), 0) AS "2",
  IF(MIN(p.yearID) < 1940, IF(MAX(p.yearID) > 1929, 1, 0), 0) AS "3",
  IF(MIN(p.yearID) < 1950, IF(MAX(p.yearID) > 1939, 1, 0), 0) AS "4",
  IF(MIN(p.yearID) < 1960, IF(MAX(p.yearID) > 1949, 1, 0), 0) AS "5",
  IF(MIN(p.yearID) < 1970, IF(MAX(p.yearID) > 1959, 1, 0), 0) AS "6",
  IF(MIN(p.yearID) < 1980, IF(MAX(p.yearID) > 1969, 1, 0), 0) AS "7",
  IF(MIN(p.yearID) < 1990, IF(MAX(p.yearID) > 1979, 1, 0), 0) AS "8",
  IF(MIN(p.yearID) < 2000, IF(MAX(p.yearID) > 1989, 1, 0), 0) AS "99",
  IF(MIN(p.yearID) < 2010, IF(MAX(p.yearID) > 1999, 1, 0), 0) AS "00",
  IF(MIN(p.yearID) < 2020, IF(MAX(p.yearID) > 2009, 1, 0), 0) AS "10"
FROM
pitching p
INNER JOIN master m ON m.playerID = p.playerID
GROUP BY p.playerID;

[johnnya24]

johnny, I can trade him, but it won't be cheap. Marichal and your 10th?

Anything is possible

Will repick in a little bit.

[johnnya24]

Re-picked Pete Rose ... bit of a gamble, but whatever.

[johnnya24]

First time I've picked Pete Rose in nearing 40 drafts.

[mjl]

40?! I don't feel nearly as bad about sucking at this then.

[johnnya24]

40?! I don't feel nearly as bad about sucking at this then.

Yeah ... 26 or 27 on the old site, and now 10 here.

[Long John]

First time in front of a puter since 10am. Will analyze and pick shortly with vain attempts at avoiding intense deliberation.

[heyelander]

still getting the hang of things with the camera.

[johnnya24]

VD Spreadsheet Update

Will hopefully get this out tonight.

I am noticing however that the playerid's are not always the same as out previous SS. For instance, martiped02 is now martipe03; walshed01 is now walshed02. So unfortunately the BY sheet is going to have to be constantly updated as we go along. It's not obvious which players do not correspond until the error appears on the Rounds sheet. But it can't be missed, so that's a good thing.

So far, 4 pitchers:

Pedro Martinez
Ed Walsh
Bob Gibson
Eddie Plank

Scratch Eddie Plank ... Eddie Plank is just missing from the Career pitching sheet. Looks like MJL has decided to Lance Armstrong his ass out of the record books

Pedro missing also ...

[mjl]

hm. that shouldn't happen. let me take a look at my code... oh, that's not good. it's worse than you think it is - martipe02, walshed01, etc. are just missing from the CP data.

ok, give me a little time to fix this.

[johnnya24]

hm. that shouldn't happen. let me take a look at my code... oh, that's not good. it's worse than you think it is - martipe02, walshed01, etc. are just missing from the CP data.

ok, give me a little time to fix this.

I'm just manually inputting them. In the mean time I'll unfix the BY sheet.

[mjl]

The BY sheets should be fine.

[johnnya24]

The BY sheets should be fine.

Yup.

I'm updating the career hitting also ... the same?

[johnnya24]

Based on career stats alone, we can delete approximately half of the pitchers without even looking at a BY. That's a lot of shattered dreams. Hundreds of guys pitched less than 1 inning in the majors.

[mjl]

Yup.

I'm updating the career hitting also ... the same?

yeah. It's not a difficult fix, I'll get it in. For anyone whose name is the same as another player, the sheet currently only has the most recent player.