This works for me.
SELECT ID,
CASE WHEN Category IN( 'Art', 'PE') THEN 'Non Educational' ELSE Category END Category,
DATEADD( dd, -1, DATEADD(wk, DATEDIFF(wk,0,Date), 0)) Week,
SUM(Hours) Total_Hours
FROM Attendance
GROUP BY ID,
CASE WHEN Category IN( 'Art', 'PE') THEN 'Non Educational' ELSE Category END ,
DATEADD( dd, -1, DATEADD(wk, DATEDIFF(wk,0,Date), 0))
However, I'm not sure about your second requirement. It seems that you might need a running total calculation. For information on this article Solving the Running Total and Ordinal Rank Problems[/url]