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)
 Ways to optimize the SP

Author  Topic 

ganeshkumar08
Posting Yak Master

187 Posts
Posted - 2008-06-24 : 06:18:30
Hello,

Can any one suggest me in optimizing the SP.
To execute single SP it takes nearly 50 seconds.
Can i know what are ways to optimize the SP.

Thanks
Ganesh

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

bjoerns
Posting Yak Master

154 Posts
Posted - 2008-06-24 : 06:30:04
The Code would be really helpful.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-24 : 07:03:10
Noooo.... We are Experts. We should be able to read minds.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

ganeshkumar08
Posting Yak Master

187 Posts
Posted - 2008-06-24 : 08:56:30
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- Start of function to return event results as an attacking data set
--exec sp_GenerateAttackingOverview 0,0,-1,-1,-1,-1,-1,-1,1,-1,1,-1,''
ALTER PROCEDURE [dbo].[sp_GenerateAttackingOverview]
@SessionID int,
@extrapType int = -1,
@startTime int = -1,
@endTime int = -1,
@segmentid int = -1,
@greater90min int = -1,
@less90min int = -1,
@excludeGK int = -1,
@returnPositions int = -1,
@WithPoss int=-1,
@WithoutPoss int=-1,
@BOOP int=-1,
@SelectedFields varchar(1000) = ''
AS
BEGIN

declare @secondsInHalf as int
declare @startSegment as tinyint
declare @endSegment as tinyint
set @secondsInHalf = (45 * 60)

--copy of entered start time and end time to use in scout game time calculations
declare @initStartTime int
declare @initEndTime int
declare @initSegmentid int
set @initStartTime = @startTime
set @initEndTime = @endTime
set @initSegmentid = @segmentid

declare @max_seg_end_time numeric(8,3)

declare @startTimeCopy int
declare @endTimeCopy int
declare @segmentIDCopy int

set @startTimeCopy = @startTime
set @endTimeCopy = @endTime
set @segmentIDCopy = @segmentid

/*********** Trend 2.1 ***Start*** ***********/
Declare @PossessionType int
Set @PossessionType = -1
If @WithPoss = 1 -- Player / Club in Possession
Set @PossessionType = 1
Else If @WithoutPoss = 1 -- Player / Club not in Possession
Set @PossessionType = 2
Else If @BOOP = 1
Set @PossessionType = 3 -- Player / Club in Ball Out of play
Else If @WithPoss = -1 And @WithoutPoss = -1 And @BOOP = -1
Set @PossessionType = 4
/*********** Trend 2.1 ***End*** ***********/
-- For Possession

--Declaration of TotalTime table
declare @totaltime table(
matchid int,
segid int,
totalperiod numeric(7,2)
)

--Insert Values into TotalTime table
insert into @totaltime
select matchid,segmentid,max(time) from trendevents where matchid in
(select matchid from trendselected_matches where sessionid = @sessionid)
group by matchid,segmentid


if @segmentid = -1 --default case
begin

if @startTime = -1 and @endTime = -1
begin
set @startSegment = 0
set @endSegment = 1
set @startTime = 0
set @endTime = 3500

select @endTime = max(time) from TrendEvents
where MatchID in (select MatchID from TrendSelected_Matches where SessionID = @SessionID)

end
else
begin
if @startTime < @secondsInHalf
begin
set @startSegment = 0
end
else
begin
set @startSegment = 1
set @startTime = @startTime - @secondsInHalf
end

if @endTime <= @secondsInHalf
begin
set @endSegment = 0
end
else
begin
set @endSegment = 1
set @endTime = @endTime - @secondsInHalf
end
end
end
else --A segment has been specified, so set startSegment and endSegment to be the same.
--What to do with the time values?
begin
set @startSegment = @segmentid
set @endSegment = @segmentid

if @startTime = -1
begin
set @startTime = 0
end

set @max_seg_end_time = (select max(time) from TrendEvents
where MatchID in (select MatchID from TrendSelected_Matches
where SessionID = @SessionID)
and segmentid = @endSegment)

if @endTime = -1
begin
set @endTime = @max_seg_end_time

end
end


--Get the host id
declare @hostid int
set @hostid = @SessionID

--Declare results table. This is essentially what we return.
declare @results table (
matchid int,
playerid int,
clubid int,
positionid int,
matchDate datetime,
TimeOnPitch numeric(7,2),
AppearanceTime numeric(7,2),
TotalPeriod numeric(7,2),
totalPassCount int default 0,
totalShotCount int default 0,
onTargetShotCount int default 0,
goalCount int default 0,
ownGoalCount int default 0,
finalThirdEntryCount int default 0,
penAreaEntryCount int default 0,
headersCount int default 0,
dribbleCount int default 0,
crossesCount int default 0,
cornerCount int default 0,
offsideCount int default 0,
tackledCount int default 0,
possessionWonCount int default 0,
possessionLostCount int default 0,
fouledCount int default 0,
freekickCount int default 0,
totalTimeOnPitch numeric (7,2),
blockedShotCount int default 0,
CopyTimeOnPitch numeric(7,2), -- For Extrapolation
/****** Trend 2.1 Start *********/
Shotsinsidebox int default 0,
Shotsontargetinsidebox int default 0,
Shotsoutsidebox int default 0,
Shotsontargetoutsidebox int default 0,
CrossesfromLEFT int default 0,
CrossesfromRIGHT int default 0,
CornersfromLEFT int default 0,
CornersfromRIGHT int default 0
/****** Trend 2.1 Start *********/

primary key(matchid, playerid)
)

-- temp table to store events data per match
declare @tempEvents table (
matchid int,
playerid int,
segmentid int,
event int,
outcome int,
eventCount int,


primary key(matchid, playerid, segmentid, event, outcome)
)

--Declare a temporary table to store first pass of information
declare @time_from_db table (
matchid int,
playerid int,
segmentid int,
timeon numeric(8,3),
timeoff numeric(8,3),

primary key(matchid, playerid, segmentid)
)

--Insert temp times into table. This will be used to calculate the actual time for the period.
insert into @time_from_db
select matchid, playerid, segmentid,
case
when segmentid = @startsegment and timeon >= @starttime then
timeon
when segmentid > @startsegment then
timeon
else
@starttime
end,
case
when segmentid = @endsegment and timeoff <= @endtime then
timeoff
when segmentid < @endsegment then
timeoff
else
@endtime
end
from trendplayer_time tp
where matchid in (select matchid from TrendSelected_Matches where SessionID = @hostid)
and (segmentid >= @startsegment and segmentid <= @endsegment)

--Insert rows into @results for each matchid, playerid, timeOnPitch
--
-- insert into @results (matchid, playerid, timeOnPitch)
-- select matchid, playerid, sum(timeoff - timeon) from @time_from_db
-- group by matchid, playerid
-- order by matchid, playerid


-- For Extraploation
/********* Trend 2.1.1 **** Start ************/
if @extrapType=0 or @extrapType= 1
insert into @results (matchid, playerid, timeOnPitch,copytimeOnPitch)
select matchid, playerid, sum(timeoff - timeon),sum(timeoff - timeon) from @time_from_db
group by matchid, playerid
order by matchid, playerid

else -- Normal
insert into @results (matchid, playerid, timeOnPitch)
select matchid, playerid, sum(timeoff - timeon) from @time_from_db
group by matchid, playerid
order by matchid, playerid
/********* Trend 2.1.1 **** End *************/


--Set the complete time for the match, used to work out whether performance time is <90> mins
update @results
set AppearanceTime = v.tim
from
(select MatchID as m1, PlayerID as p1, sum(totalTime) as tim from TrendPlayer_Time
group by MatchID, Playerid)v
where matchid = v.m1
and playerid = v.p1




--select count of all events for players
--This will derive the results for shots/passes/crosses/headers/freekicks/dribbles/clearances
if(@startSegment = @endSegment)
begin

insert into @tempEvents
select matchid, player1, segmentid, event, outcome, count(event) as eventCount from TrendEvents
where matchid in (select MatchID from TrendSelected_Matches where SessionID = @hostid)--(select distinct MatchID from @temp)
--and Player1 in (select PlayerID from TrendSelected_Players where SessionID = @hostid)--(select distinct PlayerID from @temp)
and SegmentID = @startSegment and Time >= @startTime and Time <= @endTime
And Event In(9,10,36,38,5,40,41,2,11,12,22,23,39,33,4)
group by matchid, player1, segmentid, event, outcome
order by matchid, player1, event
end
else
begin

