Summing multiple lines together

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Summing multiple lines together

Author

Topic

sdr
Starting Member

8 Posts

Posted - 2008-10-15 : 08:16:50

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)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 09:06:00

Somewhat easier to read too...

SELECT		em.employee_account_name AS Employee,
		SUM(DATEDIFF(SECOND, tr.start_date_time, tr.end_date_time) / 3600.0) AS HoursWorked
FROM		dbo.Employee_Master AS em
LEFT JOIN	dbo.Time_Record AS tr ON tr.employee_account_name = em.employee_account_name
			AND tr.TimeDate >= @startDate
			AND tr.TimeDate <= @endDate
WHERE		em.supervisor <> 'Y'
		AND em.time_end_date IS NULL
GROUP BY	em.employee_account_name

E 12°55'05.63"
N 56°04'39.26"

sdr
Starting Member

8 Posts

Posted - 2008-10-15 : 11:15:47

Awsome thank you very much Peso. I am new to SQL and have inherited some projects that are driving me crazy. I am trying to learn SQL as I go and I think I have found just the place to learn. One more thing, how would I go about getting only the records where the HoursWorked are < 35?

When I tried it in the WHERE clause it gave me an error that HoursWorked is an invalid column name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:27:34

quote:
Originally posted by sdr

Awsome thank you very much Peso. I am new to SQL and have inherited some projects that are driving me crazy. I am trying to learn SQL as I go and I think I have found just the place to learn. One more thing, how would I go about getting only the records where the HoursWorked are < 35?

When I tried it in the WHERE clause it gave me an error that HoursWorked is an invalid column name.

HoursWorked is an alias and you cant use alias created directly in where clause. For using this in where just make alias a column by form a derived table out of query and then use it in where

SELECT Employee,HoursWorked
FROM
(SELECT		em.employee_account_name AS Employee,
		SUM(DATEDIFF(SECOND, tr.start_date_time, tr.end_date_time) / 3600.0) AS HoursWorked
FROM		dbo.Employee_Master AS em
LEFT JOIN	dbo.Time_Record AS tr ON tr.employee_account_name = em.employee_account_name
			AND tr.TimeDate >= @startDate
			AND tr.TimeDate <= @endDate
WHERE		em.supervisor <> 'Y'
		AND em.time_end_date IS NULL
GROUP BY	em.employee_account_name
)t--This is the derived table formed out of query
WHERE HoursWorked < 35

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 11:31:54

Or in a HAVING clause

SELECT		em.employee_account_name AS Employee,
		SUM(DATEDIFF(SECOND, tr.start_date_time, tr.end_date_time) / 3600.0) AS HoursWorked
FROM		dbo.Employee_Master AS em
LEFT JOIN	dbo.Time_Record AS tr ON tr.employee_account_name = em.employee_account_name
			AND tr.TimeDate >= @startDate
			AND tr.TimeDate <= @endDate
WHERE		em.supervisor <> 'Y'
		AND em.time_end_date IS NULL
GROUP BY	em.employee_account_name
HAVING		SUM(DATEDIFF(SECOND, tr.start_date_time, tr.end_date_time) / 3600.0) < 35

E 12°55'05.63"
N 56°04'39.26"

sdr
Starting Member

8 Posts

Posted - 2008-10-15 : 11:48:44

Sweet both ways work.

Let me make sure I understand what is going on here...
visakh16's way has me selecting the fields I want from a table that was created in the original query and placing a WHERE clause on the records that I now want to pull out of it.

Peso's way adds a HAVING clause to the existing query saying only to return the records that meet my criteria. So HAVING is another form of a WHERE clause?

So anytime I want to include a field that has been given an alias I will need to use one of the methods you guys have provided.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 11:50:30

They work in different ways.

WHERE filters out the records from the table, directly.
HAVING waits and when all records are fetched and grouped then HAVING is applied.

E 12°55'05.63"
N 56°04'39.26"

sdr
Starting Member

8 Posts

Posted - 2008-10-15 : 12:01:52

quote:
Originally posted by Peso

They work in different ways.

WHERE filters out the records from the table, directly.
HAVING waits and when all records are fetched and grouped then HAVING is applied.

E 12°55'05.63"
N 56°04'39.26"

So the reason the HAVING clause won't work in the WHERE clause is that the calculation hasn't been finished at that point?

Sorry to ask so many questions but I want to make sure I understand what is going on in the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 12:40:58

quote:
Originally posted by sdr

quote:
Originally posted by Peso

They work in different ways.

WHERE filters out the records from the table, directly.
HAVING waits and when all records are fetched and grouped then HAVING is applied.

E 12°55'05.63"
N 56°04'39.26"

So the reason the HAVING clause won't work in the WHERE clause is that the calculation hasn't been finished at that point?

Sorry to ask so many questions but I want to make sure I understand what is going on in the code.

You cant use aggregate functions in WHERE clause

sdr
Starting Member

8 Posts

Posted - 2008-10-15 : 13:42:27

Ah I get it now. Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources