I have a table named players with these fields:
playerid {int}
teamid {int}
jersey {int}
playername {varchar(50)}
age {int}
And a table named pitchcounts with these fields:
id {int}
gamenum {int}
playerid {int}
pitchcount {int}
Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed
and the days rest required and next availability date of the pitcher based on the criteria below:
In-House Ages 13-16 (Babe Ruth)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-95 3 Rest
********************************************************
In-House Ages 11 & 12 (Majors-American, National & Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-85 3 Rest
No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week.
Travel Ages 8, 9, 10, 11 & 12
Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House.
If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play.
********************************************************
In-House Ages 10 and 9'S in Majors (Majors American, National, Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-75 3 Rest
Double A In-House (AA)
No more than 40 pitches in a game or 80 for a week.
Additional In-House Notes
If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch.
Rest Examples:
if 20 or less on Monday, available on Tuesday
if 21-40 on Monday, available on Wednesday
if 41-60 on Monday, available on Thursday
if 61 or more on Monday, available on Friday
