Complicated Query Help Please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated Query Help Please

Author

Topic

safa
Starting Member

2 Posts

Posted - 2009-11-10 : 04:55:42

Hi

Im having some difficulty getting percentages of results that happened after certain results sports:

% Of Losses, Draws and wins for teams that beat TeamA on SeasonA a week before

My Sports result table:]

ID | League | Season | Week | HTeam | ATeam | Hscore | AScore

Getting the teams and the week that they Beat teamA at Season A is easy enough

I Now need to get their results the next week into %Won % Draws %Wins

Suppose I also need the best way this could be done ie. SProcs, Views etc..

Cheers

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 05:03:17

Give sample data and wanted output please.

No, you're never too old to Yak'n'Roll if you're too young to die.

safa
Starting Member

2 Posts

Posted - 2009-11-10 : 08:10:13

Hi Webfred

Here's a few lines from Sports_Results table

ID LeagueID Season [Week] Date HTID ATID HScore AScore
------------------------------------------------------------------------------------
1 1 1994 1 1993-08-14 1 10 0 3
2 1 1994 1 1993-08-14 2 29 4 1
3 1 1994 1 1993-08-14 9 5 1 2
4 1 1994 1 1993-08-14 19 32 2 0
5 1 1994 1 1993-08-14 20 17 1 1
6 1 1994 1 1993-08-14 23 37 0 1
7 1 1994 1 1993-08-14 27 16 0 3
8 1 1994 1 1993-08-14 31 36 3 1
9 1 1994 1 1993-08-14 33 13 0 2
10 1 1994 1 1993-08-14 40 42 0 2
11 1 1994 1 1993-08-15 25 21 0 2

I run this query to get all the teams that beat team_21 at season_2002

Set @Champ=21
Set @SSN=2002

Select Count(*) As Times, [Week], (Case When HTID=@Champ Then ATID When ATID=@Champ then HTID END) As CID From Sports_Results
Where
((HTID=@Champ and HScore>AScore) Or (ATID=@Champ and HScore<AScore)) And Season=@SSN And [Week]<38
Group By HTID, ATID, [Week]
Order By [Week]

Result:
Times Week CID
-------------------
1 5 24
1 10 6
1 12 19
1 14 1
1 15 9
1 16 40
1 31 22
1 33 19
1 35 1

I got 9 teams that beat team_21 in season_2002

I need to know the next week's results for those teams that beat team_21

so I need Team_24 at week 6, Team_6 at week_11, Team_19 at week_13, etc..

Take the number of teams that won, drew and lost (After beating team_21) and divide each result by 9

9/Won, 9/drew, 9/Lost

So the result needs to be: Of all the teams that beat team_21 at season_2002 %won, %drew and %Lost their next game ..

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources