Professional Web Applications Themes

SQL / Stored proc help - Microsoft SQL / MS SQL Server

Hello, I'm hoping someone can help with this. I'm looking for a sql statement to get me my results. If somehow I can use a stored proc, that'd be cool too, but I know nothing about them (I am doing this to display on the web with ASP). I have a games table. table: GAMES game_id game_ot (whether or not the game went into overtime) away_team_id away_team_score home_team_id home_team_score What I need to generate: games played (games_played) games won (games_won) games lost (games_lost) (not in overtime) games tied (games_tied) games lost in overtime (games_lost_ot) I do plan on throwing in ...

  1. #1

    Default SQL / Stored proc help

    Hello,

    I'm hoping someone can help with this. I'm looking for a sql statement to
    get me my results. If somehow I can use a stored proc, that'd be cool too,
    but I know nothing about them (I am doing this to display on the web with
    ASP).

    I have a games table.

    table: GAMES
    game_id
    game_ot (whether or not the game went into overtime)
    away_team_id
    away_team_score
    home_team_id
    home_team_score

    What I need to generate:
    games played (games_played)
    games won (games_won)
    games lost (games_lost) (not in overtime)
    games tied (games_tied)
    games lost in overtime (games_lost_ot)

    I do plan on throwing in a season_id and then generating this by season, but
    I can throw the "WHERE" part in later on I'm sure.

    If I can provide more info please let me know.

    Thank you.
    vf


    vf Guest

  2. #2

    Default Re: SQL / Stored proc help


    "vf" <com> wrote in message
    news:%phx.gbl... 
    too, 

    The main difficulty here is how a team may appear in the home_team_id column
    or the away_team_id column.

    For this query it will simplify things to create a view which is a simple
    list of all a team's games, with the outcomes.

    create view team_games
    as
    select
    home_team_id team_id,
    game_id,
    game_ot,
    case when away_team_score < home_team_score then 1 else 0 end as win,
    case when home_team_score < away_team_score then 1 else 0 end as loss,
    case when away_team_score = home_team_score then 1 else 0 end as tie,
    union all
    select
    away_team_id team_id,
    game_id,
    game_ot,
    case when home_team_score < away_team_score then 1 else 0 end as win,
    case when away_team_score < home_team_score then 1 else 0 end as loss,
    case when away_team_score = home_team_score then 1 else 0 end as tie,

    then, simply

    select
    count(*) games_played,
    sum(win) games_won,
    sum(case when loss = 1 and game_ot = 0 then 1 else 0 end) games_lost,
    sum(tie) games_tied,
    sum(case when loss = 1 and game_ot = 1 then 1 else 0 end) games_lost_ot
    from team_games


    David


    David Guest

  3. #3

    Default Re: SQL / Stored proc help

    (oops)

    sb

    create view team_games
    as
    select
    home_team_id team_id,
    game_id,
    game_ot,
    case when away_team_score < home_team_score then 1 else 0 end as win,
    case when home_team_score < away_team_score then 1 else 0 end as loss,
    case when away_team_score = home_team_score then 1 else 0 end as tie
    union all
    select
    away_team_id team_id,
    game_id,
    game_ot,
    case when home_team_score < away_team_score then 1 else 0 end as win,
    case when away_team_score < home_team_score then 1 else 0 end as loss,
    case when away_team_score = home_team_score then 1 else 0 end as tie


    david


    David Guest

Similar Threads

  1. stored proc prob
    By DJ in forum Coldfusion Database Access
    Replies: 3
    Last Post: December 11th, 02:41 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139