Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Question Need Help w/ Personell-Scheduling DB

    Hi,
    I am trying to develop personnel-scheduling database for my Anesthesia practice. This can be looked at as a baseball team, where each player is assigned to a different position each day. There are more players on the team and positions, so some players will either be off or on vacation each day. I'd like to be able to go back and tabulate how many times in the year each player has played each position, as well as how many times each player has pitched on the weekend. I'm having trouble conceptualizing the setup and relations for this. I have a table for Players and a table for Positions. Could somebody please give me some guidance on how to proceed from here? Thanks!

    Ron

  2. #2
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Table: Player_Position

    Player_ID (FK)
    Postion_ID (FK)
    Play_Date Date
    Weekend_YN char (1) Y or N


    To get "how many times in the year each player has played each position":

    select count(*), player_id, position_id
    from player_position
    where play_date between '1-Jan-2004' and '31-Dec-2004'
    group by player_id, position_id

    To get "how many times each player has pitched on the weekend":

    select count(*), player_id
    from player_position
    where weekend_yn = 'Y'
    and position_id = 'Pitching'
    group by player_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •