Need to increase performance - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need to increase performance

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-03-01 : 06:18:24

Hi,

In my Sp I have 22 dynamic queries, it takes 6 secs to execute.
I need to increase performance of that SP.
How to achieve it.

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

asgast
Posting Yak Master

149 Posts

Posted - 2010-03-01 : 06:48:58

By using newer and faster hardware.
Or you can give an example of your code, before so we would know what ou are talking about, but 1st option helps 100%.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-03-01 : 06:53:42

This is my SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author : M. KANDASAMY
-- Create date : 07-10-2009
-- Description : CLUB WISE DEMO DATA
-- =============================================
-- Sp_TeamWiseStats 1,'107,111,115,122,128,133,135,140,143,150,153,157,160,164'
ALTER PROCEDURE [dbo].[Sp_TeamWiseStats](@ClubID INT,@MatchID varchar(MAX))
AS
BEGIN
DECLARE @MatchesID as Varchar(MAX)
DECLARE @CompetitionId as INT
SET @MatchesID=''

select @MatchesID=@MatchesID+','+cast(Sc.Matchid as varchar(50)) from Scorecard Sc
Inner join Matches Ma on Sc.Matchid=Ma.id
Inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.Id
where Ma.Competitionid=@CompetitionId and Inn.BattingClubid=@ClubID
group by Sc.Matchid

set @MatchesID=substring(@MatchesID,2,len(@MatchesID))

-- BATSMEN WISE STATUS
-- 0
--SELECT 'Batting Averages'

