[yadis]

Ovanstående nu löst
Så här blev det till slut om det intresserar nån:

SELECT Us.UserID, GroupRules.DistanceEachWeek,
IsNull(
( SELECT SUM(distance)
FROM Training
WHERE UserID = Us.UserID
AND GroupID = 11
AND Training.TrainingDate >= dbo.F_START_OF_WEEK(DateAdd(day,-7,GetDate()),2)
AND Training.TrainingDate <= dbo.F_END_OF_WEEK(DateAdd(day,-7,GetDate()),2)
) , 0) AS TotDistance
FROM [User] Us
JOIN [UserGroup] UsGrp ON Us.UserID = UsGrp.UserID
JOIN [Group] Grp on UsGrp.GroupID = Grp.GroupID
JOIN [GroupRules] on Grp.GroupID = [GroupRules].GroupID
WHERE Grp.GroupID = 11