Stored Procedure help

 
Using these tables:
[table1 - this table is being added to by other applications]
movieID  int 
releaseStateID  int 
rating  int 
swid  varchar 
processed varchar
 
[table2 - this table is only updated by this new procedure]
movieRatingID (ID)  int 
releaseStateID (key)  int 
movieID (key)  int 
rating  int 
totalRatingsCount  int 
ratingZeroCount  int 
ratingOneCount  int 
ratingTwoCount  int 
ratingThreeCount  int 
ratingFourCount  int 
ratingFiveCount  int 
 
I need to construct a stored procedure that does the following:
 
1) set all rows in table1 to 'processed = Y'  (since more records might be added during this process, I want to mark the ones I am working on so I can delete them afterwards)  
 
2) using only the rows with 'processed = Y' from table1,  select as follows 
    a) swid must be unique.  if more than one entry contains the same swid, then ignore all entried except the most recent.
    b) ignore entries with a null value for swid 
    c) group records by like movieId, releaseStateId and rating ... and get a count for each grouping
 
3) table2 is updated with the data selected from table1 as follows
    a) a record in table1 is a match for a record in table2 if both the movieID and releaseStateID match.  If no match is found then a new record is created in table2.
    b) for every updated record in table2, the totalRatingsCount is incremented by one
    c) table1 ratings field will containe a value of 0-5 , and will determine which rating count in a table2 record will be incremented.  a value of 0 increments the table2 record's ratingZeroCount by one, a rating of 1 increments the ratingOneCount by one, and so on.
   
4) table1 records marked 'processed = Y' are removed/deleted.
 
+++++++++
 
Basically what is happening is that an application is logging 'raw' votes to table1.  table2 is where the processed vote/ratings data exists and is being served to tea templates for diplay to the user.  This stored procedure is going to filter duplicate votes from table1 and tally them into table2.  The part I cannot get a grip on is how to take the rating from a table1 record (a value of 0-5), and add that as an appropriate votecount to table2.  There are procedures in place on the templating end that will restrict users from voting more than once for a single movie, this stored procedure is targeting ballot stuffing attacks.  I do not expect there to be duplicate votes (duplicates of swid/movieID/stateID) unless there is an attack of some sort.
 
This procedure will be something more advanced than I have done in the past and so I'm looking for advice.  I am not sure how difficult this will be to construct.
 
Comments appreciated.
 
Here is the rough outline I have thus far ...
 
 
+++++ updated - rough draft written so far +++++
 
 
/*  Will Merydith                    */
/*  Created: 08/11/04 3:00PM         */
/*  Revised:                         */
 

CREATE PROCEDURE dbo.spLoadMovieRatings
 
/* 
 *  Flag all records as being processed 
 */
 
AS
 
UPDATE [movie_ratings].[dbo].[VoteLog] 
 
SET [processed] = 'Y' 
 
GO
 
/* 
 *  Select all records with a unique swid, and group them into 
 *  like ratings for a movie release 
 */
 
UPDATE MovieRating  /* will an update also add new records if table2 has no record for a new movieId/releaseStateId?   */
SELECT  movieId, releaseStateId, rating, DISTINCT swid, COUNT(*) as total 
FROM VoteLog
WHERE processed = 'Y' AND swid IS NOT NULL
GROUP BY movieId, releaseStateId, rating
CASE rating 
 
    /* not sure how to grab the count for each grouping and then increment the vote counts in table 2 */ 
    WHEN 0 THEN /* update MovieRating.totalVoteCount and MovieRating.zeroVoteCount */
    WHEN 1 THEN /* update MovieRating.totalVoteCount and MovieRating.voteOneCount */
    WHEN 2 THEN /* update MovieRating.totalVoteCount and MovieRating.voteTwoCount */
    WHEN 3 THEN /* update MovieRating.totalVoteCount and MovieRating.voteThreeCount */
    WHEN 4 THEN /* update MovieRating.totalVoteCount and MovieRating.voteFourCount */
    WHEN 5 THEN /* update MovieRating.totalVoteCount and MovieRating.votezeroVoteCount */ 
    
/* 
 *  (join with statement above)  ... 
 */

LEFT OUTER JOIN MovieRating
ON VoteLog.movieId = MovieRating.movieId AND VoteLog.releaseStateId = MovieRating.releaseStateId
GO
+++++