SQL Server 2000 Correlated Query Question

[MDW]

Hey all,

Have another question. I've got a SQL Server 2000 DB that contains employee information....we've got a table called EMPLOYEE_MASTER and one called EMPLOYEE_DETAIL. The two tables are linked based on SSN.

In the EMPLOYEE_DETAIL table, there is a column called START_DATE and a column called END_DATE. Employees can move from one job to another, so there could be multiple records w/ the same SSN in this table.

I'm trying to write a query that will find the branch number a person currently works at. There are two criteria to that -
1) Find the Branch number that corresponds to the person's most recent START_DATE, but
2) Only is their END_DATE is null.

I'm thinking that a correlated query can do this, but I've never done one before. Here's what I hashed out - can someone tell me if this looks correct? I don't want to run it against my DB until I have a fair amount of confidence that it works right.

Thanks in advance:

strSQL = "SELECT D.BRANCH_NBR, " &_
"D.START_DATE," &_
"E.FIRST_NAME, " &_
"E.LAST_NAME, " &_
"E.SSN, " &_
"Right(E.SSN,6) AS LoginID, " &_
"Right(E.SSN,4) AS Pwd " &_
"FROM EMPLOYEE_MASTER E INNER JOIN EMPLOYEE_DETAIL D ON E.SSN=D.SSN " & _
"WHERE (D.START_DATE=(SELECT MAX(D2.START_DATE) FROM EMPLOYEE_DETAIL D2 WHERE D2.SSN = D.SSN) AND D.END_DATE IS NULL)

[Bob Barrows [MVP]]

MDW wrote:

> Hey all,
>
> Have another question. I've got a SQL Server 2000 DB that contains
> employee information....we've got a table called EMPLOYEE_MASTER and
> one called EMPLOYEE_DETAIL. The two tables are linked based on SSN.

Hmmmm. You may wish to rethink this: SSN might not be unique. Do a Google
search for more on this.

>
> In the EMPLOYEE_DETAIL table, there is a column called START_DATE and
> a column called END_DATE. Employees can move from one job to another,
> so there could be multiple records w/ the same SSN in this table.
>
> I'm trying to write a query that will find the branch number a person
> currently works at. There are two criteria to that - 1) Find the
> Branch number that corresponds to the person's most recent
> START_DATE, but 2) Only is their END_DATE is null.

Will there only be one record for an employee with a null END_DATE?

>
> I'm thinking that a correlated query can do this, but I've never done
> one before. Here's what I hashed out - can someone tell me if this
> looks correct? I don't want to run it against my DB until I have a
> fair amount of confidence that it works right.
>
> Thanks in advance:
>
> strSQL = "SELECT D.BRANCH_NBR, " &_
> "D.START_DATE," &_
> "E.FIRST_NAME, " &_
> "E.LAST_NAME, " &_
> "E.SSN, " &_
> "Right(E.SSN,6) AS LoginID, " &_
> "Right(E.SSN,4) AS Pwd " &_
> "FROM EMPLOYEE_MASTER E INNER JOIN EMPLOYEE_DETAIL D ON E.SSN=D.SSN "
> & _ "WHERE (D.START_DATE=(SELECT MAX(D2.START_DATE) FROM
> EMPLOYEE_DETAIL D2 WHERE D2.SSN = D.SSN) AND D.END_DATE IS NULL)

It looks to me as if you are trying to create and test a query using ASP.
This is not recommended. Always create and test your queries in your
database's native query tool (Query Analyzer in your case) before attempting
to run them from a client application (ASP). Using Query Analyzer will allow
you to analyze and optimize the performance of your queries, something you
would never do if writing your queries in ASP.

SQL is so much easier to read without all the &'s, etc. Please post the
actual query in the future instead of the vbscript code that is supposed to
result in a SQL statement.

Your attempt does not look very efficient to me. It always helps to break
down your task into subtasks. Try this in Query Analyzer:

