January 3, 2007 at 2:32 pm
Hi ,
I have the following query and after several hours I still can't get the correct syntax to get the AVG and SUM of the DeductionHrlyRate(HrlyRate). Any help is greatly appreciated.
SELECT
[empsal1].[EmpNum] ,
[empsal2].[EmpNum] AS EmpNum2 ,
[e].[WCClass] ,
[empsal1].[Salary] ,
[e].[Shift] ,
[empsal1].[HrlyRate] AS NoDeductionHrlyRate ,
CASE
WHEN ([e].[Shift] = '2nd') THEN ([empsal1].[HrlyRate] - 1.000)
ELSE ([empsal1].[HrlyRate])
END AS DeductionHrlyRate ,
[e].[TermDate] ,
[e].[EmpType] ,
[empsal1].[JobDate] ,
[empsal1].[PayFreq] ,
[empsal1].[Annual] ,
[empsal1].[SalDate] ,
[empsal1].[Key] ,
[empsal1].[SalPeriod] ,
[empsal1].[ReasonCode]
FROM
[dbo].[EmpSalary] AS empsal1
INNER JOIN
(
SELECT
DISTINCT [empsal].[EmpNum] ,
MAX([empsal].[SalDate]) AS MaxSalDate
FROM
[dbo].[EmpSalary] AS empsal
GROUP BY
[empsal].[EmpNum]
) AS empsal2
ON [empsal1].[EmpNum] = [empsal2].[EmpNum]
AND [empsal1].[SalDate] = [empsal2].[MaxSalDate]
INNER JOIN
[dbo].[Employee] AS e
ON [empsal1].[EmpNum] = [e].[EmpNum]
INNER JOIN
[dbo].[TblEmpPos] AS ep
ON [e].[EmpNum] = [ep].[EmpNum]
WHERE
[e].[WCClass] = 'WCP'
AND [e].[TermDate] IS NULL
AND [e].[EmpType] = 'H'
AND SUBSTRING([ep].[JobID],1,2) <> 'PT'
AND SUBSTRING([ep].[JobID],1,2) <> 'TP'
ORDER BY
[e].[Shift] ,
[empsal1].[EmpNum]
Thanks!
January 3, 2007 at 3:07 pm
Have you tried dropping the the distinct from the derived table?
January 3, 2007 at 3:09 pm
The Distinct on the derived table is superfluous.
Without a bit of more info on where are you trying to get the AVG and SUM from it will be difficult to guess what you are really after.
* Noel
January 3, 2007 at 3:11 pm
DOH you are right I am already doing that with the Max(SalDate)
I thought I needed the Distinct because in the EmpSalary table there is an entry for each raise an employee got. So I needed to grab the last raise given.
January 3, 2007 at 3:24 pm
I am trying to get the the AVG of the HourlyRate for all hourly employees but I need to use the deductionHrlyRate because the individual rates must subtract 1 if 2nd shift.
The AVG is the SUM of all individual HrlyRates / HeadCount or in this case the total number of HourlyEmployees.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply