lw_groupRollup.sql

If Exists (Select * From sysobjects Where name = N'lw_groupRollup' And user_name(uid) = N'dbo')
    Drop Procedure dbo.lw_groupRollup
Go

CREATE Procedure lw_groupRollup(
   @groupId int 
 )
 As
   declare @personId int 
   declare @atype_weight int 
   declare @totalWeighting int
   declare @rollUpScore float
   declare @finalScore float
   declare groups cursor read_only fast_forward local
    for select distinct p.personId  from person p join 
       personGroup pg on p.personId = pg.personId 
         where pg.groupId = @groupId 
   
    open groups
    fetch from groups into @personId
    While @@FETCH_STATUS = 0
     begin
        exec lw_personRollup @personId,@groupId
-- start
    select @totalWeighting  = sum(atype.weight) from activityType atype
            join personRollup pr on atype.activityTypeId = pr.activityTypeId
              where pr.groupId = @groupId and pr.personId= @personId 
    declare totalweightings cursor read_only fast_forward local
       for select pr.activityTypeAvg, atype.weight from personRollup pr
          join activityType atype on pr.activityTypeId = atype.activityTypeId
            where pr.groupId = @groupId and pr.personId = @personId
     open totalweightings
     fetch from totalweightings into @finalScore,@atype_Weight
      set @rollUpScore  = 0
      While @@FETCH_STATUS = 0 
       begin
         set @rollupScore = @rollupScore + round(( @finalScore * @atype_weight / @totalWeighting),2)
         fetch next from totalweightings into @finalScore,@atype_weight
       end
     print 'rollup score for personid ' + cast(@personId as varchar(4)) + 
            ' and for groupId ' + cast(@groupId as varchar(4)) +
             ' is = ' + cast(@rollUpscore as varchar(5)) 
     close totalweightings
     deallocate totalweightings
     set @totalWeighting  = 0
-- end
     fetch next from groups into @personId
     end
    
    close groups
    deallocate groups
    return 0

Go