Summing multiple lines together

[acesn8s]

I am trying to take multiple rows of a person and their time worked and turn it into one line with their total time worked for a given date range. At this point I am able to get the different clock in and out times for the day and turn that into HoursWorked for the day but I need to add those together to see if they have enough hours for the week and show it as one row. Any help would be appreciated.

SELECT Records.Employee, Records.HoursWorked, Records.TimeDate
FROM (SELECT employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, SUM(TimePeriod)
AS HoursWorked
FROM (SELECT EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
FROM dbo.Time_Record RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name)
AS TimeRecords
GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON Records.Employee = EmpMaster.employee_account_name
WHERE (EmpMaster.supervisor <> 'Y') AND (EmpMaster.time_end_date IS NULL) AND (Records.TimeDate >= @startDate) AND
(Records.TimeDate <= @endDate)

 

[RiverGuy]

Without looking at your query, all you should need to do is a T-SQL statement against another subquery. Try this.

SELECT Employee, SUM(HoursWorked) AS TotalHoursWorked
FROM

(SELECT Records.Employee, Records.HoursWorked, Records.TimeDate
FROM (SELECT employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, SUM(TimePeriod)
AS HoursWorked
FROM (SELECT EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
FROM dbo.Time_Record RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name)
AS TimeRecords
GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
dbo.Employee_Master AS EmpMaster ON Records.Employee = EmpMaster.employee_account_name
WHERE (EmpMaster.supervisor <> 'Y') AND (EmpMaster.time_end_date IS NULL) AND (Records.TimeDate >= @startDate) AND
(Records.TimeDate <= @endDate)) details

 

[acesn8s]

Thank you for your help RiverGuy.