CODEWITHSUNDEEP
sqlstored-procedures
user2988649
user2988649

Reputation: 323

Stored procedure to update temp table based on Date in SQL Server

I have two tables :

Teams [Id],[name],[nomatches],[owngoals],[othergoals],[points]

and

Matches[id],[homeid],[outid],[homegoal],[outgoal],[matchdate]

I have a trigger which fires on INSERT,UPDATE,DELETE so the Teams table current score table is always updated.

Example :

Select * from teams;

Result :

Name     NumberOfMatches   OwnGoals  OtherGoals  Points
-------------------------------------------------------    
FC Chelsea      33            61          22        68
FC Barcelona    33            46          34        59
FC Man UD       33            57          50        52

The problem:

Table Matches has a column matchdate. I want to recalculate the current score table (with my trigger maybe) for all games played before the entered date.

I don't know how to create a temp table to store the re-calculated data (nomaches, owngoals, othergoals, points for each team) based on the Date parameter.

What I have so far :

CREATE PROCEDURE check_scoretable  
( 
    @pDate DATE = NULL
)
as
DECLARE @Date DATE = COALESCE(@pDate,GETDATE())
    declare @homeid char(3);
    declare @outid char(3);
    declare @id int;

        SELECT * INTO #temp_table2  FROM teams;
        SET NOCOUNT ON; -- copy of the teams table 

declare cur CURSOR LOCAL for 
select homeid, outid
from matches where matches.matchdate < @Date
 open cur
 fetch next from cur into @homeid, @outid
 while @@FETCH_STATUS = 0 BEGIN
    select @homeid;
    select @outid;
    --Increment number of matches
    update #temp_table2 set #temp_table2.nomatches = #temp_table2.nomatches+1 where #temp_table2.id = @homeid;
    update #temp_table2 set #temp_table2.nomatches = #temp_table2.nomatches+1 where #temp_table2.id = @outid;

   fetch next from cur into @homeid, @outid
END
close cur
deallocate cur

-- Test the stored procedure
DECLARE @d DATETIME
SELECT @d = GETDATE()
EXEC check_scoretable  @date = @d

Upvotes: 0

Views: 1667

Answers (1)

Sparky
Sparky

Reputation: 15105

You can write a stored procedure, like you've done and pass the date to it. 

CREATE PROCEDURE check_scoretable  
( 
    @pDate DATE = NULL
)
as

However, rather than a cursor, do something like

SELECT tm.name,sum(tm.noMatches) as NumberMatches,sum(tm.ownGoals) as OwnGoals,
       sum(tm.otherGoals) as Othergoals,sum(tm.Points) as Points
FROM Team tm
JOIN Matches mc on mc.homeId=tm.id or mc.outId=tm.id
WHERE mc.matchDate <= @pDate

This will give you the results you are looking for.

CAVEAT: Your database design is not good, because of the redundant data in it. For example, you are tracking the number of matches in the team table, when you can compute the number of matches by 

SELECT count(*) FROM matches WHERE homeId=@id or OutId=@id

Same type of operation for total goals, etc.

The problem you might run into is, if for some reason, the team record is not updated, the number of matches listed in team could be different than the number of matches from totaling up the matches played.

Upvotes: 1

Related Questions