insert into @tempEvents
select matchid, player1, segmentid, event, outcome, count(event) as eventCount from TrendEvents
where matchid in (select MatchID from TrendSelected_Matches where SessionID = @hostid)--(select distinct MatchID from @temp)
--and Player1 in (select PlayerID from TrendSelected_Players where SessionID = @hostid)--(select distinct PlayerID from @temp)
and ((SegmentID = @startSegment and Time >= @startTime)
or (SegmentID = @endSegment and Time <= @endTime))
And Event In(9,10,36,38,5,40,41,2,11,12,22,23,39,33,4)
group by matchid, player1, segmentid, event, outcome
order by matchid, Player1, event
end

-- Select * from @tempEvents

--Updated Total Period calculation...
if @segmentidcopy = -1
begin -- No segment defined by user

if @starttimecopy <> -1 or @endtimecopy <> -1
begin -- no start/end times defined

if @startSegment <> @endSegment
begin --SP calculated start/end segments are different
update @results
set totalperiod = v.tottime
from
(select matchid as m1, max(time) - @starttimecopy as tottime from TrendEvents
where segmentid = 0
group by matchid) v
where matchid = v.m1

update @results
set totalPeriod = totalPeriod + (@endtimecopy - @secondsInHalf)
end
else
begin -- SP calculated start/end segments are same

update @results
set totalPeriod = @endtimecopy - @startTimecopy
end
end
else
begin -- start/end times NOT defined...

if @startSegment <> @endSegment
begin --SP calculated start/end segments are different

update @results
set totalperiod = v.tottime
from
(select matchid as m1,sum(totalperiod) as tottime from @totaltime group by matchid) v
where matchid = v.m1

end
else
begin -- SP calculated start/end segments are same
update @results
set totalperiod = v.tottime
from
(select matchid as m1,sum(totalperiod) as tottime from @totaltime where segid = @segmentid group by matchid) v
where matchid = v.m1
end
end
end
else if @segmentidcopy = 0 or @segmentidcopy = 1
begin
if @starttimecopy <> -1 or @endtimecopy <> -1
begin -- A start/end time specified, this must have come from 5/15 min???

if @starttime = 2700
begin -- Looking for over time
update @results
set totalPeriod = v.tim
from
(select matchid as m1, max(time) - @starttimecopy as tim from TrendEvents
where SegmentID = @segmentidcopy
group by matchid)v
where v.m1 = matchid
end
else
begin
update @results
set totalPeriod = @endtimecopy - @startTimecopy
end
end
else
begin
update @results
set totalperiod = v.tottime
from
(select matchid as m1,sum(totalperiod) as tottime from @totaltime where segid = @segmentid group by matchid) v
where matchid = v.m1
end
end





-- For extrpolation
/********* Trend 2.1.1 **** Start ************/
Declare @ExtrPolatvalue int
select @ExtrPolatvalue=dbo.fn_getExtrapolatedvalue(@SessionID)
if @extrapType=0 or @extrapType = 1
update @results set timeOnPitch=totalperiod
where timeOnPitch < @ExtrPolatvalue -- gettting from function
/********* Trend 2.1.1 **** End *************/

--Insert overall shot count
update @results
set totalShotCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where (event = 9 or event = 10 or event = 36 or event=38) -- shot/header shot/direct free kick shot/pen.
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

-- on-target shots
update @results
set onTargetShotCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where (event = 9 or event = 10 or event=36 or event=38)
and outcome in (3,5) -- blocked on target, saved on target, goal
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set blockedShotCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where (event = 9 or event = 10 or event = 36 or event = 38) --Shooting events
and outcome in (1, 2)
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set crossesCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 5
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set freekickCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event between 35 and 37
or event between 40 and 41
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set dribbleCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 2
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set goalCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 11
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set ownGoalCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 12
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set cornerCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 22 or event = 23
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set offsideCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 39
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set fouledCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 33
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1

update @results
set tackledCount = v.cnt
from
(select matchid as m1, playerid as p1, sum(eventCount) as cnt from @tempEvents
where event = 4
group by matchid, playerid)v
where playerid = v.p1
and matchid = v.m1



Select * into #TempEvent From
(Select * From TrendEvents Where 1=2) T

if(@startSegment = @endSegment)
begin
Insert Into #TempEvent
Select * From TrendEvents
Where
MatchID In(Select MatchID From TrendSelected_Matches Where SessionID=@HostID)
And SegmentID = @startSegment and Time >= @startTime and Time <= @endTime
End
Else
Begin
Insert Into #TempEvent
Select * From TrendEvents
Where
MatchID In(Select MatchID From TrendSelected_Matches Where SessionID=@HostID)
And ((SegmentID = @startSegment and Time >= @startTime)
or (SegmentID = @endSegment and Time <= @endTime))
End




-- Shots inside the box
Update @Results
Set Shotsinsidebox=v.cnt
From
(Select MatchID as m1,Player1 as P1,Count(*) as cnt From #TempEvent
Where [dbo].[fn_ShotsInsideTheBox](MatchID,SegmentID,Time,Event)=1
Group By MatchId,Player1)v
Where MatchId=v.M1 and PlayerID=V.P1

-- Shots on target inside the box

Update @Results
Set Shotsontargetinsidebox=v.cnt
From
(Select MatchID as m1,Player1 as P1,Count(*) as cnt From #TempEvent
Where [dbo].[fn_ShotsOnTargetInsideTheBox](MatchID,SegmentID,Time,Event)=1
Group By MatchId,Player1)v
Where MatchId=v.M1 and PlayerID=V.P1

-- Shots outside the box

Update @Results
Set Shotsoutsidebox=v.cnt
From
(Select MatchID as m1,Player1 as P1,Count(*) as cnt From #TempEvent
Where [dbo].[fn_ShotsOutsideTheBox](MatchID,SegmentID,Time,Event)=1
Group By MatchId,Player1)v
Where MatchId=v.M1 and PlayerID=V.P1

-- Shots on target outside the box
Update @Results
Set ShotsontargetOutsidebox=v.cnt
From
(Select MatchID as m1,Player1 as P1,Count(*) as cnt From #TempEvent
Where [dbo].[fn_ShotsOnTargetOutsideTheBox](MatchID,SegmentID,Time,Event)=1
Group By MatchId,Player1)v
Where MatchId=v.M1 and PlayerID=V.P1

Drop Table #TempEvent

--Crosses from RIGHT
Update @Results
Set CrossesfromRIGHT = V.CrossCnt
From
(
select MAtchID as m1,Player1 as P1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 5 and YPosOrigin < 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))< 0
group By MatchID,Player1ClubID,Player1

union
select MAtchID as m1,Player1 as p1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 5 and YPosOrigin < 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))>0
group By MatchID,Player1ClubID,Player1
) V
Where MatchId=V.m1 and PlayerId=V.P1
--Crosses from LEFT
Update @Results
Set CrossesfromLEFT = V.CrossCnt
From
(
select MAtchID as m1,Player1 as P1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 5 and YPosOrigin > 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))<0
group By MatchID,Player1ClubID,Player1
union
select MAtchID as M1,Player1 as P1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 5 and YPosOrigin > 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))>0
group By MatchID,Player1ClubID,Player1
)V
Where MatchId=V.m1 and PlayerId=V.P1

-- Corners from RIGHT
Update @Results
Set CornersfromRIGHT = V.CrossCnt
From
(
select MAtchID as m1,Player1 as P1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 23 and YPosOrigin < 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))< 0
group By MatchID,Player1ClubID,Player1

union
select MAtchID as m1,Player1 as p1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 23 and YPosOrigin < 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))>0
group By MatchID,Player1ClubID,Player1
) V
Where MatchId=V.m1 and PlayerId=V.P1
-- Corners from LEFT
Update @Results
Set CornersfromLEFT = V.CrossCnt
From
(
select MAtchID as m1,Player1 as P1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 23 and YPosOrigin > 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))<0
group By MatchID,Player1ClubID,Player1
union
select MatchID as M1,Player1 as P1,Player1ClubID,Count(YPosOrigin) as CrossCnt
From TrendEvents Base
Where Event = 23 and YPosOrigin > 0
and (select avg(XPosOrigin) from TrendEvents
where MatchID = Base.MatchID
and Player1ClubID = Base.Player1ClubID
and Event in (19, 24, 25, 26, 27, 28, 29, 30, 31, 32))>0
group By MatchID,Player1ClubID,Player1
)V
Where MatchId=V.m1 and PlayerId=V.P1

/******** Trend2.1 ****End********/

--Final 3rd and penalty Area entries...
if @startSegment = @endSegment
begin