EXEC('
select Playername Player,Max(TotMatch) Mat,count(Distinct Matchid) Inns,count(Distinct Matchid)- (sum(Dismissal)+Max(runout)) No,sum(runs)Runs,max(HightRuns) HS,case when sum(Dismissal)<>0 then cast(cast(sum(runs)as numeric(8,2))/(sum(Dismissal)+Max(runout)) as numeric(8,2))else 0 end Ave,sum(Balls) BF,cast(cast(sum(runs)as numeric(8,2))/sum(Balls)*100 as numeric(8,2)) SR,isnull(Max(Noof100s),''-'') [100],isnull(Max(Noof50s),0) [50],isnull(Max(Noof0s),''-'') [0],sum(fours) [4s],sum(sixes) [6s] from --,sum(DotBalls) DotBalls,Max(runout) runout
(
select Sc.Matchid,Sc.Inningsid,Sc.Strikerid, dbo.Fn_PlayerName(Sc.Strikerid) Playername,runs,
Case when Extratypeid Not in(2,4) then 1 else 0 end Balls
,Case When Runs=4 then 1 else 0 end Fours
,Case when runs=6 then 1 else 0 end Sixes
,Case When Dismissaltypeid not in(7,4) then 1 else 0 end Dismissal
,X.TotMatch,A.HightRuns
,Case when runs=0 and Extras=0 then 1 else 0 end DotBalls
,Noof100s,Noof0s,Noof50s
,isnull(Runout,0) Runout
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid
inner join (select Playerid,count(Matchid) TotMatch from(select * from Teams where Matchid in('+@MatchID+') and clubid='+@ClubID+')A group by Playerid) X on X.Playerid=Sc.Strikerid
inner join (select Strikerid,Max(runs) HightRuns from (select strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,Sc.inningsid,strikerid)A Group by Strikerid)A on A.Strikerid=Sc.Strikerid
left outer join (select Strikerid,count(*)Noof0s from (select strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,Sc.inningsid,strikerid having sum(runs)=0)A Group by Strikerid)C on C.Strikerid=Sc.Strikerid
left outer join (select Strikerid,count(*)Noof50s from (select strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,Sc.inningsid,strikerid having sum(runs)>=50)A Group by Strikerid)D on D.Strikerid=Sc.Strikerid
left outer join (select runoutid,count(*)Runout from Scorecard Sc inner join Matches Ma on Sc.Matchid=Ma.id inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id where Competitionid=9 and Dismissaltypeid=4 and Inn.BattingClubid='+@ClubID+' group by Runoutid)E on Sc.Strikerid=E.Runoutid
left outer join (select Strikerid,count(*)Noof100s from (select strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,Sc.inningsid,strikerid having sum(runs)>=100)A Group by Strikerid)F on F.Strikerid=Sc.Strikerid
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A Group by StrikerID,Playername
')

-- OPEENER/TOP/MID/LOWER ORDER
-- 1
--select 'Performance by Batting order'

EXEC('
select CASE WHEN TML=1 Then ''Openers''
WHEN TML=2 Then ''Top Order''
WHEN TML=3 Then ''Middle''
WHEN TML=4 Then ''Lower'' ELSE ''''End ''Batting Order''
,count(distinct Matchid)Mat,count(distinct Matchid)Inns,sum(runs) Runs,sum(Balls) Balls,sum(wkt) Dismissal,cast(cast(sum(runs)as numeric(8,2))/sum(Balls)*100 as numeric(8,2))SR, cast(cast(sum(runs)as numeric(8,2))/count(distinct Matchid)as numeric(8,2)) Ave
,CASE WHEN SUM(WKT)<>0 then cast(cast(sum(runs) as numeric(8,2))/sum(wkt) as numeric(8,2)) else 0 end ''Ave Runs/Wkt''
,cast(cast(sum(runs)as numeric(8,2))/sum(Balls)*6 as numeric(8,2))RR from
(
select Sc.Matchid,Sc.Inningsid,runs,Case when Extratypeid not in(2,4) then 1 else 0 end Balls,Bat.BattigOrder
,case when Bat.BattigOrder in(1,2) then 1
when Bat.BattigOrder in(3,4) then 2
when Bat.BattigOrder in(5,6,7) then 3
when Bat.BattigOrder in (8,9,10,11) then 4 else 0 end TML
,case when dismissaltypeid <>7 then 1 else 0 end wkt
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by TML ORDER BY TML
')

-- BATTING ORDER WISE STATUS
-- 2
--select 'Performance of batting position'
EXEC('
DECLARE @TempTable Table(ID INT,PlayerName VARCHAR(MAX))
DECLARE @ID INT
DECLARE @PName VARCHAR(MAX)
SET @ID=1
SET @PName=''''
While @ID <=11
BEGIN
select @PName= case when @PName<>'''' then @PName+'',''else '''' end+Playername from
(
select Bat.BattigOrder,left(Pl.Firstname,1)+''''+Pl.lastname Playername from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid
inner join Player pl on Sc.Strikerid=pl.id
Where Sc.matchid in('+@MatchID+')and Inn.BattingClubid='+@ClubID+' and Bat.BattigOrder=@ID
)A group by BattigOrder,Playername
Insert into @TempTable values(@ID,@PName)
SET @ID=@ID+1
SET @PName=''''
END
--select * from @TempTable

select Playername ''Player(s)'',BattigOrder Bat#,NoofMatch Mat,NoofMatch Inns,Nout No,Runs,Balls BF,HS,case when (NoofMatch-Nout)<>0 then cast(cast(runs as numeric(10,2))/(NoofMatch-Nout)as numeric(10,2)) else 0 end ''Ave'', SR,isnull(Noof100s,0) [100],isnull(Noof100s,0)[50],isnull(Less10s,0)[<10],Fours[4s],Sixes[6s] from
(
select BattigOrder,count(Distinct Matchid) NoofMatch,count(Distinct Matchid) NoofInn,count(Distinct Matchid)-sum(wkt)Nout,sum(runs)Runs,sum(Balls) Balls,cast(cast(sum(runs)as numeric(8,2))/sum(Balls)*100 as numeric(8,2)) SR,sum(fours) Fours,sum(sixes) Sixes,Max(Noof50s)Noof50s,Max(Noof100s)Noof100s,Max(Less10s) Less10s,Max(HS) HS from
(
select Sc.Matchid,Sc.Inningsid,runs,
Case when Extratypeid Not in(2,4) then 1 else 0 end Balls
,Case When Runs=4 then 1 else 0 end Fours
,Case when runs=6 then 1 else 0 end Sixes
,Bat.BattigOrder
,case when Dismissaltypeid <>7 then 1 else 0 end Wkt
,Noof50s,Noof100s,Less10s,HS
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid
inner join Player Pl on Sc.Strikerid=Pl.id
left outer join (select BattigOrder,count(*)Noof50s from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,strikerid,BattigOrder having sum(runs)>=50)A Group by BattigOrder)C on C.BattIgOrder=Bat.BattigOrder
left outer join (select BattigOrder,count(*)Noof100s from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,strikerid,BattigOrder having sum(runs)>=100)A Group by BattigOrder)D on D.BattIgOrder=Bat.BattigOrder
left outer join (select BattigOrder,count(*)Less10s from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,strikerid,BattigOrder having sum(runs)<=10)A Group by BattigOrder)E on E.BattIgOrder=Bat.BattigOrder
inner join (select BattigOrder,Max(runs)HS from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,Sc.inningsid,strikerid,BattigOrder)A Group by BattigOrder)F on F.BattigOrder=Bat.BattigOrder
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by BattigOrder
)B
left outer join
(
select * from @TempTable
)C on B.BattigOrder=C.ID')

--EXEC('SP_GetPartnershipRuns_Team '+@ClubID+','''+@MatchID+'''')

-- CLUB WISE PP ME EP1, EP2
-- 3
--select 'Batting performance during Powerplay, Mid and End Overs'
EXEC('
select Case When B.PME=1 then ''Powerplay''
When B.PME=2 then ''Mid Overs''
When B.PME=3 then ''End Overs 1''
When B.PME=4 then ''End Overs 2'' ELSE '''' END ''Phase of Play''
,NoofMatch Mat,NoofMatch Inns,Runs,Balls,Wickets Dismissals,Avger ''Batting Avg'',Case when Wickets<>0 then Runs/Wickets else 0 end ''Runs/Wkt'', RPO,isnull(Noof50s,0)[50s] from
(
select Pme,count(Distinct Matchid) NoofMatch,sum(runs) Runs,sum(Balls) Balls,sum(wkt)Wickets,cast(cast(sum(runs)as numeric(8,2))/count(Distinct Matchid)as numeric(8,2))Avger,Cast(Cast(sum(runs)as numeric(8,2))/sum(Balls)*6 as numeric(8,2))RPO from
(
select Sc.Matchid,runs+extras Runs
,Case When Extratypeid not in(1,2) then 1 else 0 end balls
,Case when Dismissaltypeid <>7 then 1 else 0 end Wkt
,Case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by PME
)B
left outer join
(
select Pme,count(*)Noof50s from(select Pme,count(Distinct Matchid)Matches,sum(runs)Hs from (select Sc.Matchid,Inningsid,runs+extras runs,Case when overid >=1 and overid <=6 then 1 when overid >=7 and overid <=14 then 2 when overid >=15 and overid <=17 then 3 when overid >=18 and overid <=20 then 4 else 0 end PME from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+')A Group by Matchid,Inningsid,Pme Having sum(runs) >=50 )B group by Pme
)C on c.pme=B.pme
')

-- BOWLER STATUS
-- 4
--select 'Bowling Performance'
EXEC('
select BowlerName ''Player(s)'',Max(TotMatch)Mat,count(Distinct Matchid)Inns,cast(sum(balls)/6 as varchar(10))+''.''+cast(sum(balls)-sum(Balls)/6*6 as varchar(10)) Overs,sum(Balls) Balls,sum(runs) Runs,sum(wkt)Wkt,Case When sum(wkt) <>0 then cast(cast(sum(runs)as numeric(5,2))/sum(wkt)as numeric(5,2)) else 0 end Ave
,cast(cast(sum(runs)as numeric(5,2))/sum(Balls)*6 as numeric(5,2)) Econ
,Case When sum(wkt) <>0 then cast(cast(sum(balls)as numeric(5,2))/sum(wkt)as numeric(5,2)) else 0 end SR
,isnull(Max(wkt4s),0)[4],sum(Caught) Ct,sum(stumps) St from
(
select Sc.Bowlerid,Sc.Matchid,dbo.Fn_PlayerName(Sc.Bowlerid) BowlerName
,Case when Extratypeid not in(4,3) then runs+extras else 0 end runs
,case when extratypeid not in(1,2) then 1 else 0 end balls
,case when dismissaltypeid not in(7,4) then 1 else 0 end Wkt
,case when Dismissaltypeid in(2) then 1 else 0 end Caught
,case when Dismissaltypeid in(5) then 1 else 0 end Stumps
,X.TotMatch,A.wkt4s
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join (select Playerid,count(Matchid) TotMatch from(select * from Teams where Matchid in('+@MatchID+') and clubid='+@ClubID+')A group by Playerid) X on X.Playerid=Sc.Bowlerid
left outer join (select Bowlerid,count(*)wkt4s from(select Bowlerid,sum(wkt)wkt4s from(select Sc.Matchid,inningsid,Bowlerid,Case when Dismissaltypeid not in(7,4) then 1 else 0 end Wkt from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid <>'+@ClubID+' )A group by Matchid,Inningsid,Bowlerid having sum(wkt) >=4)C group by Bowlerid) A on A.Bowlerid=Sc.Bowlerid
where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid <>'+@ClubID+'
)A group by Bowlerid,BowlerName')

-- BOLWER STATUS PP ME EP1 EP2 WISE
-- 5
--select 'Bowling performance during Powerplay, Mid and End Overs'
EXEC('
select
Case When PME=1 then ''Powerplay''
When PME=2 then ''Mid Overs''
When PME=3 then ''End Overs 1''
When PME=4 then ''End Overs 2'' ELSE '''' END ''Phase of Play''
,count(Distinct Matchid) Mat,count(Distinct Matchid) Inns,sum(runs) Runs,sum(Balls) Balls,sum(wkt)Wkts
,CASE WHEN sum(wkt)<>0 THEN cast(cast(sum(runs)as numeric(8,2))/sum(wkt) as numeric(8,2)) ELSE 0 END ''Bowl Avge''
,CASE WHEN sum(wkt)<>0 THEN cast(cast(sum(Balls)as numeric(8,2))/sum(wkt) as numeric(8,2)) ELSE 0 END SR
,Cast(Cast(sum(runs)as numeric(8,2))/sum(Balls)*6 as numeric(8,2))''Econ Rate'' from
(
select Sc.Matchid,runs+extras Runs
,Case When Extratypeid not in(1,2) then 1 else 0 end balls
,Case when Dismissaltypeid <>7 then 1 else 0 end Wkt
,Case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid<>'+@ClubID+'
)A group by PME order by PME
')

-- BOWLING STYLE WISE STATUS
--6
EXEC('
select B.Prefix ''Bowling Style'',Mat,C.Inn,B.Overs,B.Runs,B.Balls,B.wkt,B.Ave,B.Econ,B.SR,isnull(D.[4],0) [4],B.Caught,B.Stumps from
(
select Prefix,sum(runs) Runs,count(Distinct Matchid)Mat,cast(sum(balls)/6 as varchar(10))+''.''+cast(sum(balls)-sum(Balls)/6*6 as varchar(10)) Overs,sum(Balls) Balls,sum(wkt)Wkt
,Case When sum(wkt) <>0 then cast(cast(sum(runs)as numeric(8,2))/sum(wkt)as numeric(8,2)) else 0 end Ave
,cast(cast(sum(runs)as numeric(8,2))/sum(Balls)*6 as numeric(8,2)) Econ
,Case When sum(wkt) <>0 then cast(cast(sum(balls)as numeric(8,2))/sum(wkt)as numeric(8,2)) else 0 end SR
,sum(Caught) Caught,sum(stumps) stumps
from
(
select Sc.Matchid,Case when Extratypeid not in(4,3) then runs+extras else 0 end runs
,case when extratypeid not in(1,2) then 1 else 0 end balls
,case when dismissaltypeid not in(7,4) then 1 else 0 end Wkt
,case when Dismissaltypeid in(2) then 1 else 0 end Caught
,case when Dismissaltypeid in(5) then 1 else 0 end Stumps
,Prefix
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Player pl on Sc.Bowlerid=Pl.id
inner join Bowlingtype Bowl on Bowl.Bowlingtypeid=Pl.BowlingtypeId
Where Ma.Id in('+@MatchID+')
)A group by Prefix
)B Left outer join
(
Select Prefix,count(*)Inn from
(
select Matchid,Inningsid,Prefix from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Player pl on Sc.Bowlerid=Pl.id
inner join Bowlingtype Bowl on Bowl.Bowlingtypeid=Pl.BowlingtypeId
Where Ma.ID in('+@MatchID+') group by Matchid,Inningsid,Prefix
)A group by prefix
)C on B.Prefix=C.Prefix
left outer join
(
select Prefix,count(*)[4] from
(
select Prefix,Matchid,Inningsid,sum(runs) Runs,Sum(wkt) Wickets from
(
select Matchid,Inningsid,Case when Extratypeid not in(4,3) then runs+extras else 0 end runs
,case when extratypeid not in(1,2) then 1 else 0 end balls
,case when dismissaltypeid not in(7,4) then 1 else 0 end Wkt
,case when Dismissaltypeid in(2) then 1 else 0 end Caught
,case when Dismissaltypeid in(5) then 1 else 0 end Stumps
,Prefix
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Player pl on Sc.Bowlerid=Pl.id
inner join Bowlingtype Bowl on Bowl.Bowlingtypeid=Pl.BowlingtypeId
Where Ma.id in('+@MatchID+')
)A group by Matchid,Inningsid,Prefix having sum(wkt)>=4 --Order by prefix,sum(wkt) Desc,sum(runs)
)B Group by Prefix
)D on C.Prefix=D.Prefix
')

--Select ''
--7
EXEC(' --,sum(Dismissal) Wkt
select Playername Name,BattigOrder Bat#,Max(TotMatch) Mat,count(Distinct Matchid)Inns,count(Distinct Matchid)-sum(Dismissal)No,sum(runs)Runs,sum(Balls)Balls,Max(HS)Hs,cast(cast(sum(runs)as numeric(5,2))/sum(Balls)*100 as numeric(5,2)) SR ,isnull(max(Noof100s),0) [100],isnull(max(Noof50s),0) [50],isnull(Max(Noof0s),0)[<10],sum(fours) [4s],sum(sixes) [6s] from
(
select Sc.Matchid,Sc.Inningsid,Sc.Strikerid,dbo.Fn_PlayerName(Sc.Strikerid) Playername,runs,
Case when Extratypeid Not in(2,4) then 1 else 0 end Balls
,Case When Runs=4 then 1 else 0 end Fours
,Case when runs=6 then 1 else 0 end Sixes
,Case When Dismissaltypeid not in(7,4) then 1
when Dismissaltypeid =4 and Sc.strikerid=runoutid then 1 else 0 end Dismissal
,Bat.BattigOrder,HS,Noof0s,Noof50s,Noof100s,TotMatch
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid
inner join (select Playerid,count(Matchid) TotMatch from(select * from Teams where Matchid in('+@MatchID+') and clubid='+@ClubID+')A group by Playerid) X on X.Playerid=Sc.Strikerid
inner join (select BattigOrder,Strikerid,Max(runs) HS from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.playerid=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,Sc.inningsid,BattigOrder,strikerid)A Group by BattigOrder,Strikerid)A on A.BattIgOrder=Bat.BattigOrder and A.Strikerid=Sc.Strikerid
left outer join (select BattigOrder,Strikerid,count(*)Noof0s from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,strikerid,BattigOrder having sum(runs)<10)A Group by BattigOrder,Strikerid)B on B.BattIgOrder=Bat.BattigOrder and B.Strikerid=Sc.Strikerid
left outer join (select BattigOrder,Strikerid,count(*)Noof50s from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,strikerid,BattigOrder having sum(runs)>=50)A Group by BattigOrder,Strikerid)C on C.BattIgOrder=Bat.BattigOrder and C.Strikerid=Sc.Strikerid
left outer join (select BattigOrder,Strikerid,count(*)Noof100s from (select BattigOrder,strikerid,sum(Runs) runs from Scorecard Sc inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id inner join BattingOrder Bat on Sc.Matchid=Bat.Matchid and Sc.Inningsid=Bat.Inningsid and Bat.PlayerId=Sc.Strikerid where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+' group by Sc.Matchid,strikerid,BattigOrder having sum(runs)>=100)A Group by BattigOrder,Strikerid)D on D.BattIgOrder=Bat.BattigOrder and D.Strikerid=Sc.Strikerid
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A Group by BattigOrder,StrikerID,Playername
')

-- MATCHES WISE BATTING BOWLING DOT BALLS STATUS
--select 'Dot Ball both Batting & Bowling'
-- 8
EXEC('
select B.IPLID,B.Matches,B.Balls,B.Dots,B.[Dot Balls %],C.Balls,C.Dots,C.[Dot Balls %] from
(
select IPLID,ID,Matches,sum(Balls) Balls,sum(Dots) Dots ,cast(cast(sum(Dots) as numeric(8,2))/sum(Balls)*100 as numeric(8,2))''Dot Balls %'' from
(
select Ma.IPLID,Ma.ID,Ca.Prefix+'' Vs ''+Cb.Prefix+'' ''+convert(varchar(50),Ma.date,103) Matches,Sc.Matchid,Inningsid,case when (runs=0 and extras=0) then 1 else 0 end Dots
,case when Extratypeid not in(1,2,5,7) then 1 else 0 end Balls
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Clubs Ca on Ma.ClubA=Ca.ID
inner join Clubs Cb on Ma.ClubB=Cb.ID
Inner join innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
where Ma.competitionid=9 AND Sc.Matchid in('+@MatchID+') and inn.BattingClubid='+@ClubID+'
)A Group by IPLID,ID,Matches,Matchid,Inningsid
)B
LEFT OUTER JOIN
(
select IPLID,ID,Matches,sum(Balls) Balls,sum(Dots) Dots ,cast(cast(sum(Dots) as numeric(8,2))/sum(Balls)*100 as numeric(8,2))''Dot Balls %'' from
(
select Ma.IPLID,Ma.ID,Ca.Prefix+'' Vs''+Cb.Prefix+'' ''+convert(varchar(50),Ma.date,103) Matches,Sc.Matchid,Inningsid,case when (runs=0 and extras=0) then 1 else 0 end Dots
,case when Extratypeid not in(1,2,5,7) then 1 else 0 end Balls
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Clubs Ca on Ma.ClubA=Ca.ID
inner join Clubs Cb on Ma.ClubB=Cb.ID
Inner join innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
where Ma.competitionid=9 AND Sc.Matchid in('+@MatchID+') and inn.BattingClubid<>'+@ClubID+'
)A Group by IPLID,ID,Matches,Matchid,Inningsid
)C on B.ID=C.ID
')

--select 'Yorker and Slow ball'
--9
EXEC('
select dbo.Fn_BowlingStyle(BowlingTypeid),count(Ballid) ''Balls bowled'',sum(runs) ''Runs conceded'',sum(Dots)[0s],sum(ones) [1s],sum(Twos) [2s],sum(Three) [3s],sum(Fours) [4s],sum(Sixes) [6s],sum(wkt) wkt,sum(Extras)Extras from
(
select Bowl.BowlingTypeid,Sc.Matchid,Sc.Inningsid,Sc.Ballid,runs,Extras
,Case when runs=0 and Extras=0 then 1 else 0 end Dots
,Case when runs=1 then 1 else 0 end Ones
,Case when runs=2 then 1 else 0 end Twos
,Case when runs=3 then 1 else 0 end Three
,Case when runs=4 then 1 else 0 end Fours
,Case when runs=6 then 1 else 0 end Sixes
,Case when Dismissaltypeid <>7 then 1 else 0 end Wkt
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join (select Inn.Matchid,ID,Inn.BattingClubid,BowlClubid from innings Inn inner join (select Matchid, 3-id InnID,BattingClubid BowlClubid from Innings)A on A.Matchid=Inn.Matchid and A.InnId=Inn.id)A on Sc.Matchid=A.Matchid and Sc.Inningsid=A.id
inner join Bowling Bowl on Sc.Matchid=Bowl.Matchid and Sc.Inningsid=Bowl.Inningsid and Sc.Ballid=Bowl.Ballid
Where BowlClubid='+@ClubID+' and Sc.Matchid in('+@MatchID+') and Bowl.BowlingTypeid in(5,9)
)A Group by BowlingTypeid
')

--select 'Wickets Falling with Dot Balls'
-- 10
EXEC('
Declare @PreBallsCount Table(ID Int Identity(1,1),MatchID Int,InningsID Int,BatOrder int,OverID Int,BallID Int,
StrikerID Int,RunOutID Int,Matches Varchar(255),PlayerName Varchar(255),Description Varchar(255),
Sruns Int,TotalRuns varchar(255),PreBallCnt Int,CurrScore varchar(255))

Insert Into @PreBallsCount
(MatchID,InningsID,BatOrder,OverID,BallID,StrikerID,RunOutID,Matches,PlayerName,Description,Sruns,TotalRuns)

select Sc.Matchid,Sc.Inningsid,BatOrder.BattigOrder,SC.OverID,Sc.Ballid,Sc.Strikerid,runoutid,Ca.prefix+'' Vs ''+Cb.prefix+'' ''+convert(varchar(50),Ma.date,103) Matches,left(pl.firstname,1)+'' ''+pl.lastname,Dis.Description
,Sruns,Totalruns
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Clubs Ca on Ma.ClubA=ca.id
inner join Clubs Cb on Ma.ClubB=cb.id
inner join Player pl on Sc.Strikerid=pl.id
inner join Dismissaltype Dis on Sc.Dismissaltypeid=Dis.id
inner join BattingOrder BatOrder on Sc.Matchid=BatOrder.Matchid and Sc.Inningsid=BatOrder.Inningsid and Sc.Strikerid=BatOrder.Playerid
inner join (select Matchid,Inningsid,Strikerid,sum(runs) Sruns from Scorecard Sc Where Sc.Matchid in('+@MatchID+') group by Matchid,Inningsid,Strikerid)A on Sc.Matchid=A.Matchid and Sc.Inningsid=A.Inningsid and Sc.Strikerid=A.Strikerid
--inner join (select Matchid,Inningsid,sum(runs)+sum(Extras) Totalruns from Scorecard Sc Where Sc.Matchid in('+@MatchID+') group by Matchid,Inningsid)B on Sc.Matchid=B.Matchid and Sc.Inningsid=B.Inningsid
Inner join (select Matchid,Inningsid,cast(sum(runs)as varchar(50))+''/''+cast(sum(wkt)as varchar(50))Totalruns from(select Matchid,Inningsid,runs+Extras runs,case when dismissaltypeid <>7 then 1 else 0 end Wkt from Scorecard Sc Where Sc.Matchid in('+@MatchID+'))A Group by Matchid,Inningsid)B on Sc.Matchid=B.Matchid and Sc.Inningsid=B.Inningsid
Where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid ='+@ClubID+' and dismissaltypeid <>7

Declare @Temp Int,@PreBallsCnt Int,@TempBallID Int
Declare @MatchID Int,@InningsID Int,@OverID Int,@BallID Int,@StrikerID Int,@RunOutID Int
Declare @CurrScore Varchar(255)
set @CurrScore=''''
Set @Temp = 1
Set @PreBallsCnt = 0
While @Temp < = (Select Max(ID) From @PreBallsCount)
Begin
Select @MatchID =MatchID ,@InningsID =InningsID ,@OverID = OverID,@BallID =BallID ,@StrikerID =StrikerID
,@RunOutID=RunOutID From @PreBallsCount Where ID = @Temp
Set @TempBallID = @BallID

While 1 = 1
Begin

Set @TempBallID = @TempBallID - 1
If Exists(Select 1 From ScoreCard Where MatchID = @MatchID And InningsID = @InningsID And
OverID = @OverID And Ballid = @TempBallID And StrikerID = @StrikerID)
Begin

Set @PreBallsCnt = @PreBallsCnt + 1
End
Else
Break
End

select @CurrScore=cast(sum(runs)as varchar(50))+''/''+cast(sum(wkt)as varchar(50)) from
(
select runs+extras runs,case when dismissaltypeid <>7 then 1 else 0 end wkt from Scorecard where matchid=@MatchID and Inningsid=@InningsID and ballid <=@BallID
)A

Update @PreBallsCount
Set PreBallCnt = @PreBallsCnt,CurrScore = @CurrScore
Where MatchID = @MatchID And InningsID = @InningsID And BallID = @BallID

Set @PreBallsCnt = 0
Set @Temp = @Temp + 1
End
Select Matches Match,PlayerName Batsmen,PreBallCnt ''No. of Dot balls faced before dismissal'',Description ''Mode of Dismissal'',Sruns ''Bastmen Score'',TotalRuns ''Team Score'',CurrScore,Inningsid ''Batting 1st/2nd'' from @PreBallsCount where PreBallCnt>0
')
-- BATSMEN RUNS WITH POWER PLAY
--SELECT 'Batsmen POWERPLAY Status'
-- 11
EXEC ('
select B.Player,Innings,B.Balls,runs,SR,Average,[No],HS from
(
select PME,Player,count(distinct Matchid) Innings,sum(runs) runs
,case when sum(runs)<>0 then cast(cast(sum(runs) as numeric(8,2))/sum(balls)*100 as numeric(8,2)) else 0 end SR
,case when sum(wkt)<>0 then cast(cast(sum(runs)as numeric(8,2))/sum(wkt)as numeric(8,2)) else 0 end Average
,count(distinct Matchid)-sum(wkt) [No]
,sum(Balls) balls,sum(wkt)wkt
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
,case when dismissaltypeid not in(7,4) then 1
when dismissaltypeid=4 and strikerid=runoutid then 1 else 0 end wkt
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.ID in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A where pme=1 group by Player,pme
)B
left outer join
(
select pme,Player,Max(runs)HS from
(
select Matchid,Pme,Player,sum(runs) runs
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.ID in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by Matchid,Player,pme
)B group by Player,pme
)C on B.Player=C.Player and B.pme=c.pme
')

-- BATSMEN RUNS WITH MID PLAY
--SELECT 'Batsmen MID PLAY Status'
-- 12
EXEC ('
select B.Player,Innings,B.Balls,runs,SR,Average,[No],HS from
(
select PME,Player,count(distinct Matchid) Innings,sum(runs) runs
,case when sum(runs)<>0 then cast(cast(sum(runs) as numeric(8,2))/sum(balls)*100 as numeric(8,2)) else 0 end SR
,case when sum(wkt)<>0 then cast(cast(sum(runs)as numeric(8,2))/sum(wkt)as numeric(8,2)) else 0 end Average
,count(distinct Matchid)-sum(wkt) [No]
,sum(Balls) balls,sum(wkt)wkt
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
,case when dismissaltypeid not in(7,4) then 1
when dismissaltypeid=4 and strikerid=runoutid then 1 else 0 end wkt
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.Id in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A where pme=2 group by Player,pme
)B
left outer join
(
select pme,Player,Max(runs)HS from
(
select Matchid,Pme,Player,sum(runs) runs
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.id in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by Matchid,Player,pme
)B group by Player,pme
)C on B.Player=C.Player and B.pme=c.pme
')

-- BATSMEN RUNS WITH END PLAY 1
--SELECT 'Batsmen END PLAY 1 Status'
-- 13
EXEC ('
select B.Player,Innings,B.Balls,runs,SR,Average,[No],HS from
(
select PME,Player,count(distinct Matchid) Innings,sum(runs) runs
,case when sum(runs)<>0 then cast(cast(sum(runs) as numeric(8,2))/sum(balls)*100 as numeric(8,2)) else 0 end SR
,case when sum(wkt)<>0 then cast(cast(sum(runs)as numeric(8,2))/sum(wkt)as numeric(8,2)) else 0 end Average
,count(distinct Matchid)-sum(wkt) [No]
,sum(Balls) balls,sum(wkt)wkt
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
,case when dismissaltypeid not in(7,4) then 1
when dismissaltypeid=4 and strikerid=runoutid then 1 else 0 end wkt
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.Id in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A where pme=3 group by Player,pme
)B
left outer join
(
select pme,Player,Max(runs)HS from
(
select Matchid,Pme,Player,sum(runs) runs
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.id in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by Matchid,Player,pme
)B group by Player,pme
)C on B.Player=C.Player and B.pme=c.pme
')

-- BATSMEN RUNS WITH END PLAY 2
--SELECT 'Batsmen END PLAY 2 Status'
-- 14
EXEC ('
select B.Player,Innings,B.Balls,runs,SR,Average,[No],HS from
(
select PME,Player,count(distinct Matchid) Innings,sum(runs) runs
,case when sum(runs)<>0 then cast(cast(sum(runs) as numeric(8,2))/sum(balls)*100 as numeric(8,2)) else 0 end SR
,case when sum(wkt)<>0 then cast(cast(sum(runs)as numeric(8,2))/sum(wkt)as numeric(8,2)) else 0 end Average
,count(distinct Matchid)-sum(wkt) [No]
,sum(Balls) balls,sum(wkt)wkt
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
,case when dismissaltypeid not in(7,4) then 1
when dismissaltypeid=4 and strikerid=runoutid then 1 else 0 end wkt
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.id in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A where pme=4 group by Player,pme
)B
left outer join
(
select pme,Player,Max(runs)HS from
(
select Matchid,Pme,Player,sum(runs) runs
from
(
select Sc.Matchid,Inningsid,runs
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
,left(Pl.firstname,1)+'' ''+Pl.lastname Player
,case when extratypeid not in(2,3)then 1 else 0 end Balls
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player Pl on Sc.Strikerid=Pl.id
where Ma.Id in('+@MatchID+') and Inn.BattingClubid='+@ClubID+'
)A group by Matchid,Player,pme
)B group by Player,pme
)C on B.Player=C.Player and B.pme=c.pme
')

--SELECT 'Bowler POWER PLAY Status'
-- 15
EXEC('
select BowlerName,sum(runs)runs,sum(Balls)Ball,sum(Wkt) wkt,cast(cast(sum(runs)as numeric(8,2))/sum(balls)*6 as numeric(5,2))Econ from
(
select Sc.Matchid,Inningsid,Bowlerid,left(pl.Firstname,1)+'' ''+pl.Lastname BowlerName
,Case When Extratypeid not in(1,2) then 1 else 0 end Balls
,case When Extratypeid not in(3,4) then runs+extras else 0 end runs
,Case When Dismissaltypeid not in(7,4) then 1 else 0 end wkt
,Case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player pl on Sc.Bowlerid=Pl.id
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid<>'+@ClubID+'
and overid >=1 and overid <=6
)A Group by Pme,Bowlerid,BowlerName Order by Bowlerid
')

--SELECT 'Bowler MID PLAY Status'
-- 16
EXEC('
select BowlerName,sum(runs)runs,sum(Balls)Ball,sum(Wkt) wkt,cast(cast(sum(runs)as numeric(8,2))/sum(balls)*6 as numeric(5,2))Econ from
(
select Sc.Matchid,Inningsid,Bowlerid,left(pl.Firstname,1)+'' ''+pl.Lastname BowlerName
,Case When Extratypeid not in(1,2) then 1 else 0 end Balls
,case When Extratypeid not in(3,4) then runs+extras else 0 end runs
,Case When Dismissaltypeid not in(7,4) then 1 else 0 end wkt
,Case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player pl on Sc.Bowlerid=Pl.id
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid<>'+@ClubID+'
and overid >=7 and overid <=14
)A Group by Pme,Bowlerid,BowlerName Order by Bowlerid
')

--SELECT 'Bowler END PLAY 1 Status'
-- 17
EXEC('
select BowlerName,sum(runs)runs,sum(Balls)Ball,sum(Wkt) wkt,cast(cast(sum(runs)as numeric(8,2))/sum(balls)*6 as numeric(5,2))Econ from
(
select Sc.Matchid,Inningsid,Bowlerid,left(pl.Firstname,1)+'' ''+pl.Lastname BowlerName
,Case When Extratypeid not in(1,2) then 1 else 0 end Balls
,case When Extratypeid not in(3,4) then runs+extras else 0 end runs
,Case When Dismissaltypeid not in(7,4) then 1 else 0 end wkt
,Case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player pl on Sc.Bowlerid=Pl.id
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid<>'+@ClubID+'
and overid >=15 and overid <=17
)A Group by Pme,Bowlerid,BowlerName Order by Bowlerid
')

--SELECT 'Bowler END PLAY2 Status'
-- 18
EXEC('
select BowlerName,sum(runs)runs,sum(Balls)Ball,sum(Wkt) wkt,cast(cast(sum(runs)as numeric(8,2))/sum(balls)*6 as numeric(5,2))Econ from
(
select Sc.Matchid,Inningsid,Bowlerid,left(pl.Firstname,1)+'' ''+pl.Lastname BowlerName
,Case When Extratypeid not in(1,2) then 1 else 0 end Balls
,case When Extratypeid not in(3,4) then runs+extras else 0 end runs
,Case When Dismissaltypeid not in(7,4) then 1 else 0 end wkt
,Case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player pl on Sc.Bowlerid=Pl.id
Where Sc.matchid in('+@MatchID+') and Inn.BattingClubid<>'+@ClubID+'
and overid >=18 and overid <=20
)A Group by Pme,Bowlerid,BowlerName Order by Bowlerid
')
-- BATTING DOT BALLS ANALYSIS
-- 19
EXEC('select Prefix,PME,sum(Dots) Dots,sum(Balls) Balls ,cast(cast(sum(Dots) as numeric(8,2))/sum(Balls)*100 as numeric(8,2))DotPerc from
(
select Cl.Prefix,case when (runs=0 and extras=0) then 1 else 0 end Dots
,case when Extratypeid not in(1,2) then 1 else 0 end Balls
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Inn.Matchid=Sc.Matchid and Inn.id=Sc.Inningsid
inner join Clubs Cl on Inn.BattingClubid=cl.id
where Ma.id IN('+@MatchID+')and Inn.BattingClubid='+@ClubID+'
)A Group by Prefix,PME Order by Prefix,PME
')
-- BOWLING DOT BALLS ANALYSIS
-- 20
EXEC('select Prefix,PME,sum(Dots) Dots,sum(Balls) Balls ,cast(cast(sum(Dots) as numeric(8,2))/sum(Balls)*100 as numeric(8,2))DotPerc from
(
select Cl.Prefix,case when (runs=0 and extras=0) then 1 else 0 end Dots
,case when Extratypeid not in(1,2) then 1 else 0 end Balls
,case when overid >=1 and overid <=6 then 1
when overid >=7 and overid <=14 then 2
when overid >=15 and overid <=17 then 3
when overid >=18 and overid <=20 then 4 else 0 end PME
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings Inn on Inn.Matchid=Sc.Matchid and Inn.id=Sc.Inningsid
inner join Clubs Cl on Inn.BattingClubid=cl.id
where Ma.id IN('+@MatchID+')and Inn.BattingClubid<>'+@ClubID+'
)A Group by Prefix,PME Order by Prefix,PME
')

-- RUNOUT BATTING
-- 21
EXEC('Select runoutid,Ca.prefix+'' Vs ''+Cb.prefix Matches,runs+1 RA,left(Pl.Firstname,1)+'' ''+Pl.Lastname Playername,A.sruns,dbo.TeamScorec(Sc.Matchid,Sc.Inningsid,Sc.Ballid) Totalruns,C.Score,Sc.Inningsid
from Scorecard Sc
inner join Matches Ma on Sc.Matchid=Ma.id
inner join Innings inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.id
inner join Player pl on Sc.Runoutid=pl.id
inner join Clubs Ca on Ma.ClubA=ca.id
inner join Clubs Cb on Ma.ClubB=cb.id
inner join (select Matchid,Inningsid,Strikerid,sum(runs) Sruns from Scorecard Sc Where Sc.Matchid in('+@MatchID+') group by Matchid,Inningsid,Strikerid)A on Sc.Matchid=A.Matchid and Sc.Inningsid=A.Inningsid and Sc.Strikerid=A.Strikerid
inner join (select Matchid,Inningsid,sum(runs)+sum(Extras) Totalruns from Scorecard Sc Where Sc.Matchid in('+@MatchID+') group by Matchid,Inningsid)B on Sc.Matchid=B.Matchid and Sc.Inningsid=B.Inningsid
inner join (select Matchid,Inningsid,cast(sum(runs)as varchar(100))+''/''+cast(sum(wkt) as varchar(50))Score from(select Sc.Matchid,Sc.Inningsid,runs+Extras runs,Case when dismissaltypeid <> 7 then 1 else 0 end wkt from Scorecard sC Inner join Innings Inn on Sc.Matchid=Inn.Matchid and Sc.Inningsid=Inn.ID where Sc.Matchid in('+@MatchID+') and Inn.BattingClubid='+@ClubID+')A Group by Matchid,Inningsid)C on Sc.Matchid=C.Matchid and Sc.Inningsid=C.Inningsid
Where Sc.Matchid in('+@MatchID+') and Dismissaltypeid =4
')

END

Solutions are easy. Understanding the problem, now, that's the hard part

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-01 : 09:55:26

break it down into 22 seperate queries.
benchmark each query and work through the lot starting with the longest running query.
make sure you have appropriate indices on each join (post some sample execution plans - you want to minimise/avoid "scans")

you may find it faster to create 1 overall procedure to call 22 seperate procedures, as each of the sub-procedures would/should each get a proper cached execution plan.

you may also need to downgrade your expectations....when doing a lot of work, doing expect things to happen in "no-time"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources