Query Problem

ALTER PROCEDURE GetTimeTable
(@UserName NVARCHAR(50), @WeekNo INT, @Year INT)
AS
 
IF @WeekNo IS NULL BEGIN
 
SET @WeekNo =  DATENAME(week, GETDATE())
 
END
 
IF @Year IS NULL BEGIN
 
SET @Year =  DATENAME(year, GETDATE())
 
END
 
DECLARE @tTable TABLE
(tId INT, tTimecode NVARCHAR(50), tDate DATETIME, tHours DECIMAL, tUserName NVARCHAR(50), tComments NVARCHAR(MAX), tApproved BIT)

DECLARE @hrsTable TABLE
(hrID INT, hrTimecode NVARCHAR(50), hrMonday INT, MonHrsTot INT, hrTuesday INT, TuesHrsTot INT,hrWednesday INT, WedHrsTot INT, hrThursday INT, ThursHrsTot INT, hrFriday INT, FriHrsTot INT, hrSaturday INT, SatHrsTot INT, hrSunday INT, SunHrsTot INT)

 
INSERT INTO @tTable (tId, tTimecode, tDate, tHours, tUsername, tComments, tApproved)
SELECT ID, TimeCode, Date, Hours, UserName, Comments, Approved
FROM tblHours
WHERE tblHours.UserName = @UserName AND DATENAME(week, tblHours.Date) = @WeekNo AND DATENAME(year, tblHours.Date) = @Year
 
INSERT INTO @hrsTable(hrTimecode)
SELECT DISTINCT tTimecode
FROM @tTable t
WHERE t.tUserName = @UserName
 
UPDATE @hrsTable
SET hrTuesday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Monday'
 
UPDATE @hrsTable
SET hrTuesday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Tuesday'
 
UPDATE @hrsTable
SET hrWednesday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Wednesday'
 
UPDATE @hrsTable
SET hrThursday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Thursday'
 
UPDATE @hrsTable
SET hrFriday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Friday'
 
UPDATE @hrsTable
SET hrSaturday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Saturday'
 
UPDATE @hrsTable
SET hrSunday = t.tHours
FROM @hrsTable h
JOIN @tTable t ON h.hrTimecode = t.tTimecode
WHERE DATENAME(weekday, t.tDate) = 'Sunday'

UPDATE @hrsTable
SET MonHrsTot = SUM(hrMonday)
FROM @hrsTable h

SELECT *
FROM @hrsTable

I think that its something to do with the following lines:

UPDATE @hrsTable
SET MonHrsTot = SUM(hrMonday)
FROM @hrsTable h

The error that I'm getting is:

"An aggregate may not appear in the set list of an UPDATE statement"

Would appriciate any help that you might have..

Thanks,

StellyStart Free Trial