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

---

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;