lw_getScores.sql

-- RETURN: recordset of scores by person/group
ALTER PROC lw_getScores;1(
   @personId int,
   @groupId int
) AS
select g.[name] as Grade,pa.* from personActivity pa
      join grade g on pa.gradeId=g.gradeId
      join activity a on pa.activityId=a.activityId
   where pa.personId=@personId and a.groupId=@groupId
   order by pa.dateOfActivity desc
go

-- RETURN: recordset of scores by activity
CREATE PROC lw_getScores;2(
   @activityId int
) AS
select g.[name] as Grade,pa.* from personActivity pa
         join grade g on pa.gradeId=g.gradeId
      where pa.activityId=@activityId
      order by pa.dateOfActivity
go

-- RETURN: recordsets of scores for all groups memberships of person
ALTER PROC lw_getAllScores;1(
   @personId int
) AS
declare @groupId int
declare groups cursor read_only fast_forward
   for select pg.groupId from personGroup pg
         join [group] g on pg.groupId=g.groupId
      where pg.personId=@personId order by g.period
open groups
fetch from groups into @groupId
while @@FETCH_STATUS=0
begin
   exec lw_getScores;1 @personId,@groupId
   fetch next from groups into @groupId
end
close groups
deallocate groups
go

-- RETURN: recordsets of scores for all members of group
ALTER PROC lw_getAllScores;2(
   @groupId int
) AS
declare @personId int
declare persons cursor read_only fast_forward
   for select pg.personId from personGroup pg
         join [person] p on pg.personId=p.personId
      where pg.groupId=@groupId
      order by p.lastName,p.firstName
open persons
fetch from persons into @personId
while @@FETCH_STATUS=0
begin
   exec lw_getScores;1 @personId,@groupId
   fetch next from persons into @personId
end
close persons
deallocate persons
go

-- RETURN: recordsets of scores for all activities of group
ALTER PROC lw_getAllScores;3(
   @groupId int
) AS
declare @activityId int
declare activities cursor read_only fast_forward
   for select a.activityId from activity a
      where a.groupId=@groupId
      order by a.dueDate
open activities
fetch from activities into @activityId
while @@FETCH_STATUS=0
begin
   exec lw_getScores;2 @activityId
   fetch next from activities into @activityId
end
close activities
deallocate activities
go

-- TESTING
exec lw_getScores 2,3
exec lw_getScores;2 19
exec lw_getAllScores 3
exec lw_getAllScores;2 4
exec lw_getAllScores;3 3