daily pastebin goal
63%
SHARE
TWEET

Untitled

a guest Jul 15th, 2014 233 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE RidgebaseSBA AS SELECT a.GAME_ID,
  2.     CASE
  3.         WHEN
  4.             (a.RUN1_SB_FL OR a.RUN1_CS_FL) = 'T'
  5.                 AND a.start_bases_cd = 1
  6.                 OR a.start_bases_cd = 5
  7.         THEN
  8.             CONCAT(a.pit_ID,
  9.                     '+',
  10.                     a.POS2_FLD_ID,
  11.                     '+',
  12.                     a.BASE1_RUN_ID)
  13.         WHEN
  14.             (a.RUN2_SB_FL OR a.RUN2_CS_FL) = 'T'
  15.                 AND a.start_bases_cd = 2
  16.         THEN
  17.             CONCAT(a.pit_ID,
  18.                     '+',
  19.                     a.POS2_FLD_ID,
  20.                     '+',
  21.                     a.BASE2_RUN_ID)
  22.     END AS battery,
  23.     a.pit_id AS pitcher,
  24.     a.POS2_FLD_ID AS catcher,
  25.     CASE
  26.         WHEN
  27.             (a.RUN1_SB_FL OR a.RUN1_CS_FL) = 'T'
  28.                 AND a.start_bases_cd = 1
  29.                 OR a.start_bases_cd = 5
  30.         THEN
  31.             a.BASE1_RUN_ID
  32.         WHEN
  33.             (a.RUN2_CS_FL OR a.RUN2_SB_FL) = 'T'
  34.                 AND a.start_bases_cd = 2
  35.         THEN
  36.             a.BASE2_RUN_ID
  37.     END AS baserunner,
  38.     CASE
  39.         WHEN a.OUTS_CT = 0 THEN 1
  40.         ELSE 0
  41.     END AS OUTS_0,
  42.     CASE
  43.         WHEN a.OUTS_CT = 1 THEN 1
  44.         ELSE 0
  45.     END AS OUTS_1,
  46.     CASE
  47.         WHEN a.OUTS_CT = 2 THEN 1
  48.         ELSE 0
  49.     END AS OUTS_2,
  50.     CASE
  51.         WHEN a.START_BASES_CD = 1 THEN 1
  52.         ELSE 0
  53.     END AS BS1__,
  54.     CASE
  55.         WHEN a.START_BASES_CD = 2 THEN 1
  56.         ELSE 0
  57.     END AS BS_2_,
  58.     CASE
  59.         WHEN a.START_BASES_CD = 5 THEN 1
  60.         ELSE 0
  61.     END AS BS1_3,
  62.     SUM(IF(a.EVENT_CD = 6, 1, 0)) AS CS,
  63.     SUM(IF(a.EVENT_CD = 4, 1, 0)) AS SB,
  64.     SUM(IF(a.EVENT_CD = 6, 1, 0)) + SUM(IF(a.EVENT_CD = 4, 1, 0)) AS SBA,
  65.     SUM(a.EVENT_OUTS_CT) / 3 AS IP,
  66.     CASE WHEN a.WP_FL = "T" THEN 1 ELSE 0 END AS WP,
  67.     CASE WHEN a.PB_FL = "T" THEN 1 ELSE 0 END AS PB,
  68.     CASE WHEN a.EVENT_CD = 8 THEN 1 ELSE 0 END AS PK,
  69.     CASE
  70.         WHEN BAT_HOME_ID = 1 THEN a.HOME_SCORE_CT - a.AWAY_SCORE_CT
  71.         ELSE a.AWAY_SCORE_CT - a.HOME_SCORE_CT
  72.     END AS scoremargin,
  73.     a.INN_CT,
  74.     CASE
  75.         WHEN a.PIT_HAND_CD = 'L' THEN 1
  76.         ELSE 0
  77.     END AS P_hand_L,
  78.     CASE
  79.         WHEN a.PIT_HAND_CD = 'R' THEN 1
  80.         ELSE 0
  81.     END AS P_hand_R,
  82.     CASE
  83.         WHEN a.BAT_HAND_CD = 'L' THEN 1
  84.         ELSE 0
  85.     END AS B_hand_L,
  86.     CASE
  87.         WHEN a.BAT_HAND_CD = 'R' THEN 1
  88.         ELSE 0
  89.     END AS B_hand_R FROM
  90.     events a
  91. WHERE
  92.     a.start_bases_cd = 1
  93.         OR a.start_bases_cd = 2
  94.         OR a.start_bases_cd = 5
  95. GROUP BY a.game_id , a.pit_id , a.pos2_fld_id  , a.inn_ct,  CASE
  96.         WHEN
  97.             (a.RUN1_SB_FL OR a.RUN1_CS_FL) = 'T'
  98.                 AND a.start_bases_cd = 1
  99.                 OR a.start_bases_cd = 5
  100.         THEN
  101.             a.BASE1_RUN_ID
  102.         WHEN
  103.             (a.RUN2_CS_FL OR a.RUN2_SB_FL) = 'T'
  104.                 AND a.start_bases_cd = 2
  105.         THEN
  106.             a.BASE2_RUN_ID
  107.     END;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top