update @results
set FinalThirdEntryCount = v.cnt
from
(select matchid as m1, player1 as p1, count(*) as cnt from TrendEvents
where matchid in (select MatchID from TrendSelected_Matches where SessionID = @hostid)--(select matchid from @results)
and player1 in (select PlayerID from TrendSelected_Players where SessionID = @hostid)--(select playerid from @results)
and SegmentID = @startSegment and Time >= @startTime and Time <= @endTime
and event in (2,3,5,6,19, 20, 22, 23, 27, 31, 35, 36, 37, 40, 41, 48)
and final3rdEntry = 1
group by matchid, player1)v
where matchid = v.m1
and playerid = v.p1


update @results
set PenAreaEntryCount = v.cnt
from
(select matchid as m1, player1 as p1, count(*) as cnt from TrendEvents
where matchid in (select MatchID from TrendSelected_Matches where SessionID = @hostid)--(select matchid from @results)
and player1 in (select PlayerID from TrendSelected_Players where SessionID = @hostid)--(select playerid from @results)
and SegmentID = @startSegment and Time >= @startTime and Time <= @endTime
and event in (2,3,5,6,19, 20, 22, 23, 27, 31, 35, 36, 37, 40, 41, 48)
and penAreaEntry = 1
group by matchid, player1)v
where matchid = v.m1
and playerid = v.p1
end
else
begin

update @results
set FinalThirdEntryCount = v.cnt
from
(select matchid as m1, player1 as p1, count(*) as cnt from TrendEvents
where matchid in (select MatchID from TrendSelected_Matches where SessionID = @hostid)--(select matchid from @results)
and player1 in (select PlayerID from TrendSelected_Players where SessionID = @hostid)--(select playerid from @results)
and ((SegmentID = @startSegment and Time >= @startTime)
or (SegmentID = @endSegment and Time <= @endTime))
and event in (2,3,5,6,19, 20, 22, 23, 27, 31, 35, 36, 37, 40, 41, 48)
and final3rdEntry = 1
group by matchid, player1)v
where matchid = v.m1
and playerid = v.p1

update @results
set PenAreaEntryCount = v.cnt
from
(select matchid as m1, player1 as p1, count(*) as cnt from TrendEvents
where matchid in (select MatchID from TrendSelected_Matches where SessionID = @hostid)--(select matchid from @results)
and player1 in (select PlayerID from TrendSelected_Players where SessionID = @hostid)--(select playerid from @results)
and ((SegmentID = @startSegment and Time >= @startTime)
or (SegmentID = @endSegment and Time <= @endTime))
and event in (2,3,5,6,19, 20, 22, 23, 27, 31, 35, 36, 37, 40, 41, 48)
and penAreaEntry = 1
group by matchid, player1)v
where matchid = v.m1
and playerid = v.p1
end

update @results set matchDate = v.md
from
(select matchID as mi, Date as md from trendMATCHES)v
where matchid = v.mi

update @results
set clubid = v.c1
from
(select matchid as m1, playerid as p1, clubid as c1 from view_matchInfoMoreComplete
where matchid in (select matchid from TrendSelected_matches where SessionID = @hostid))v
where matchid = v.m1
and playerid = v.p1

delete from @results
where clubid not in (select clubid from TrendSelected_Teams where SessionID = @hostid)

delete from @results where timeonpitch < 0.0



--DECLARE RESULTS1 TABLE FOR SUMMING TEAM INFORMATION BEFORE AVERAGING
declare @results1 table (
matchid int,
--playerid int,
clubid int,
TimeOnPitch numeric(7,2),
TotalPeriod numeric(7,2),
totalShotCount numeric(8,1) default 0,
blockedShotCount numeric(8,1) default 0,
onTargetShotCount numeric(8,1) default 0,
goalCount numeric(8,1) default 0,
ownGoalCount numeric(8,1) default 0,
dribbleCount numeric(8,1) default 0,
crossesCount numeric(8,1) default 0,
cornerCount numeric(8,1) default 0,
offsideCount numeric(8,1) default 0,
tackledCount numeric(8,1) default 0,
fouledCount numeric(8,1) default 0,
freekickCount numeric(8,1) default 0,
finalThirdEntryCount numeric(8,1) default 0,
penAreaEntryCount numeric(8,1) default 0,
CopyTimeOnPitch numeric(7,2),
Shotsinsidebox int default 0,
Shotsontargetinsidebox int default 0,
Shotsoutsidebox int default 0,
Shotsontargetoutsidebox int default 0,
CrossesfromLEFT int default 0,
CrossesfromRIGHT int default 0,
CornersfromLEFT int default 0,
CornersfromRIGHT int default 0
primary key(matchid, clubid)
)


