Help with SQL query?

I have two tables: one with person information and the other with a
person's status changes/updates. (A person's status can go from
Prospective to Current to Completed.)



Code:
Person Table:
UserName FullName
jsmith Joe Smith
bjones Bob Jones
cblack Carol Black

Status Table:
UserName Status DateChanged
jsmith Prospective 1/1/2006
bjones Prospective 1/2/2006
jsmith Current 3/3/2006
cblack Prospective 3/3/2006
bjones Current 4/4/2006
bjones Completed 5/5/2006

>From the Status Table it's obvious that "bjones" is Completed,
"cblack" is Prospective, and "jsmith" is Current. But I'm not sure how
to grab this info from these tables in a select. I'm thinking I might
need the MAX(DateChanged) in there somewhere, but I have no clue how
to use it.

Eventually my goal is to say "show me all the names of the people who
are Current".

Any ideas? Thanks in advance, I am terrible at SQL queries!

Re: Help with SQL query?

SELECT A.FullName, A.UserName, B.Status, B.DateChanged
FROM Person as A
JOIN Status as B
ON A.UserName = B.UserName
WHERE B.DateChanged =
(SELECT MAX(C.DateChanged)
FROM Status as C
WHERE A.UserName = C.UserName)

This is called a correlated subquery since it references a column from
the outer query.

Roy Harvey
Beacon Falls, CT

On Wed, 31 Oct 2007 13:08:13 -0700, melissa820@hotmail.com wrote:

>I have two tables: one with person information and the other with a
>person's status changes/updates. (A person's status can go from
>Prospective to Current to Completed.)
>
>
>
>Code:
>Person Table:
>UserName FullName
>jsmith Joe Smith
>bjones Bob Jones
>cblack Carol Black
>
>Status Table:
>UserName Status DateChanged
>jsmith Prospective 1/1/2006
>bjones Prospective 1/2/2006
>jsmith Current 3/3/2006
>cblack Prospective 3/3/2006
>bjones Current 4/4/2006
>bjones Completed 5/5/2006
>
>>From the Status Table it's obvious that "bjones" is Completed,
>"cblack" is Prospective, and "jsmith" is Current. But I'm not sure how
>to grab this info from these tables in a select. I'm thinking I might
>need the MAX(DateChanged) in there somewhere, but I have no clue how
>to use it.
>
>Eventually my goal is to say "show me all the names of the people who
>are Current".
>
>Any ideas? Thanks in advance, I am terrible at SQL queries!

Re: Help with SQL query?

melissa820@hotmail.com wrote:
>
> I have two tables: one with person information and the other with a
> person's status changes/updates. (A person's status can go from
> Prospective to Current to Completed.)
>
> Code:
> Person Table:
> UserName FullName
> jsmith Joe Smith
> bjones Bob Jones
> cblack Carol Black
>
> Status Table:
> UserName Status DateChanged
> jsmith Prospective 1/1/2006
> bjones Prospective 1/2/2006
> jsmith Current 3/3/2006
> cblack Prospective 3/3/2006
> bjones Current 4/4/2006
> bjones Completed 5/5/2006
>
> >From the Status Table it's obvious that "bjones" is Completed,
> "cblack" is Prospective, and "jsmith" is Current. But I'm not sure how
> to grab this info from these tables in a select. I'm thinking I might
> need the MAX(DateChanged) in there somewhere, but I have no clue how
> to use it.
>
> Eventually my goal is to say "show me all the names of the people who
> are Current".
>
> Any ideas? Thanks in advance, I am terrible at SQL queries!

I sure hope this is not homework...

Anyway, so here it goes (untested):

SELECT P.UserName
, P.FullName
, S.Status
, S.DateChanged
FROM Person P
INNER JOIN Status S
ON S.UserName = P.UserName
WHERE S.DateChanged = (
SELECT MAX(DateChanged)
FROM Status
WHERE UserName = P.UserName
)

--
Gert-Jan

Re: Help with SQL query?

(melissa820@hotmail.com) writes:
> I have two tables: one with person information and the other with a
> person's status changes/updates. (A person's status can go from
> Prospective to Current to Completed.)
>
>
>
> Code:
> Person Table:
> UserName FullName
> jsmith Joe Smith
> bjones Bob Jones
> cblack Carol Black
>
> Status Table:
> UserName Status DateChanged
> jsmith Prospective 1/1/2006
> bjones Prospective 1/2/2006
> jsmith Current 3/3/2006
> cblack Prospective 3/3/2006
> bjones Current 4/4/2006
> bjones Completed 5/5/2006
>
>>From the Status Table it's obvious that "bjones" is Completed,
> "cblack" is Prospective, and "jsmith" is Current. But I'm not sure how
> to grab this info from these tables in a select. I'm thinking I might
> need the MAX(DateChanged) in there somewhere, but I have no clue how
> to use it.
>
> Eventually my goal is to say "show me all the names of the people who
> are Current".

Here is a different solution from Roy's and Gert-Jan's. It only runs
on SQL 2005, but it's potentially faster:

WITH numbered AS (
SELECT P.UserName, P.FullName, S.Status,
rowno = row_number() OVER( PARTITION BY P.UserName
ORDER BY S.DateChanged DESC)
FROM Person P
JOIN Status S ON P.UserName = S.UserName
)
SELECT UserName, FullName
FROM numbered
WHERE rowno = 1
AND Status = 'Current'


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Help with SQL query?

Thank you everyone for your help.

And no, it wasn't homework... just something I was asked to do at work
but which I'm not very familiar with. :)