8
I have multiple Playerid.. (1 to 37) how can i incorperate this into the stored procedure.. I could hard code all 37 but when an increase is players i would have to modify every single time.
Below is the stored procedure i have create..
CREATE Procedure sp_influenceTEST
@PlayerID int
As
select gameid as gameidIn, result as resultIn
into #Ingameid
from results
where playerid = @PlayerID
order by gameid asc
select *
into #Inallgames
from #Ingameid
left join results
on #Ingameid.gameidIn = results.gameid and #Ingameid. resultIn = results.result
order by #Ingameid.gameidIn
create table #Inall (playerid decimal(5), allgame decimal(5))
insert into #Inall
select #Inallgames.playerid, count(#Inallgames.playerid) as count
from #Inallgames
where #Inallgames.playerid <> @PlayerID
group by #Inallgames.playerid
order by count desc
create table #Inwon (playerid decimal(5), wongames decimal(5))
insert into #Inwon
select #Inallgames.playerid, count(#Inallgames.playerid) as count
from #Inallgames
where #Inallgames.playerid <> @PlayerID
and #Inallgames.result = 1
group by #Inallgames.playerid
order by count desc
create table #Inallwon (playerid decimal(5), wongame decimal(5), allgames decimal(5))
insert into #Inallwon
select #Inall.playerid, #Inwon.wongames, #Inall.allgame
from #Inall
left join #Inwon
on #Inall.playerid = #Inwon.playerid
select #Inallwon.wongame, #Inallwon.allgames, wongame / allgames as percentage
into #InallwonP
from #Inallwon
order by percentage desc
select sum(percentage) as InfluenceValue into #influenceValue from #InallwonP
select sum(allgames) as allgames into #Influenceallgames from #Inallwon
create table #InfluencePlayerID (playerid decimal(5))
insert #InfluencePlayerID values (@PlayerID)
create table #Influence (Playerid decimal(5), Allgames decimal (5), InfluenceValue float (5))
insert into #Influence
select #InfluencePlayerID.playerid, #Influenceallgames.allgames, #InfluenceValue.influencevalue
from #InfluencePlayerID, #Influenceallgames, #InfluenceValue
select *
from #influence
loop
GO