if @extrapType = 0--Linear Extrap
begin
--LINEAR EXTRAP PLAYER PER MATCH
Select * into #A1 From
(
select
matchid, clubid, playerid,
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolveMatchID(matchid) as 'Match',
dbo.fn_ResolveClubID(clubid) as 'Team',
convert (varchar, matchDate, 111) as 'Date',
--dbo.fn_ResolveTimeValue(@SessionID, totalperiod) as 'Time On Pitch',

-- For Extrpolation
case when (TimeonPitch <> CopyTimeonPitch) then dbo.fn_ResolveTimeValue(@SessionID, CopyTimeonPitch)
else dbo.fn_ResolveTimeValue(@SessionID, totalperiod) end as 'Time On Pitch',

cast(totalShotCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Shots',
cast(onTargetShotCount * (totalperiod / TimeonPitch) as numeric(8,1))as 'Shots on Target',
cast((totalShotCount - (onTargetShotCount + blockedShotCount)) * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Shots off Target',
Shotsinsidebox as 'Shots inside the box',
Shotsontargetinsidebox as 'Shots on target inside the box',
Shotsoutsidebox as 'Shots outside the box',
Shotsontargetoutsidebox as 'Shots on target outside the box',
cast(blockedShotCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(onTargetShotCount,0) as numeric(7,2))/cast(nullif(totalShotCount, 0) as numeric(7,2))) * 100 as numeric(7,2))/* * (totalperiod / TimeonPitch)*/), 0) as numeric(8,2)) as 'Shooting Accuracy',
cast(goalCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Goals',
cast(ownGoalCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Own Goals',
cast(offsideCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Offsides',
cast(dribbleCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Dribbles',
cast(crossesCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Crosses',
CrossesfromLEFT as 'Crosses from LEFT',
CrossesfromRIGHT as 'Crosses from RIGHT',
cast(cornerCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Corners Taken',
CornersfromLEFT as 'Corners from LEFT',
CornersfromRIGHT as 'Corners from RIGHT',
cast(freekickCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Free Kicks Taken',
cast(fouledCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Fouled',
cast(tackledCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Tackled',
cast(finalThirdEntryCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Final 3rd Entries',
cast(penAreaEntryCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Pen Area Entries',
-- For Extrapolation
case when (TimeonPitch < [totalperiod]) then '1' else '0' end as 'ExtraPolated'

from @results
where
/********* Trend 2.1.1 **** Start ************/
PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
/********* Trend 2.1.1 **** End **************/
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
-- order by matchid, clubid, playerid
)A1
IF @SelectedFields = ''
Select
[Player],[Match],[Team],[Date],[Time On Pitch],[Shots],[Shots on Target]
,[Shots off Target],[Shots inside the box],[Shots on target inside the box]
,[Shots outside the box],[Shots on target outside the box],[Blocked Shots],[Shooting Accuracy]
,[Goals],[Own Goals],[Offsides],[Dribbles],[Crosses],[Crosses from LEFT],[Crosses from RIGHT]
,[Corners Taken],[Corners from LEFT],[Corners from RIGHT],[Free Kicks Taken],[Fouled]
,[Tackled],[Final 3rd Entries],[Pen Area Entries]
from #A1 Order By matchid, clubid, playerid
Else
EXEC('Select [Player],[Match],[Team],[Date] ,' + @SelectedFields + 'From #A1 Order By matchid, clubid, playerid' )

Drop table #A1

--LINEAR EXTRAP PLAYER AVERAGE
Select * into #A2 From
(
select
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolveClubID(clubid) as 'Team',
count(matchid) as '# Matches',
-- dbo.fn_ResolveTimeValue(@SessionID, avg(TotalPeriod)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

cast(avg(totalShotCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Shots',
cast(avg(onTargetShotCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Shots on Target',
cast((avg((totalShotCount - (onTargetShotCount + blockedShotCount)) * (totalperiod / TimeonPitch))) as numeric(8,1)) as 'Shots off Target',
avg(Shotsinsidebox) as 'Shots inside the box',
avg(Shotsontargetinsidebox) as 'Shots on target inside the box',
avg(Shotsoutsidebox) as 'Shots outside the box',
avg(Shotsontargetoutsidebox) as 'Shots on target outside the box',
cast(avg(blockedShotCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(avg(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(avg(goalCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Goals',
cast(avg(ownGoalCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Own Goals',
cast(avg(offsideCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Offsides',
cast(avg(dribbleCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Dribbles',
cast(avg(crossesCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Crosses',
Avg(CrossesfromLEFT) as 'Crosses from LEFT',
Avg(CrossesfromRIGHT) as 'Crosses from RIGHT',
cast(avg(cornerCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Corners Taken',
Avg(CornersfromLEFT) as 'Corners from LEFT',
Avg(CornersfromRIGHT) as 'Corners from RIGHT',
cast(avg(freekickCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Free Kicks Taken',
cast(avg(fouledCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Fouled',
cast(avg(tackledCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Tackled',
cast(avg(finalThirdEntryCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Final 3rd Entries',
cast(avg(penAreaEntryCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'
from @results
--where PlayerID in (select PlayerID from TrendSelected_Players where SessionID = @SessionID)
where PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)

AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
group by playerid, clubid
)A2
IF @SelectedFields = ''
Select * From #A2
ELSE
EXEC ('Select [Player],[Team],[# Matches],' + @SelectedFields + 'From #A2')

Drop table #A2


--LINEAR EXTRAP TEAM PER MATCH (NO EXTRAP REQUIRED - SAME AS DEFAULT RESULTS)
Select * into #A3 From
(
select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, max(matchDate), 111) as 'Date',
-- dbo.fn_ResolveTimeValue(@SessionID, max(TimeOnPitch)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

cast(sum(totalShotCount) as int) as 'Shots',
cast(sum(onTargetShotCount) as int) as 'Shots on Target',
cast((sum(totalShotCount - (onTargetShotCount + blockedShotCount))) as int) as 'Shots off Target',
Sum(Shotsinsidebox) as 'Shots inside the box',
Sum(Shotsontargetinsidebox) as 'Shots on target inside the box',
Sum(Shotsoutsidebox) as 'Shots outside the box',
Sum(Shotsontargetoutsidebox) as 'Shots on target outside the box',
cast(sum(blockedShotCount) as int) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(sum(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(sum(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(sum(goalCount) as int) as 'Goals',
cast(sum(ownGoalCount) as int) as 'Own Goals',
cast(sum(offsideCount) as int) as 'Offsides',
cast(sum(dribbleCount) as int) as 'Dribbles',
cast(sum(crossesCount) as int) as 'Crosses',
Sum(CrossesfromLEFT) as 'Crosses from LEFT',
Sum(CrossesfromRIGHT) as 'Crosses from RIGHT',
cast(sum(cornerCount) as int) as 'Corners Taken',
Sum(CornersfromLEFT) as 'Corners from LEFT',
Sum(CornersfromRIGHT) as 'Corners from RIGHT',
cast(sum(freekickCount) as int) as 'Free Kicks Taken',
cast(sum(fouledCount) as int) as 'Fouled',
cast(sum(tackledCount) as int) as 'Tackled',
cast(sum(finalThirdEntryCount) as int) as 'Final 3rd Entries',
cast(sum(penAreaEntryCount) as int) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results
--where ClubID in (Select ClubID from TrendSelected_Teams where SessionID = @SessionID)
-- For Custom Report
Where ClubID In(select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))

AND ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))--CHECK WHETHER GOALKEEPER IS REQUIRED
group by clubid, matchid
)A3
IF @SelectedFields = ''
Select * from #A3
Else
EXEC('Select [Team],[Match],[Date], ' + @SelectedFields + ' From #A3 ')
Drop table #A3



--INSERT INTO SECOND TABLE FOR LINEAR EXTRAP TEAM AVERAGE
insert into @results1
(clubid,matchid,timeonpitch,copytimeonpitch,totalperiod,totalShotCount,blockedShotCount,onTargetShotCount,goalCount,ownGoalCount,offsideCount,dribbleCount,
crossesCount,cornerCount,freekickCount,fouledCount,tackledCount, finalThirdEntryCount, penAreaEntryCount
,Shotsinsidebox ,Shotsontargetinsidebox ,Shotsoutsidebox ,Shotsontargetoutsidebox ,
CrossesfromLEFT ,CrossesfromRIGHT , CornersfromLEFT ,CornersfromRIGHT
)
(select clubid,matchid,sum(timeonpitch),sum(copytimeonpitch),avg(totalperiod),sum(totalShotCount),sum(blockedShotCount),sum(onTargetShotCount),sum(goalCount),sum(ownGoalCount),sum(offsideCount),sum(dribbleCount),
sum(crossesCount),sum(cornerCount),sum(freekickCount),sum(fouledCount),sum(tackledCount), sum(finalThirdEntryCount), sum(penAreaEntryCount)
,Sum(Shotsinsidebox) ,Sum(Shotsontargetinsidebox) ,Sum(Shotsoutsidebox) ,Sum(Shotsontargetoutsidebox) ,
Sum(CrossesfromLEFT) ,Sum(CrossesfromRIGHT) , Sum(CornersfromLEFT) ,Sum(CornersfromRIGHT)
from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
AND ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))--CHECK WHETHER GOALKEEPER IS REQUIRED
group by matchid, clubid)
--LINEAR EXTRAP TEAM AVERAGE

Select * into #A4 From
(
select
dbo.fn_ResolveClubID(clubid) as 'Team',
count(dbo.fn_ResolveMatchID(matchid)) as '# of Matches',
-- cast( dbo.fn_ResolveTimeValue(@SessionID,max(totalperiod)) as numeric(7,2)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

Cast(avg(totalShotCount) as numeric(8,1)) as 'Shots',
Cast(avg(onTargetShotCount) as numeric(8,1)) as 'Shots On Target',
cast( (avg(totalShotCount) - avg(onTargetShotCount)) as numeric(8,1) ) as 'Shots off Target',
Avg(Shotsinsidebox) as 'Shots inside the box',
Avg(Shotsontargetinsidebox) as 'Shots on target inside the box',
Avg(Shotsoutsidebox) as 'Shots outside the box',
Avg(Shotsontargetoutsidebox) as 'Shots on target outside the box',
Cast(avg(blockedShotCount) as numeric(8,1)) as 'Blocked Shots',
cast( (cast(avg(onTargetShotCount) as numeric(7,2)) / cast(nullif(avg(totalShotCount), 0) as numeric(7,2)) ) * 100 as numeric(8,1) ) as 'Shooting Accuracy',
Cast(avg(goalCount) as numeric(8,1)) as 'Goals',
Cast(avg(ownGoalCount) as numeric(8,1)) as 'Own Goals',
Cast(avg(offsideCount) as numeric(8,1)) as 'Offsides',
Cast(avg(dribbleCount) as numeric(8,1)) as 'Dribbles',
Cast(avg(crossesCount) as numeric(8,1)) as 'Crosses',
Avg(CrossesfromLEFT) as 'Crosses from LEFT',
Avg(CrossesfromRIGHT) as 'Crosses from RIGHT',
Cast(avg(cornerCount) as numeric(8,1)) as 'Corners Taken',
Avg(CornersfromLEFT) as 'Corners from LEFT',
Avg(CornersfromRIGHT) as 'Corners from RIGHT',
Cast(avg(freekickCount) as numeric(8,1)) as 'Free Kicks Taken',
Cast(avg(fouledCount) as numeric(8,1)) as 'Fouled',
Cast(avg(tackledCount) as numeric(8,1)) as 'Tackled',
Cast(avg(finalThirdEntryCount) as numeric(8,1)) as 'Final 3rd Entries',
Cast(avg(penAreaEntryCount) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results1
Where ClubID In(select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
group by clubid
)A4
IF @SelectedFields = ''
Select * from #A4
Else
EXEC('Select [Team],[# of Matches], ' + @SelectedFields + ' From #A4')
Drop table #A4
--LINEAR EXTRAP POSITIONS
if @returnPositions > -1
begin
--Positions are required...
-- Insert the positions into the @results table...
update @results
set positionID = dbo.fn_ResolvePlayersPositionSegment(matchid, playerid, 0)

update @results
set positionID = dbo.fn_ResolvePlayersPositionSegment(matchid, playerid, 1)
where positionID <= 0
--LINEAR EXTRAP POSITION PER MATCH

select
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, matchDate, 111) as 'Date',
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolvePositionID(positionid) as 'Position',
-- dbo.fn_ResolveTimeValue(@SessionID, totalperiod) as 'Time On Pitch',
-- For Extrpolation
case when (TimeonPitch <> CopyTimeonPitch) then dbo.fn_ResolveTimeValue(@SessionID, CopyTimeonPitch)
else dbo.fn_ResolveTimeValue(@SessionID, totalperiod) end as 'Time On Pitch',

cast(totalShotCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Shots',
cast(onTargetShotCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Shots on Target',
cast((totalShotCount - onTargetShotCount) * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Shots off Target',
cast(blockedShotCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Blocked Shots',
cast((cast((cast(nullif(onTargetShotCount, 0) as numeric(7,2))/cast(nullif(totalShotCount, 0) as numeric(7,2))) * 100 as numeric(7,2))) as int) as 'Shooting Accuracy',
cast(goalCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Goals',
cast(ownGoalCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Own Goals',
cast(offsideCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Offsides',
cast(dribbleCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Dribbles',
cast(crossesCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Crosses',
cast(cornerCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Corners Taken',
cast(freekickCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Free Kicks Taken',
cast(fouledCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Fouled',
cast(tackledCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Tackled',
cast(finalThirdEntryCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Final 3rd Entries',
cast(penAreaEntryCount * (totalperiod / TimeonPitch) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (TimeonPitch < [totalperiod]) then '1' else '0' end as 'ExtraPolated'

from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
and ((positionID in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
and
0 not in (select PositionID from TrendSelected_Positions where SessionID = @SessionID))
or 0 in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
or not exists (select * from TrendSelected_Positions where SessionID = @SessionID))
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))



--LINEAR EXTRAP POSITION AVERAGE

select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolvePositionID(positionid) as 'Position',
count(matchid) as '# Matches',
-- dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

cast(avg(totalShotCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Shots',
cast(avg(onTargetShotCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Shots on Target',
cast(avg((totalShotCount - onTargetShotCount) * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Shots off Target',
cast(avg(blockedShotCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Blocked Shots',
cast((cast((cast(nullif(avg(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))) as int) as 'Shooting Accuracy',
cast(avg(goalCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Goals',
cast(avg(ownGoalCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Own Goals',
cast(avg(offsideCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Offsides',
cast(avg(dribbleCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Dribbles',
cast(avg(crossesCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Crosses',
cast(avg(cornerCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Corners Taken',
cast(avg(freekickCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Free Kicks Taken',
cast(avg(fouledCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Fouled',
cast(avg(tackledCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Tackled',
cast(avg(finalThirdEntryCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Final 3rd Entries',
cast(avg(penAreaEntryCount * (totalperiod / TimeonPitch)) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
and ((positionID in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
and
0 not in (select PositionID from TrendSelected_Positions where SessionID = @SessionID))
or 0 in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
or not exists (select * from TrendSelected_Positions where SessionID = @SessionID))
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
group by clubid, playerid, positionid

end
end
--exec sp_GenerateAttackingOverview 0,1,-1,-1,-1,-1,-1,-1,1,-1,1,-1,''
else if @extrapType = 1--normalization
begin
--NORMALIZED PLAYER PER MATCH
Select * Into #A5 From
(
select matchid, clubid, playerid,
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolveMatchID(matchid) as 'Match',
dbo.fn_ResolveClubID(clubid) as 'Team',
convert (varchar, matchDate, 111) as 'Date',
-- dbo.fn_ResolveTimeValue(@SessionID, TimeOnPitch) as 'Time On Pitch',
-- For Extrpolation
case when (TimeonPitch <> CopyTimeonPitch) then dbo.fn_ResolveTimeValue(@SessionID, CopyTimeonPitch)
else dbo.fn_ResolveTimeValue(@SessionID, totalperiod) end as 'Time On Pitch',

cast(totalShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Shots',
cast(onTargetShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Shots on Target',
cast(blockedShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Blocked Shots',
cast((totalShotCount - (onTargetShotCount + blockedShotCount)) / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Shots off Target',
Shotsinsidebox as 'Shots inside the box',
Shotsontargetinsidebox as 'Shots on target inside the box',
Shotsoutsidebox as 'Shots outside the box',
Shotsontargetoutsidebox as 'Shots on target outside the box',
cast(isnull((cast((cast(nullif(onTargetShotCount,0) as numeric(7,2))/cast(nullif(totalShotCount, 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(8,2)) as 'Shooting Accuracy',
cast(goalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Goals',
cast(ownGoalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Own Goals',
cast(offsideCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Offsides',
cast(dribbleCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Dribbles',
cast(crossesCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Crosses',
CrossesfromLEFT as 'Crosses from LEFT',
CrossesfromRIGHT as 'Crosses from RIGHT',
cast(cornerCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Corners Taken',
CornersfromLEFT as 'Corners from LEFT',
CornersfromRIGHT as 'Corners from RIGHT',
cast(freekickCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Free Kicks Taken',
cast(fouledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Fouled',
cast(tackledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Tackled',
cast(finalThirdEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Final 3rd Entries',
cast(penAreaEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (TimeonPitch < [totalperiod]) then '1' else '0' end as 'ExtraPolated'


from @results
--where PlayerID in (select PlayerID from TrendSelected_Players where SessionID = @SessionID)

Where PlayerID in (select PlayerID from TrendSelected_PLayers Where SessionID=@SessionID)

and ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))

-- order by matchid, clubid, playerid
)A5

IF @SelectedFields = ''
Select
[Player],[Match],[Team],[Date],[Time On Pitch],[Shots],[Shots on Target]
,[Shots off Target],[Shots inside the box],[Shots on target inside the box]
,[Shots outside the box],[Shots on target outside the box],[Blocked Shots],[Shooting Accuracy]
,[Goals],[Own Goals],[Offsides],[Dribbles],[Crosses],[Crosses from LEFT],[Crosses from RIGHT]
,[Corners Taken],[Corners from LEFT],[Corners from RIGHT],[Free Kicks Taken],[Fouled]
,[Tackled],[Final 3rd Entries],[Pen Area Entries]
from #A5 Order By matchid, clubid, playerid
Else
EXEC('Select [Player],[Match],[Team],[Date] ,' + @SelectedFields + 'From #A5 Order By matchid, clubid, playerid' )
Drop table #A5


--NORMALIZED PLAYER AVERAGE
Select * Into #A6 From
(
select
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolveClubID(clubid) as 'Team',
count(matchid) as '# Matches',
-- dbo.fn_ResolveTimeValue(@SessionID, avg(TotalPeriod)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

cast(avg(totalShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Shots',
cast(avg(onTargetShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Shots on Target',
cast((avg((totalShotCount - (onTargetShotCount + blockedShotCount)) / dbo.fn_ResolveTimetoMinutes(TimeOnPitch))) as numeric(8,1)) as 'Shots off Target',
Avg(Shotsinsidebox) as 'Shots inside the box',
Avg(Shotsontargetinsidebox) as 'Shots on target inside the box',
Avg(Shotsoutsidebox) as 'Shots outside the box',
Avg(Shotsontargetoutsidebox) as 'Shots on target outside the box',
cast(avg(blockedShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(avg(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(avg(goalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Goals',
cast(avg(ownGoalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Own Goals',
cast(avg(offsideCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Offsides',
cast(avg(dribbleCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Dribbles',
cast(avg(crossesCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Crosses',
Avg(CrossesfromLEFT) as 'Crosses from LEFT',
Avg(CrossesfromRIGHT) as 'Crosses from RIGHT',
cast(avg(cornerCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Corners Taken',
Avg(CornersfromLEFT) as 'Corners from LEFT',
Avg(CornersfromRIGHT) as 'Corners from RIGHT',
cast(avg(freekickCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Free Kicks Taken',
cast(avg(fouledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Fouled',
cast(avg(tackledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Tackled',
cast(avg(finalThirdEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Final 3rd Entries',
cast(avg(penAreaEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results
--where PlayerID in (select PlayerID from TrendSelected_Players where SessionID = @SessionID)
Where PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)

AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
group by playerid, clubid
)A6

IF @SelectedFields = ''
Select * From #A6
ELSE
EXEC ('Select [Player],[Team],[# Matches],' + @SelectedFields + 'From #A6')
Drop table #A6


--NORMALIZED TEAM PER MATCH
Select * into #A7 From
(
select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, max(matchDate), 111) as 'Date',
-- dbo.fn_ResolveTimeValue(@SessionID, max(TimeOnPitch)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

cast(sum(totalShotCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Shots',
cast(sum(onTargetShotCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Shots on Target',
cast((sum(totalShotCount - (onTargetShotCount + blockedShotCount))) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Shots off Target',
Sum(Shotsinsidebox) as 'Shots inside the box',
Sum(Shotsontargetinsidebox) as 'Shots on target inside the box',
Sum(Shotsoutsidebox) as 'Shots outside the box',
Sum(Shotsontargetoutsidebox) as 'Shots on target outside the box',
cast(sum(blockedShotCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(sum(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(sum(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(sum(goalCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Goals',
cast(sum(ownGoalCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Own Goals',
cast(sum(offsideCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Offsides',
cast(sum(dribbleCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Dribbles',
cast(sum(crossesCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Crosses',
Sum(CrossesfromLEFT) as 'Crosses from LEFT',
Sum(CrossesfromRIGHT) as 'Crosses from RIGHT',
cast(sum(cornerCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Corners Taken',
Sum(CornersfromLEFT) as 'Corners from LEFT',
Sum(CornersfromRIGHT) as 'Corners from RIGHT',
cast(sum(freekickCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Free Kicks Taken',
cast(sum(fouledCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Fouled',
cast(sum(tackledCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Tackled',
cast(sum(finalThirdEntryCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Final 3rd Entries',
cast(sum(penAreaEntryCount) / dbo.fn_ResolveTimetoMinutes(avg(TotalPeriod)) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results
--where ClubID in (Select ClubID from TrendSelected_Teams where SessionID = @SessionID)
Where ClubID In(select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
AND ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))
group by clubid, matchid
)A7
IF @SelectedFields = ''
Select * from #A7
Else
EXEC('Select [Team],[Match],[Date], ' + @SelectedFields + ' From #A7 ')

Drop table #A7


--INSERT INTO SECOND TABLE FOR NORMALIZED TEAM AVERAGE
insert into @results1
(clubid,matchid,timeonpitch,copytimeonpitch,totalperiod,totalShotCount,blockedShotCount,onTargetShotCount,goalCount,ownGoalCount,offsideCount,dribbleCount,
crossesCount,cornerCount,freekickCount,fouledCount,tackledCount, finalThirdEntryCount, penAreaEntryCount
,Shotsinsidebox ,Shotsontargetinsidebox ,Shotsoutsidebox ,Shotsontargetoutsidebox ,
CrossesfromLEFT ,CrossesfromRIGHT , CornersfromLEFT ,CornersfromRIGHT)
(select clubid,matchid,sum(timeonpitch),sum(copytimeonpitch),avg(totalperiod),sum(totalShotCount),sum(blockedShotCount),sum(onTargetShotCount),sum(goalCount),sum(ownGoalCount),sum(offsideCount),sum(dribbleCount),
sum(crossesCount),sum(cornerCount),sum(freekickCount),sum(fouledCount),sum(tackledCount), sum(finalThirdEntryCount), sum(penAreaEntryCount)
,Sum(Shotsinsidebox) ,Sum(Shotsontargetinsidebox) ,Sum(Shotsoutsidebox) ,Sum(Shotsontargetoutsidebox) ,
Sum(CrossesfromLEFT) ,Sum(CrossesfromRIGHT) , Sum(CornersfromLEFT) ,Sum(CornersfromRIGHT)
from @results
where clubid in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
AND ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))
group by matchid, clubid)
--NORMALIZED TEAM AVERAGE
Select * into #A8 From
(
select
dbo.fn_ResolveClubID(clubid) as 'Team',
count(dbo.fn_ResolveMatchID(matchid)) as '# of Matches',
-- cast( dbo.fn_ResolveTimeValue(@SessionID,max(totalperiod)) as numeric(7,2)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

Cast(avg(totalShotCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Shots',
Cast(avg(onTargetShotCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Shots On Target',
cast( (avg(totalShotCount) - avg(onTargetShotCount)) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1) ) as 'Shots off Target',
Avg(Shotsinsidebox) as 'Shots inside the box',
Avg(Shotsontargetinsidebox) as 'Shots on target inside the box',
Avg(Shotsoutsidebox) as 'Shots outside the box',
Avg(Shotsontargetoutsidebox) as 'Shots on target outside the box',
Cast(avg(blockedShotCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Blocked Shots',
cast((cast(avg(onTargetShotCount) as numeric(7,2))/ cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 /dbo.fn_ResolveTimetoMinutes(avg(timeOnPitch)) as numeric(8,1) ) as 'Shooting Accuracy',
Cast(avg(goalCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Goals',
Cast(avg(ownGoalCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Own Goals',
Cast(avg(offsideCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Offsides',
Cast(avg(dribbleCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Dribbles',
Cast(avg(crossesCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Crosses',
Avg(CrossesfromLEFT) as 'Crosses from LEFT',
Avg(CrossesfromRIGHT) as 'Crosses from RIGHT',
Cast(avg(cornerCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Corners Taken',
Sum(CornersfromLEFT) as 'Corners from LEFT',
Sum(CornersfromRIGHT) as 'Corners from RIGHT',
Cast(avg(freekickCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Free Kicks Taken',
Cast(avg(fouledCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Fouled',
Cast(avg(tackledCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Tackled',
Cast(avg(finalThirdEntryCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Final 3rd Entries',
Cast(avg(penAreaEntryCount) / dbo.fn_ResolveTimetoMinutes(avg(totalPeriod)) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results1
Where ClubID In (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
group by clubid
)A8
IF @SelectedFields = ''
Select * from #A8
Else
EXEC('Select [Team],[# of Matches], ' + @SelectedFields + ' From #A8')
Drop table #A8

--NORMALIZED POSITIONS
if @returnPositions > -1
begin
--Positions are required...
-- Insert the positions into the @results table...
update @results
set positionID = dbo.fn_ResolvePlayersPositionSegment(matchid, playerid, 0)

update @results
set positionID = dbo.fn_ResolvePlayersPositionSegment(matchid, playerid, 1)
where positionID <= 0


--NORMALIZED POSITIONS PER MATCH

select
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, matchDate, 111) as 'Date',
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolvePositionID(positionid) as 'Position',
--dbo.fn_ResolveTimeValue(@SessionID, TimeOnPitch) as 'Time On Pitch',
-- For Extrpolation
case when (TimeonPitch <> CopyTimeonPitch) then dbo.fn_ResolveTimeValue(@SessionID, CopyTimeonPitch)
else dbo.fn_ResolveTimeValue(@SessionID, totalperiod) end as 'Time On Pitch',

cast(totalShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Shots',
cast(onTargetShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Shots on Target',
cast((totalShotCount - onTargetShotCount) / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Shots off Target',
cast(blockedShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Blocked Shots',
cast((cast((cast(nullif(onTargetShotCount, 0) as numeric(7,2))/cast(nullif(totalShotCount, 0) as numeric(7,2))) * 100 as numeric(7,2))) as int) as 'Shooting Accuracy',
cast(goalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Goals',
cast(ownGoalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Own Goals',
cast(offsideCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Offsides',
cast(dribbleCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Dribbles',
cast(crossesCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Crosses',
cast(cornerCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Corners Taken',
cast(freekickCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Free Kicks Taken',
cast(fouledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Fouled',
cast(tackledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Tackled',
cast(finalThirdEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Final 3rd Entries',
cast(penAreaEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (TimeonPitch < [totalperiod]) then '1' else '0' end as 'ExtraPolated'

from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
and ((positionID in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
and
0 not in (select PositionID from TrendSelected_Positions where SessionID = @SessionID))
or 0 in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
or not exists (select * from TrendSelected_Positions where SessionID = @SessionID))
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))


--NORMALIZED POSITION AVERAGE

select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolvePositionID(positionid) as 'Position',
count(matchid) as '# Matches',
-- dbo.fn_ResolveTimeValue(@SessionID, avg(TimeOnPitch)) as 'Time On Pitch',
-- For Extrpolation
case when (avg(TimeonPitch) <> avg(CopyTimeonPitch)) then dbo.fn_ResolveTimeValue(@SessionID, avg(CopyTimeonPitch))
else dbo.fn_ResolveTimeValue(@SessionID, avg(totalperiod)) end as 'Time On Pitch',

cast(avg(totalShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Shots',
cast(avg(onTargetShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Shots on Target',
cast(avg((totalShotCount - onTargetShotCount) / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Shots off Target',
cast(avg(blockedShotCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Blocked Shots',
cast((cast((cast(nullif(avg(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))) as int) as 'Shooting Accuracy',
cast(avg(goalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Goals',
cast(avg(ownGoalCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Own Goals',
cast(avg(offsideCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Offsides',
cast(avg(dribbleCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Dribbles',
cast(avg(crossesCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Crosses',
cast(avg(cornerCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Corners Taken',
cast(avg(freekickCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Free Kicks Taken',
cast(avg(fouledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Fouled',
cast(avg(tackledCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Tackled',
cast(avg(finalThirdEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Final 3rd Entries',
cast(avg(penAreaEntryCount / dbo.fn_ResolveTimetoMinutes(TimeOnPitch)) as numeric(8,1)) as 'Pen Area Entries'
-- For Extrapolation
,case when (avg(TimeonPitch) < avg([totalperiod])) then '1' else '0' end as 'ExtraPolated'

from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
and ((positionID in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
and
0 not in (select PositionID from TrendSelected_Positions where SessionID = @SessionID))
or 0 in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
or not exists (select * from TrendSelected_Positions where SessionID = @SessionID))
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
group by clubid, playerid, positionid

end
end
else --DEFAULT RESULTS REQUIRED
begin
--DEFAULT PLAYER PER MATCH
Select * Into #A9 From
(
select matchid, clubid, playerid,
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolveMatchID(matchid) as 'Match',
dbo.fn_ResolveClubID(clubid) as 'Team',
convert (varchar, matchDate, 111) as 'Date',
dbo.fn_ResolveTimeValue(@SessionID, TimeOnPitch) as 'Time On Pitch',
cast(totalShotCount as int) as 'Shots',
cast(onTargetShotCount as int) as 'Shots on Target',
cast((totalShotCount - (onTargetShotCount + blockedShotCount)) as int) as 'Shots off Target',
Shotsinsidebox as 'Shots inside the box',
Shotsontargetinsidebox as 'Shots on target inside the box',
Shotsoutsidebox as 'Shots outside the box',
Shotsontargetoutsidebox as 'Shots on target outside the box',
cast(blockedShotCount as int) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(onTargetShotCount, 0) as numeric(7,2))/cast(nullif(totalShotCount, 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(goalCount as int) as 'Goals',
cast(ownGoalCount as int) as 'Own Goals',
cast(offsideCount as int) as 'Offsides',
cast(dribbleCount as int) as 'Dribbles',
cast(crossesCount as int) as 'Crosses',
CrossesfromLEFT as 'Crosses from LEFT',
CrossesfromRIGHT as 'Crosses from RIGHT',
cast(cornerCount as int) as 'Corners Taken',
CornersfromLEFT as 'Corners from LEFT',
CornersfromRIGHT as 'Corners from RIGHT',
cast(freekickCount as int) as 'Free Kicks Taken',
cast(fouledCount as int) as 'Fouled',
cast(tackledCount as int) as 'Tackled',
cast(finalThirdEntryCount as int) as 'Final 3rd Entries',
cast(penAreaEntryCount as int) as 'Pen Area Entries'

from @results
--where PlayerID in (select PlayerID from TrendSelected_Players where SessionID = @SessionID)
Where PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)

and ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
-- order by matchid, clubid, playerid
)A9
IF @SelectedFields = ''
Select
[Player],[Match],[Team],[Date],[Time On Pitch],[Shots],[Shots on Target]
,[Shots off Target],[Shots inside the box],[Shots on target inside the box]
,[Shots outside the box],[Shots on target outside the box],[Blocked Shots],[Shooting Accuracy]
,[Goals],[Own Goals],[Offsides],[Dribbles],[Crosses],[Crosses from LEFT],[Crosses from RIGHT]
,[Corners Taken],[Corners from LEFT],[Corners from RIGHT],[Free Kicks Taken],[Fouled]
,[Tackled],[Final 3rd Entries],[Pen Area Entries]
from #A9 Order By matchid, clubid, playerid
Else
EXEC('Select [Player],[Match],[Team],[Date] ,' + @SelectedFields + 'From #A9 Order By matchid, clubid, playerid' )

Drop table #A9


--DEFAULT PLAYER AVERAGE
Select * Into #A10 From
(
select
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolveClubID(clubid) as 'Team',
count(matchid) as '# Matches',
--convert (varchar, matchDate, 111) as 'Date',
dbo.fn_ResolveTimeValue(@SessionID, avg(TimeOnPitch)) as 'Time On Pitch',
cast(avg(totalShotCount) as numeric(8,1)) as 'Shots',
cast(avg(onTargetShotCount) as numeric(8,1)) as 'Shots on Target',
cast((avg(totalShotCount - (onTargetShotCount + blockedShotCount))) as numeric(8,1)) as 'Shots off Target',
Avg(Shotsinsidebox) as 'Shots inside the box',
Avg(Shotsontargetinsidebox) as 'Shots on target inside the box',
Avg(Shotsoutsidebox) as 'Shots outside the box',
Avg(Shotsontargetoutsidebox) as 'Shots on target outside the box',
cast(avg(blockedShotCount) as numeric(8,1)) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(avg(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(avg(goalCount) as numeric(8,1)) as 'Goals',
cast(avg(ownGoalCount) as numeric(8,1)) as 'Own Goals',
cast(avg(offsideCount) as numeric(8,1)) as 'Offsides',
cast(avg(dribbleCount) as numeric(8,1)) as 'Dribbles',
cast(avg(crossesCount) as numeric(8,1)) as 'Crosses',
Avg(CrossesfromLEFT) as 'Crosses from LEFT',
Avg(CrossesfromRIGHT) as 'Crosses from RIGHT',
cast(avg(cornerCount) as numeric(8,1)) as 'Corners Taken',
Avg(CornersfromLEFT) as 'Corners from LEFT',
Avg(CornersfromRIGHT) as 'Corners from RIGHT',
cast(avg(freekickCount) as numeric(8,1)) as 'Free Kicks Taken',
cast(avg(fouledCount) as numeric(8,1)) as 'Fouled',
cast(avg(tackledCount) as numeric(8,1)) as 'Tackled',
cast(avg(finalThirdEntryCount) as numeric(8,1)) as 'Final 3rd Entries',
cast(avg(penAreaEntryCount) as numeric(8,1)) as 'Pen Area Entries'
from @results
--where PlayerID in (select PlayerID from TrendSelected_Players where SessionID = @SessionID)
Where PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)

and ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
group by playerid, clubid
)A10
IF @SelectedFields = ''
Select * From #A10
ELSE
EXEC ('Select [Player],[Team],[# Matches],' + @SelectedFields + 'From #A10')
Drop table #A10


--DEFAULT TEAM PER MATCH
Select * into #A11 From
(
select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, max(matchDate), 111) as 'Date',
dbo.fn_ResolveTimeValue(@SessionID, max(TimeOnPitch)) as 'Time On Pitch',
cast(sum(totalShotCount) as int) as 'Shots',
cast(sum(onTargetShotCount) as int) as 'Shots on Target',
cast((sum(totalShotCount - (onTargetShotCount + blockedShotCount))) as int) as 'Shots off Target',
Sum(Shotsinsidebox) as 'Shots inside the box',
Sum(Shotsontargetinsidebox) as 'Shots on target inside the box',
Sum(Shotsoutsidebox) as 'Shots outside the box',
Sum(Shotsontargetoutsidebox) as 'Shots on target outside the box',
cast(sum(blockedShotCount) as int) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(sum(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(sum(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(sum(goalCount) as int) as 'Goals',
cast(sum(ownGoalCount) as int) as 'Own Goals',
cast(sum(offsideCount) as int) as 'Offsides',
cast(sum(dribbleCount) as int) as 'Dribbles',
cast(sum(crossesCount) as int) as 'Crosses',
Sum(CrossesfromLEFT) as 'Crosses from LEFT',
Sum(CrossesfromRIGHT) as 'Crosses from RIGHT',
cast(sum(cornerCount) as int) as 'Corners Taken',
Sum(CornersfromLEFT) as 'Corners from LEFT',
Sum(CornersfromRIGHT) as 'Corners from RIGHT',
cast(sum(freekickCount) as int) as 'Free Kicks Taken',
cast(sum(fouledCount) as int) as 'Fouled',
cast(sum(tackledCount) as int) as 'Tackled',
cast(sum(finalThirdEntryCount) as int) as 'Final 3rd Entries',
cast(sum(penAreaEntryCount) as int) as 'Pen Area Entries'
from @results
--where ClubID in (select ClubID from TrendSelected_Teams where SessionID = @SessionID)
Where ClubID In (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))
group by clubid, matchid
)A11
IF @SelectedFields = ''
Select * from #A11
Else
EXEC('Select [Team],[Match],[Date], ' + @SelectedFields + ' From #A11 ')

Drop table #A11


--INSERT INTO SECOND TABLE FOR DEFAULT TEAM AVERAGE
insert into @results1
(clubid,matchid,timeonpitch,totalperiod,totalShotCount,blockedShotCount,onTargetShotCount,goalCount,ownGoalCount,offsideCount,dribbleCount,
crossesCount,cornerCount,freekickCount,fouledCount,tackledCount, finalThirdEntryCount, penAreaEntryCount
,Shotsinsidebox ,Shotsontargetinsidebox ,Shotsoutsidebox ,Shotsontargetoutsidebox ,
CrossesfromLEFT ,CrossesfromRIGHT , CornersfromLEFT ,CornersfromRIGHT
)
(select clubid,matchid,sum(timeonpitch),avg(totalperiod),sum(totalShotCount),sum(blockedShotCount),sum(onTargetShotCount),sum(goalCount),sum(ownGoalCount),sum(offsideCount),sum(dribbleCount),
sum(crossesCount),sum(cornerCount),sum(freekickCount),sum(fouledCount),sum(tackledCount), sum(finalThirdEntryCount), sum(penAreaEntryCount)
,Sum(Shotsinsidebox) ,Sum(Shotsontargetinsidebox) ,Sum(Shotsoutsidebox) ,Sum(Shotsontargetoutsidebox) ,
Sum(CrossesfromLEFT) ,Sum(CrossesfromRIGHT) , Sum(CornersfromLEFT) ,Sum(CornersfromRIGHT)
from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and ((@excludeGK > -1 and dbo.fn_isGoalkeeper(matchid, playerid) = 0) or
(@excludeGK = -1))
group by matchid, clubid)



--DEFAULT TEAM AVERAGE
Select * into #A12 From
(
select
dbo.fn_ResolveClubID(clubid) as 'Team',
count(dbo.fn_ResolveMatchID(matchid)) as '# of Matches',
cast( dbo.fn_ResolveTimeValue(@SessionID,max(totalperiod)) as numeric(7,2)) as 'Time On Pitch',
Cast(avg(totalShotCount) as numeric(8,1)) as 'Shots',
Cast(avg(onTargetShotCount) as numeric(8,1)) as 'Shots On Target',
cast( (avg(totalShotCount) - avg(onTargetShotCount)) as numeric(8,1) ) as 'Shots off Target',
Avg(Shotsinsidebox) as 'Shots inside the box',
Avg(Shotsontargetinsidebox) as 'Shots on target inside the box',
Avg(Shotsoutsidebox) as 'Shots outside the box',
Avg(Shotsontargetoutsidebox) as 'Shots on target outside the box',
Cast(avg(blockedShotCount) as numeric(8,1)) as 'Blocked Shots',
cast( (cast(avg(onTargetShotCount) as numeric(7,2)) / cast(nullif(avg(totalShotCount), 0) as numeric(7,2)) ) * 100 as numeric(8,1) ) as 'Shooting Accuracy',
Cast(avg(goalCount) as numeric(8,1)) as 'Goals',
Cast(avg(ownGoalCount) as numeric(8,1)) as 'Own Goals',
Cast(avg(offsideCount) as numeric(8,1)) as 'Offsides',
Cast(avg(dribbleCount) as numeric(8,1)) as 'Dribbles',
Cast(avg(crossesCount) as numeric(8,1)) as 'Crosses',
Avg(CrossesfromLEFT) as 'Crosses from LEFT',
Avg(CrossesfromRIGHT) as 'Crosses from RIGHT',
Cast(avg(cornerCount) as numeric(8,1)) as 'Corners Taken',
Avg(CornersfromLEFT) as 'Corners from LEFT',
Avg(CornersfromRIGHT) as 'Corners from RIGHT',
Cast(avg(freekickCount) as numeric(8,1)) as 'Free Kicks Taken',
Cast(avg(fouledCount) as numeric(8,1)) as 'Fouled',
Cast(avg(tackledCount) as numeric(8,1)) as 'Tackled',
Cast(avg(finalThirdEntryCount) as numeric(8,1)) as 'Final 3rd Entries',
Cast(avg(penAreaEntryCount) as numeric(8,1)) as 'Pen Area Entries'
from @results1
Where ClubID In (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
group by clubid
)A12
IF @SelectedFields = ''
Select * from #A12
Else
EXEC('Select [Team],[# of Matches], ' + @SelectedFields + ' From #A12')

Drop table #A12
if @returnPositions > -1
begin
--Positions are required...
-- Insert the positions into the @results table...
update @results
set positionID = dbo.fn_ResolvePlayersPositionSegment(matchid, playerid, 0)

update @results
set positionID = dbo.fn_ResolvePlayersPositionSegment(matchid, playerid, 1)
where positionID <= 0


select
dbo.fn_ResolveMatchID(matchid) as 'Match',
convert (varchar, matchDate, 111) as 'Date',
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolvePositionID(positionid) as 'Position',
dbo.fn_ResolveTimeValue(@SessionID, TimeOnPitch) as 'Time On Pitch',
cast(totalShotCount as int) as 'Shots',
cast(onTargetShotCount as int) as 'Shots on Target',
cast((totalShotCount - onTargetShotCount) as int) as 'Shots off Target',
cast(blockedShotCount as int) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(onTargetShotCount, 0) as numeric(7,2))/cast(nullif(totalShotCount, 0) as numeric(7,2))) * 100 as numeric(7,2))), 0) as numeric(7,2)) as 'Shooting Accuracy',
cast(goalCount as int) as 'Goals',
cast(ownGoalCount as int) as 'Own Goals',
cast(offsideCount as int) as 'Offsides',
cast(dribbleCount as int) as 'Dribbles',
cast(crossesCount as int) as 'Crosses',
cast(cornerCount as int) as 'Corners Taken',
cast(freekickCount as int) as 'Free Kicks Taken',
cast(fouledCount as int) as 'Fouled',
cast(tackledCount as int) as 'Tackled',
cast(finalThirdEntryCount as int) as 'Final 3rd Entries',
cast(penAreaEntryCount as int) as 'Pen Area Entries'
from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
and ((positionID in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
and
0 not in (select PositionID from TrendSelected_Positions where SessionID = @SessionID))
or 0 in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
or not exists (select * from TrendSelected_Positions where SessionID = @SessionID))
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))


select
dbo.fn_ResolveClubID(clubid) as 'Team',
dbo.fn_ResolvePlayerID(playerid) as 'Player',
dbo.fn_ResolvePositionID(positionid) as 'Position',
count(matchid) as '# Matches',
dbo.fn_ResolveTimeValue(@SessionID, avg(TimeOnPitch)) as 'Time On Pitch',
cast(avg(totalShotCount) as numeric(8,1)) as 'Shots',
cast(avg(onTargetShotCount) as numeric(8,1)) as 'Shots on Target',
cast(avg((totalShotCount - onTargetShotCount)) as numeric(8,1)) as 'Shots off Target',
cast(avg(blockedShotCount) as int) as 'Blocked Shots',
cast(isnull((cast((cast(nullif(avg(onTargetShotCount), 0) as numeric(7,2))/cast(nullif(avg(totalShotCount), 0) as numeric(7,2))) * 100 as numeric(7,2))),0) as numeric(7,2)) as 'Shooting Accuracy',
cast(avg(goalCount) as numeric(8,1)) as 'Goals',
cast(avg(ownGoalCount) as numeric(8,1)) as 'Own Goals',
cast(avg(offsideCount) as numeric(8,1)) as 'Offsides',
cast(avg(dribbleCount) as numeric(8,1)) as 'Dribbles',
cast(avg(crossesCount) as numeric(8,1)) as 'Crosses',
cast(avg(cornerCount) as numeric(8,1)) as 'Corners Taken',
cast(avg(freekickCount) as numeric(8,1)) as 'Free Kicks Taken',
cast(avg(fouledCount) as numeric(8,1)) as 'Fouled',
cast(avg(tackledCount) as numeric(8,1)) as 'Tackled',
cast(avg(finalThirdEntryCount) as numeric(8,1)) as 'Final 3rd Entries',
cast(avg(penAreaEntryCount) as numeric(8,1)) as 'Pen Area Entries'
from @results
where ClubID in (select ClubID from dbo.fn_GetTeamPossession(@SessionID,@startTime,@endTime,@startSegment,@endSegment,@PossessionType))
and PlayerID in (Select PlayerID from TrendSelected_Players where SessionID = @SessionID)
and ((positionID in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
and
0 not in (select PositionID from TrendSelected_Positions where SessionID = @SessionID))
or 0 in (select PositionID from TrendSelected_Positions where SessionID = @SessionID)
or not exists (select * from TrendSelected_Positions where SessionID = @SessionID))
AND ((@greater90min > -1 and AppearanceTime >= 5400) or
(@less90min > -1 and AppearanceTime < 5400) or
(@greater90min = -1 and @less90min = -1 and TimeOnPitch > 0))
group by clubid, playerid, positionid

end
end



END
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-24 : 09:04:33
I will not fix 1780 lines of code for you, for free.
The procedure above is 76428 characters (not including CrLf).

Good luck.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site