?
Solved

Nested Select on two tables where one table has many rows for one unique item

Posted on 2007-07-24
8
Medium Priority
?
281 Views
Last Modified: 2010-03-20
Need help on a joining of data on two tables.  I tried a nested select but didn't produce the results I require.

Business need:
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.

For example:

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.

select
first_name,
middle_name,
last_name,
MAX(DATEADD (mi, audit_timestamp,'01/01/1930')) AS audit_timestamp
from employee
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

SELECT
A.serial_number,
CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
A.detail_date,
A.sequence,
A.timecode_id,
A.ld_code3,
A.hours,
SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Converted'
FROM
Ceridian.dbo.timecard_raw AS A
WHERE
A.serial_number =  '000002074'
GROUP BY
A.serial_number,A.pay_period,A.detail_date,A.sequence,A.timecode_id,A.ld_code3,A.hours

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

Thanks
0
Comment
Question by:PROJHOPE
  • 5
  • 3
8 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 19559619
You have a problem.

If your employees table contains historical obsolete versions of the names, and if you have no unique identifier of actual employees, you cannot use that table. It's basically worthless.

You cannot identify "correct" records with the rule that the three basic fields are not null. Many people don't have a middle name, and having a middle initial isn't proof that it's the correct middle initial (there might exist a later record that corrects it).

You *must* have some way of identifying families of records pertaining to the same physical employee. If you don't, you will have to restart from scratch.

Cheers!
(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 19559677
Is "serial_number" a unique (foreign) key to identify physical employees?
(°v°)
0
 

Author Comment

by:PROJHOPE
ID: 19559743
Hi harfang, worthless is the understatement now I can see why this vendor is out of business.  Sorry for not having more info.

serial_number is unique foreign key to use to identify the employee.  The serial_number field is what joins the employee and the timecard_raw table together.

As stated originally the serial_number for an employee on employee table can appear more than once due to changes in an employee's name, status ,etc.

I.E.

00002074 Joseph Smith
00002074 Joseph A Smith



0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:PROJHOPE
ID: 19560006
One more thing a query like this may be better than my first query

select DISTINCT serial_number,first_name,last_name from employee
where serial_number = '000002074'

serial_number 000002074 has 5 rows on the employee table.  When I run this query one unique record comes back which is great.

Now to incorporate this into query #2 and my problem resolved...
0
 
LVL 58

Expert Comment

by:harfang
ID: 19560089
So you need a query (or better, a table) with the latest information about each employee. Sorry, I'm no guru in SQL-Server, but something like this should do it:

SELECT * FROM employee
WHERE audit_timestamp >= All (
    Select audit_timestamp From employee TMP
    Where serial_number = employee.serial_number
    )

This should be useful. You can use that to conceive a make-table query as well, so that you have an actual accurate and up-to-date list of employees.

In any case (query or new table), you can then combine it to your second query, as serial_number (what a horrible name, though) will be unique.

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 19560190
Perhaps something like this?

SELECT
    ...,
    B.*
FROM
    (
        SELECT * FROM employee
        WHERE audit_timestamp >= All (
            Select audit_timestamp From employee TMP
            Where serial_number = employee.serial_number
            )
    ) AS B
    INNER JOIN Ceridian.dbo.timecard_raw AS A
    ON B.serial_number = A.serial_number
WHERE
    B.serial_number =  '000002074'
[...]

(°v°)
0
 

Author Comment

by:PROJHOPE
ID: 19568553
harfang please give yourself credit!  I like the ALL syntax, it is sometimes hard and overwhelming to know all expressions in SQL.

I like the code and I have compared and it gives a unique serial number and employee row.

When I add it to my query I get an error after the 'AS B' from the nested select. I think I am missing a parenthesis...

See below

SELECT    
A.serial_number,
CONVERT(VARCHAR(10),CONVERT(datetime,A.pay_period),101) AS 'Pay Period',
A.detail_date,
A.sequence,
A.timecode_id,
A.ld_code3 AS 'Project Code',
A.hours AS 'Hours Non-Formatted',
MAX(DATEADD (mi,B.audit_timestamp,'01/01/1930')) AS 'Employee Timestamp',
SUM(CONVERT(decimal(9, 4), LEFT(A.hours, 2)) + (CONVERT(decimal(9, 4), RIGHT(A.hours, 2)) / 60)) AS 'Hours Formatted',
B.*
FROM
(SELECT *
FROM Ceridian.dbo.employee
WHERE audit_timestamp >= ALL (SELECT audit_timestamp FROM Ceridian.dbo.employee TMP  WHERE serial_number = employee.serial_number) AS B
INNER JOIN Ceridian.dbo.timecard_raw AS A,
ON B.serial_number = A.serial_number
WHERE
A.serial_number = '000002074'
GROUP BY A.serial_number, A.pay_period, A.detail_date,A.sequence, A.timecode_id,
A.ld_code3, A.hours, B.last_name,B.first_name

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'AS'.
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 19568828
> I think I am missing a parenthesis...

So you do. it's ... employee.serial_number)) AS B

The first closes the sub-query of the All syntax, the second closes the entire "As B" record source (I think both parenthesis appeared in my sample).

> please give yourself credit!

Thank you for that. It's just that I really write JetSQL syntax automatically, which can lead to some nasty surprises in this "SQL_Syntax" group. Your example with varchar and convert clearly points towards MS-SQL Server.

Good luck!
(°v°)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question