November 21, 2012 at 1:32 am
Hi all, Am getting the above error in my procedure.
this is my procedure
ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client]
( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int)
as
begin
select E.Member_FirstName as Player ,
Count(A.FixturePlayer_MemberKey) As Matches ,
SUM(D.Bowling_Overs) As Overs,
SUM(D.Bowling_Maidens) as Maidens,
SUM(D.Bowling_Runs) as Runs,
SUM(D.Bowling_Wickets) as Wickets,
isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Overs),0),0) as Economy,
isnull(SUM(D.Bowling_Runs)/nullif(SUM(D.Bowling_Wickets),0),0) as Average,
(select (cast((D.Bowling_Wickets) as varchar)+'-'+cast((D.Bowling_Runs) as varchar)) from InningsBowlingDetails D
where D.Bowling_Wickets in (select max(D.Bowling_Wickets) from InningsBowlingDetails D group by D.Bowling_MemberKey)
and D.Bowling_Runs in (select MIN(D.Bowling_Runs) from InningsBowlingDetails D group by D.Bowling_MemberKey)
group by D.Bowling_Wickets,D.Bowling_Runs,D.Bowling_MemberKey)as BestBowling
from tbl_FixturePlayers A,tbl_Fixtures B,FixtureInnings C,InningsBowlingDetails D,tbl_ClubMembers E
where B.Fixture_SeasonKey= CASE WHEN @Fixture_SeasonKey <>0 AND @Fixture_SeasonKey IS NOT NULL THEN @Fixture_SeasonKey ELSE B.Fixture_SeasonKey END
and E.Member_Key=D.Bowling_MemberKey
and B.Fixture_TeamKey= CASE WHEN @Fixture_TeamKey <>0 AND @Fixture_TeamKey IS NOT NULL THEN @Fixture_TeamKey ELSE B.Fixture_TeamKey END
and B.Fixture_Fixturetype= CASE WHEN @Fixture_FixtureType <>0 AND @Fixture_FixtureType IS NOT NULL THEN @Fixture_FixtureType ELSE B.Fixture_Fixturetype END
and D.Bowling_MemberKey=A.FixturePlayer_MemberKey
and A.FixturePlayer_FixtureKey=B.Fixture_Key
and C.Innings_FixtureKey=B.Fixture_Key
and D.Bowling_InningsKey=C.Innings_Key
Group By A.FixturePlayer_MemberKey,E.Member_FirstName ,D.Bowling_Wickets,D.Bowling_Runs,D.Bowling_MemberKey
end
November 21, 2012 at 1:38 am
i want to display this format
0-0
2-2
2-2
0-3
2-45
5-49
November 21, 2012 at 1:47 am
Hello and welcome to SSC,
If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.
Have a read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.
Thanks.
November 21, 2012 at 1:50 am
Can you post the DDL (ideally temp tables/table vars) for the tables and some anonomised sample data that will give the results as this will enable us to help you.
Being a bit of a cricket nut myself what is you are trying to do?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 21, 2012 at 2:02 am
If you could explain how you decide what are the best bowling figures for any one innings, this should be quite easy. For example, what order what you put these in, from best to worst?
0-0
2-2
8-8
0-3
2-45
5-49
1-0
1-90
John
November 21, 2012 at 2:14 am
John,
Generally the Best bowling figures use the number of wickets first, then runs conceded, eg 5-7 is considered worse than 6-100, but 6-50 would be better than both of them.
There are other stats that determing best bowling as well especially over a season such as Strike rate (balls/wicket), or Average (runs/wicket). It all depends on what you classify them as.
something like
Select i.Bowler, Max(w.wickets) wickets, Min(RunsPerWicket)
From Innings i
JOIN (Select Bowler, Max(Wickets) Wickets from Innings group by Bowler) w
ON w.Bowler=i.Bowler
AND w.Wickets=i.wickets
group by i.Bowler
Would probably give you the information on best bowling in an innings.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 21, 2012 at 2:31 am
Yes, once we get DDL and sample data for InningsBowlingDetails we should be able to rewrite that subquery quite easily.
John
November 21, 2012 at 2:38 am
Looking forward to our friends in the USA helping out with a cricket question 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply