Nested Select on two tables where one table has many rows for one unique item
Posted on 2007-07-24
Need help on a joining of data on two tables. I tried a nested select but didn't produce the results I require.
To report on payroll timesheets by employee
There are two tables employees and timecard_raw.
Now the problem. It seems the vendor used the employee table as an audit table as well. Each employee is record more than one for any changes to their profile.
Joe NULL Smith
Joseph NULL Smith
Joseph P Smith
I created this query to retrieve just one row of the data, I used the audit_timestamp and ISNULL to determine the correct unique row.
MAX(DATEADD (mi, audit_timestamp,'01/01/1930')) AS audit_timestamp
where serial_number = '000002074'
and (first_name IS NOT NULL AND
middle_name IS NOT NULL AND
last_name IS NOT NULL)
group by first_name,middle_name,last_name
My second query is the employee (based by serial number) timecard input with a calculated column to convert VARCHAR into a monetary integer figure
CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Converted'
Ceridian.dbo.timecard_raw AS A
A.serial_number = '000002074'
the 'Hours Converted' calculated column works well...
If I do an INNER JOIN with the employees table, then the 'Hours Converted' is summed up by the amount of rows listed on the employee table for each employee.
I need to be able to combine query #1 and #2