SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 is this possible
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robcpettit
Starting Member

United Kingdom
9 Posts

Posted - 09/10/2013 :  11:06:32  Show Profile  Reply with Quote
I have to typical table of football results,date, hometeam, awayteam, htresult, atresult. I would like to know if it possible to add two further columns that, (this is hard to explain),..
at the start of the season each team is allocated 20 points. Before a match is played the hometeam minuses the awayteams allocated points. When the the match is played points are added to the allocated points depending on the outcome. eg team1 v team2, first game so match points = 0,
team1 wins so team1 points are now 21, team2 19. team1 v team2 again so match points now 2. Is there away of doing this on the fly with a table to show the match points and possible the teams overall points. I can do regards robert

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/10/2013 :  12:31:09  Show Profile  Reply with Quote
quote:
Originally posted by robcpettit

I have to typical table of football results,date, hometeam, awayteam, htresult, atresult. I would like to know if it possible to add two further columns that, (this is hard to explain),..
at the start of the season each team is allocated 20 points. Before a match is played the hometeam minuses the awayteams allocated points. When the the match is played points are added to the allocated points depending on the outcome. eg team1 v team2, first game so match points = 0,
team1 wins so team1 points are now 21, team2 19. team1 v team2 again so match points now 2. Is there away of doing this on the fly with a table to show the match points and possible the teams overall points. I can do regards robert

I must admit I didn't follow your explanation. But, seems like based on the number of games won/lost, each team gets points given to them or taken away. If that is the case, it should be possible to do.

"Before a match is played the hometeam minuses the awayteams allocated points" What does this mean?
And also this: "team1 v team2 again so match points now 2"
Go to Top of Page

robcpettit
Starting Member

United Kingdom
9 Posts

Posted - 09/10/2013 :  14:31:51  Show Profile  Reply with Quote
thankyou for your reply. It is like that to a degree. The important part and the the bit i cant seem to do is that before the match is played, hometeam needs to deduct/takeaway awayteam points(prev points). I thinking about using either a second table or temp table with the team names and a column for my points starting a 20, then loop through my results 1 row at a time referencing the team table to calculate the points for win, loose or draw. Sounds messy.
Regards Robert
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

276 Posts

Posted - 09/10/2013 :  18:18:26  Show Profile  Reply with Quote
Well, I'm not sure, I quiet understand the point system described here, but this is my attempt (bare in mind, this is not guaranteed to Work if a team play twice on the same day):

create table #football_results (date date,hometeam varchar(10),awayteam varchar(10),htresults int,atresults int,hmatch int,amatch int);
insert into #football_results (date,hometeam,awayteam,htresults,atresults,hmatch,amatch)
   values ('01-01-2013','team1','team2',2,1,null,null)
         ,('01-01-2013','team3','team4',0,1,null,null)
         ,('02-01-2013','team1','team3',0,0,null,null)
         ,('03-01-2013','team4','team1',1,3,null,null)
         ,('03-01-2013','team3','team2',1,1,null,null)
         ,('04-01-2013','team1','team4',0,1,null,null)
         ,('05-01-2013','team3','team1',2,0,null,null)
         ,('06-01-2013','team2','team4',3,1,null,null)
         ,('07-01-2013','team4','team2',2,3,null,null)
         ,('08-01-2013','team2','team1',1,1,null,null)
         ,('09-01-2013','team4','team3',0,1,null,null)
         ,('10-01-2013','team2','team3',1,2,null,null)
;
select distinct team
               ,20 as points
  into #team_points
  from (select hometeam as team
          from #football_results
        union all
        select awayteam as team
          from #football_results
       ) as a
;

declare @rowcount int,@row int;
select @rowcount=count(*) from #football_results;
set @row=0;
while @row<@rowcount
begin
   set @row+=1;
   update b
      set b.hmatch=c.points
         ,b.amatch=d.points
     from (select row_number() over (order by date,hometeam,awayteam) as row
                 ,date
                 ,hometeam
                 ,awayteam
             from #football_results
          ) as a
          inner join #football_results as b
                  on b.date=a.date
                 and b.hometeam=a.hometeam
                 and b.awayteam=a.awayteam
          inner join #team_points as c
                  on c.team=b.hometeam
          inner join #team_points as d
                  on d.team=b.awayteam
    where a.row=@row
   ;
   update c
      set c.points=c.points
                  +case
                      when b.hmatch=b.amatch then 1
                      else abs(b.hmatch-b.amatch)
                   end
                  *case
                      when b.htresults<b.atresults then -1
                      when b.htresults>b.atresults then 1
                      else 0
                   end
     from (select row_number() over (order by date,hometeam,awayteam) as row
                 ,date
                 ,hometeam
                 ,awayteam
             from #football_results
          ) as a
          inner join #football_results as b
                  on b.date=a.date
                 and b.hometeam=a.hometeam
                 and b.awayteam=a.awayteam
          inner join #team_points as c
                  on c.team=b.hometeam
    where a.row=@row
   ;
   update c
      set c.points=c.points
                  +case
                      when b.hmatch=b.amatch then 1
                      else abs(b.hmatch-b.amatch)
                   end
                  *case
                      when b.htresults>b.atresults then -1
                      when b.htresults<b.atresults then 1
                      else 0
                   end
     from (select row_number() over (order by date,hometeam,awayteam) as row
                 ,date
                 ,hometeam
                 ,awayteam
             from #football_results
          ) as a
          inner join #football_results as b
                  on b.date=a.date
                 and b.hometeam=a.hometeam
                 and b.awayteam=a.awayteam
          inner join #team_points as c
                  on c.team=b.awayteam
    where a.row=@row
   ;
end;

select * from #football_results order by date;
select * from #team_points order by team;

drop table #team_points;
drop table #football_results;

ps: Yeah, I know it's ugly, but it seems to do the job :-)
Go to Top of Page

robcpettit
Starting Member

United Kingdom
9 Posts

Posted - 09/12/2013 :  13:45:42  Show Profile  Reply with Quote
Thankyou for your reply and taking the the time to type up the code. As a beginner there lots here for me to learn from. Very much appreciated.
Regards Robert
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000