This query will give you the "current" record for each employee (assuming
there will be only one "current" record for each employee):
SELECT SSN, BRANCH_NBR, START_DATE
FROM EMPLOYEE_DETAIL
WHERE END_DATE IS NULL

If there could be multiple "current" records (i.e., records where the
END_DATE is Null, then you will need to do this:

First, get the most recent "current" start dates for each employee:
SELECT SSN,Max(START_DATE) CurStartDate
FROM EMPLOYEE_DETAIL
WHERE END_DATE IS NULL
GROUP BY SSN

Test it to verify that it works correctly.
Then use this query as one of the data sources in the next query:

SELECT SSN, BRANCH_NBR, START_DATE
FROM EMPLOYEE_DETAIL d INNER JOIN
(SELECT SSN,Max(START_DATE) CurStartDate
FROM EMPLOYEE_DETAIL
WHERE END_DATE IS NULL
GROUP BY SSN) q ON d.SSN = q.SSN AND
d.START_DATE = q.START_DATE

Again, test it in QA to verify it works as advertised.

Now, it's simply a matter of adding the master table into the join to get
the information required from the master table:

SELECT e.SSN, d.BRANCH_NBR, d.START_DATE,
E.FIRST_NAME, E.LAST_NAME, Right(E.SSN,6) AS LoginID,
Right(E.SSN,4) AS Pwd
FROM EMPLOYEE_MASTER E INNER JOIN
EMPLOYEE_DETAIL d ON E.SSN=D.SSN
INNER JOIN
(SELECT SSN,Max(START_DATE) CurStartDate
FROM EMPLOYEE_DETAIL
WHERE END_DATE IS NULL
GROUP BY SSN) q ON d.SSN = q.SSN AND
d.START_DATE = q.START_DATE

Try both versions of this query in QA to see which performs better. I
strongly suggest creating a stored procedure from this query, and executing
the stored procedure in ASP instead of using dynamic SQL.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Manohar Kamath [MVP]]

Run the query in the SQL query analyzer and check if you get correct
results.

Also, why not just query on the end date being null, and not worry about Max
start date? Considering the details table contains only full-time jobs, a
person can not have more than one job.


--
Manohar Kamath
Editor, .netWire
[url]www.dotnetwire.com[/url]


"MDW" <MDW@discussions.microsoft.com> wrote in message
news:4F3056B6-2D66-425E-A128-B7332BFB0162@microsoft.com...

> Hey all,
>
> Have another question. I've got a SQL Server 2000 DB that contains

employee information....we've got a table called EMPLOYEE_MASTER and one
called EMPLOYEE_DETAIL. The two tables are linked based on SSN.

>
> In the EMPLOYEE_DETAIL table, there is a column called START_DATE and a

column called END_DATE. Employees can move from one job to another, so there
could be multiple records w/ the same SSN in this table.

>
> I'm trying to write a query that will find the branch number a person

currently works at. There are two criteria to that -

> 1) Find the Branch number that corresponds to the person's most recent

START_DATE, but

> 2) Only is their END_DATE is null.
>
> I'm thinking that a correlated query can do this, but I've never done one

before. Here's what I hashed out - can someone tell me if this looks
correct? I don't want to run it against my DB until I have a fair amount of
confidence that it works right.

>
> Thanks in advance:
>
> strSQL = "SELECT D.BRANCH_NBR, " &_
> "D.START_DATE," &_
> "E.FIRST_NAME, " &_
> "E.LAST_NAME, " &_
> "E.SSN, " &_
> "Right(E.SSN,6) AS LoginID, " &_
> "Right(E.SSN,4) AS Pwd " &_
> "FROM EMPLOYEE_MASTER E INNER JOIN EMPLOYEE_DETAIL D ON E.SSN=D.SSN " & _
> "WHERE (D.START_DATE=(SELECT MAX(D2.START_DATE) FROM EMPLOYEE_DETAIL D2

WHERE D2.SSN = D.SSN) AND D.END_DATE IS NULL)