Need SQL Help Finding Current Status of members

------=_NextPart_000_002F_01C601C4.9FAD2460
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I have a table which keeps track of the status of members. In the table is


member_id int(8)
status_code char(1)
status_date date

KEY member_id (member_id,status_code,status_date)


Each member can have multiple records because a record is added each time
the status changes but the old record is kept for history.

What I want to do is find the latest status for each member. Actually I want
to find all those with an status of "A". But it must be the current (latest)
status. How do I find the most current date for each member in a pile of
many records for many members with many status settings with one SQL
statement? This is a bit beyond my capabilities so I am asking for help.

My initial SQL is

SELECT * FROM memberstatus WHERE status_code = 'a'

but that is my limit. I know an AND comes next but need help after that! I
know that MAX is not for dates so that is out. Is there a LATEST DATE? I did
not see one when I was looking at the date functions.

Appreciate the help.

Mike



------=_NextPart_000_002F_01C601C4.9FAD2460
Content-Type: text/plain; charset=us-ascii


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
------=_NextPart_000_002F_01C601C4.9FAD2460--

Re: Need SQL Help Finding Current Status of members

--=_alternative 0021E857852570D9_=
Content-Type: text/plain; charset="US-ASCII"

Responses blended in...(see below)

"Michael Avila" wrote on 12/15/2005
10:12:23 PM:

> I have a table which keeps track of the status of members. In the table
is
>
>
> member_id int(8)
> status_code char(1)
> status_date date
>
> KEY member_id (member_id,status_code,status_date)
>

What makes one row unique from all of the others? It's usually more
helpful to post the ENTIRE table definition.

>
> Each member can have multiple records because a record is added each
time
> the status changes but the old record is kept for history.

That isn't normally a problem except that your data is only accurate to
the nearest day. Working just from the partial table definition you
posted, I can think it is reasonable for someone to have more than one
status code during the same day. That makes it impossible (using just the
columns you showed us) to determine which happened first or last during a
particular day.

>
> What I want to do is find the latest status for each member. Actually I
want
> to find all those with an status of "A". But it must be the current
(latest)
> status. How do I find the most current date for each member in a pile of
> many records for many members with many status settings with one SQL
> statement? This is a bit beyond my capabilities so I am asking for help.
>
> My initial SQL is
>
> SELECT * FROM memberstatus WHERE status_code = 'a'
>
> but that is my limit. I know an AND comes next but need help after that!
I
> know that MAX is not for dates so that is out. Is there a LATEST DATE? I
did
> not see one when I was looking at the date functions.

Who said that MAX() didn't work with dates? In fact, MAX actually is the
"latest date" function you asked about.

>
> Appreciate the help.
>
> Mike
>

Since you didn't say what version you are using (later versions provide
more solution options) I must assume the worst and refer you to the
documentation for the older versions. In this, you will find an article
describing how to isolate those records containing the maximum of a group.

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-row.h tml

If you read through this and can't figure out how to make the example work
for your situation, please come back to the list and I am sure someone can
help you with additional details.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0021E857852570D9_=--

RE: Need SQL Help Finding Current Status of members

------=_NextPart_000_000E_01C6021E.95649830
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit

That is the ENTIRE table definition. It is NOT a partial definition. Look at
the key. I guess it is possible to have a person go from active to inactive
to active. I'll have to look at that again. A status will not change every
day. Maybe once a year or less. But it looks like that will have to be
revisited and changed.

What version did I not tell you about? I thought I put MySQL 4.1.15 in
there. May have forgotten to do that.

-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]
Sent: Friday, December 16, 2005 1:12 AM
To: Michael Avila
Cc: MySQL - Win32
Subject: Re: Need SQL Help Finding Current Status of members



Responses blended in...(see below)

"Michael Avila" wrote on 12/15/2005
10:12:23 PM:

> I have a table which keeps track of the status of members. In the table
is
>
>
> member_id int(8)
> status_code char(1)
> status_date date
>
> KEY member_id (member_id,status_code,status_date)
>

What makes one row unique from all of the others? It's usually more
helpful to post the ENTIRE table definition.

>
> Each member can have multiple records because a record is added each
time
> the status changes but the old record is kept for history.

That isn't normally a problem except that your data is only accurate to
the nearest day. Working just from the partial table definition you posted,
I can think it is reasonable for someone to have more than one status code
during the same day. That makes it impossible (using just the columns you
showed us) to determine which happened first or last during a particular
day.

