Help adding multiple fields together 1

[Jdbenike]

DECLARE @WEDate smalldatetime, @Dist varchar(6)

SET @WEDate = '12/06/2008'
SET @Dist = 'D12'

SELECT HomeLaborLevelName2, PERSONNUM, PersonFullName, HomeLaborLevelName3, HomeLaborLevelName5, HomeLaborLevelDsc5 as Jobtitle,
       MAX(CASE WHEN DOW = 'Sunday' THEN Schedule ELSE '' END) AS SundayDate,
	   MAX(CASE WHEN DOW = 'Sunday' Then ScheduleTime ELSE '' END) AS SundayTime,
       MAX(CASE WHEN DOW = 'Monday' THEN Schedule ELSE '' END) AS MondayDate,
	   MAX(CASE WHEN DOW = 'Monday' Then ScheduleTime ELSE '' END) AS MondayTime,
       MAX(CASE WHEN DOW = 'Tuesday' THEN Schedule ELSE '' END) AS TuesdayDate,
       MAX(CASE WHEN DOW = 'Tuesday' Then ScheduleTime ELSE '' END) AS TuesdayTime,
       MAX(CASE WHEN DOW = 'Wednesday' THEN Schedule ELSE '' END) AS WednesdayDate,
       MAX(CASE WHEN DOW = 'Wednesday' Then ScheduleTime ELSE '' END) AS WednesdayTime,
       MAX(CASE WHEN DOW = 'Thursday' THEN Schedule ELSE '' END) AS ThursdayDate,
       MAX(CASE WHEN DOW = 'Thursday' Then ScheduleTime ELSE '' END) AS ThursdayTime,
       MAX(CASE WHEN DOW = 'Friday' THEN Schedule ELSE '' END) AS FridayDate,
       MAX(CASE WHEN DOW = 'Friday' Then ScheduleTime ELSE '' END) AS FridayTime,
       MAX(CASE WHEN DOW = 'Saturday' THEN Schedule ELSE '' END) AS SaturdayDate,
       MAX(CASE WHEN DOW = 'Saturday' Then ScheduleTime ELSE '' END) AS SaturdayTime
       
FROM
(SELECT CT.HomeLaborLevelName2, CT.PERSONNUM, CT.PersonFullName, CT.HomeLaborLevelName3,CT.HomeLaborLevelName5,CT.HomeLaborLevelDsc5, CALENDARDTM,
   DOW=DATENAME(dw, CalendarDTM),
   Schedule=ISNULL(RIGHT(CONVERT(varchar(20), ShiftStartDate),7)+'-'+
            RIGHT(CONVERT(varchar(20), ShiftEndDate),7), 'OFF'),
   ScheduleTime=ISNULL(datediff(second, shiftstartdate, shiftenddate), 0)
 FROM
  (SELECT HomeLaborLevelName2, PERSONNUM, PersonFullName, HomeLaborLevelName3,HomeLaborLevelName5,HomeLaborLevelDsc5, CALENDARDTM 
   FROM Calendar, 
    (SELECT HomeLaborLevelName2, PERSONNUM, PersonFullName, HomeLaborLevelName3,HomeLaborLevelName5,HomeLaborLevelDsc5
     FROM VP_Person
     WHERE 
       HomeLaborLevelName2 = @Dist AND EmploymentStatus = 'Active' AND Homelaborlevelname5 IN ('1000','1001','1010','1012','1013','1017','1040', '1042','1045','1100','1105','1113','1126','1136','1185','1194','1209',	
'1231','1260','1330','1351','1567','1573','1576','1577','1581',	'1582',	'1583',	'1584','1586',	'1587',	'1601',	
'1681','1708','1709','1889','1890','1898','1900','1901','1911',	'1912','1928','2010','2037','2181','2183','2214',
'2266',	'2273','2335',	'2336',	'2337',	'2338',	'2339','2340','2341',	'2342',	'2343',	'2350','2360','2361',	'2362',	
'2363','2369',	'2370',	'2371',	'2381',	'2382',	'2383',	'2384')) as PTbl
   WHERE CALENDARDTM between DATEADD(dd, -6, @WEDate) and @WEDate) as CT 
 LEFT OUTER JOIN VP_Schedule
   ON SHIFTSTARTDATE between DATEADD(dd, -6, @WEDate) and DATEADD(mi, 1439, @WEDate) AND
      CONVERT(datetime, CONVERT(varchar(12), ShiftStartDate, 1)) = CalendarDTM AND 
      CT.PersonNUM = VP_Schedule.PERSONNUM AND
      VP_Schedule.HomeLaborLevelName2 = @Dist) as Sched
GROUP BY PERSONNUM, PersonFullName, HomeLaborLevelName3, HomeLaborLevelName2, HomeLaborLevelName5,HomeLaborLevelDsc5
ORDER BY HomeLaborLevelName3, PersonNum

In the top part of this code where I declare the variables. SundayTime,MondayTime,TuesdayTime... ect. It brings back a value in seconds for their scheduled shift.
How do I bring back a total for the week by adding all those 7 values together Sun thru saturday? Not sure what part of the code I would try and do that.

Thanks ahead of time.

This report grabs a persons schedule based on the week ending date for 7 days.

 

[RiverGuy]

The easiest to code would be to just wrap that whole query (minus the variable declarations and value assignments) in another derived table (subquery), and add up those output columns:

SELECT *,
TotalTime = SundayTime + MondayTime + TuesdayTime + WednesdayTime + ThursdayTime + FridayTime + SaturdayTime
FROM
  (<YourQueryHere>) tbl
ORDER BY <Your Order By Clause Here>