Query Delimna

I am Trying to Write a Query that calculates and
summarizes Pay from the following Tables:
Employee
EmpID
EmpName
RegularRate
OverTimeRate
Hours
Date
EmpID
Etype (1=Regular, 2=Overtime)
Hours
The Query I have been using unsuccessfully goes something
like this:
Select Employee.EmpID, Employee.EmpName,
Sum(Hours.Hours) as Hours,
sum(Hours.Hours * (Case When Hours.Etype=1 Then
Employee.RegularRate Else Employee.OverTimeRate End))
From Hours
Left Join Employee on Employee.EmpID = Hours.EmpID
Group by Employee.EmpID, Employee.EmpName
This Query will not run unless I also include the Etype,
RegularRate and OvertimeRate in my Group by statement.
When I do this, it throws off my totals.
Any Suggestions?
Thanks
Keith
Please post your DDL + INSERT statements of sample data + expected results.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:09d201c4b6a5$9a8282c0$a601280a@.phx.gbl...
I am Trying to Write a Query that calculates and
summarizes Pay from the following Tables:
Employee
EmpID
EmpName
RegularRate
OverTimeRate
Hours
Date
EmpID
Etype (1=Regular, 2=Overtime)
Hours
The Query I have been using unsuccessfully goes something
like this:
Select Employee.EmpID, Employee.EmpName,
Sum(Hours.Hours) as Hours,
sum(Hours.Hours * (Case When Hours.Etype=1 Then
Employee.RegularRate Else Employee.OverTimeRate End))
From Hours
Left Join Employee on Employee.EmpID = Hours.EmpID
Group by Employee.EmpID, Employee.EmpName
This Query will not run unless I also include the Etype,
RegularRate and OvertimeRate in my Group by statement.
When I do this, it throws off my totals.
Any Suggestions?
Thanks
Keith

Query Delimna

I am Trying to Write a Query that calculates and
summarizes Pay from the following Tables:
Employee
EmpID
EmpName
RegularRate
OverTimeRate
Hours
Date
EmpID
Etype (1=Regular, 2=Overtime)
Hours
The Query I have been using unsuccessfully goes something
like this:
Select Employee.EmpID, Employee.EmpName,
Sum(Hours.Hours) as Hours,
sum(Hours.Hours * (Case When Hours.Etype=1 Then
Employee.RegularRate Else Employee.OverTimeRate End))
From Hours
Left Join Employee on Employee.EmpID = Hours.EmpID
Group by Employee.EmpID, Employee.EmpName
This Query will not run unless I also include the Etype,
RegularRate and OvertimeRate in my Group by statement.
When I do this, it throws off my totals.
Any Suggestions?
Thanks
KeithPlease post your DDL + INSERT statements of sample data + expected results.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:09d201c4b6a5$9a8282c0$a601280a@.phx.gbl...
I am Trying to Write a Query that calculates and
summarizes Pay from the following Tables:
Employee
EmpID
EmpName
RegularRate
OverTimeRate
Hours
Date
EmpID
Etype (1=Regular, 2=Overtime)
Hours
The Query I have been using unsuccessfully goes something
like this:
Select Employee.EmpID, Employee.EmpName,
Sum(Hours.Hours) as Hours,
sum(Hours.Hours * (Case When Hours.Etype=1 Then
Employee.RegularRate Else Employee.OverTimeRate End))
From Hours
Left Join Employee on Employee.EmpID = Hours.EmpID
Group by Employee.EmpID, Employee.EmpName
This Query will not run unless I also include the Etype,
RegularRate and OvertimeRate in my Group by statement.
When I do this, it throws off my totals.
Any Suggestions?
Thanks
Keith