>
> What I want to do is find the latest status for each member. Actually I
want
> to find all those with an status of "A". But it must be the current
(latest)
> status. How do I find the most current date for each member in a pile of
> many records for many members with many status settings with one SQL
> statement? This is a bit beyond my capabilities so I am asking for help.
>
> My initial SQL is
>
> SELECT * FROM memberstatus WHERE status_code = 'a'
>
> but that is my limit. I know an AND comes next but need help after that!
I
> know that MAX is not for dates so that is out. Is there a LATEST DATE? I
did
> not see one when I was looking at the date functions.

Who said that MAX() didn't work with dates? In fact, MAX actually is the
"latest date" function you asked about.

>
> Appreciate the help.
>
> Mike
>

Since you didn't say what version you are using (later versions provide
more solution options) I must assume the worst and refer you to the
documentation for the older versions. In this, you will find an article
describing how to isolate those records containing the maximum of a group.

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-row.h tml

If you read through this and can't figure out how to make the example work
for your situation, please come back to the list and I am sure someone can
help you with additional details.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

------=_NextPart_000_000E_01C6021E.95649830--

RE: Need SQL Help Finding Current Status of members

--=_alternative 0054D837852570D9_=
Content-Type: text/plain; charset="US-ASCII"

I didn't mean to offend. I thought your table definition was incomplete
because I didn't see anything that made each row unique.

A "KEY" is not a "UNIQUE" index or "PRIMARY KEY" index ("KEY" is simply a
synonym for "index"). There is nothing in your table definition that
enforces uniqueness between rows. If you simply changed your existing KEY
to a PRIMARY KEY that would only ensure that any one member cannot have
the same status twice in one day. It won't prevent them from having more
than one status per day. To do that you would need to add another index, a
"UNIQUE(`status_date`)". That would prevent two entries on the same day,
even with your current key.

Were you able to translate the example to fit your needs?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Michael Avila" wrote on 12/16/2005
08:56:20 AM:

> That is the ENTIRE table definition. It is NOT a partial definition.
> Look at the key. I guess it is possible to have a person go from
> active to inactive to active. I'll have to look at that again. A
> status will not change every day. Maybe once a year or less. But it
> looks like that will have to be revisited and changed.
>
> What version did I not tell you about? I thought I put MySQL 4.1.15
> in there. May have forgotten to do that.
>
> -----Original Message-----
> From: SGreen@unimin.com [mailto:SGreen@unimin.com]
> Sent: Friday, December 16, 2005 1:12 AM
> To: Michael Avila
> Cc: MySQL - Win32
> Subject: Re: Need SQL Help Finding Current Status of members

>
> Responses blended in...(see below)
>
> "Michael Avila" wrote on 12/15/2005
> 10:12:23 PM:
>
> > I have a table which keeps track of the status of members. In the
table is
> >
> >
> > member_id int(8)
> > status_code char(1)
> > status_date date
> >
> > KEY member_id (member_id,status_code,status_date)
> >
>
> What makes one row unique from all of the others? It's usually more
> helpful to post the ENTIRE table definition.
>
> >
> > Each member can have multiple records because a record is added each
time
> > the status changes but the old record is kept for history.
>
> That isn't normally a problem except that your data is only accurate
> to the nearest day. Working just from the partial table definition
> you posted, I can think it is reasonable for someone to have more
> than one status code during the same day. That makes it impossible
> (using just the columns you showed us) to determine which happened
> first or last during a particular day.
>
> >
> > What I want to do is find the latest status for each member. Actually
I want
> > to find all those with an status of "A". But it must be the current
(latest)
> > status. How do I find the most current date for each member in a pile
of
> > many records for many members with many status settings with one SQL
> > statement? This is a bit beyond my capabilities so I am asking for
help.
> >
> > My initial SQL is
> >
> > SELECT * FROM memberstatus WHERE status_code = 'a'
> >
> > but that is my limit. I know an AND comes next but need help after
that! I
> > know that MAX is not for dates so that is out. Is there a LATEST DATE?
I did
> > not see one when I was looking at the date functions.
>
> Who said that MAX() didn't work with dates? In fact, MAX actually is
> the "latest date" function you asked about.
>
> >
> > Appreciate the help.
> >
> > Mike
> >
>
> Since you didn't say what version you are using (later versions
> provide more solution options) I must assume the worst and refer you
> to the documentation for the older versions. In this, you will find
> an article describing how to isolate those records containing the
> maximum of a group.
>
> http://dev.mysql.com/doc/refman/4.1/en/example-maximum-row.h tml
>
> If you read through this and can't figure out how to make the
> example work for your situation, please come back to the list and I
> am sure someone can help you with additional details.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
--=_alternative 0054D837852570D9_=--