query - which came last?

Hi,
Query puzzler here.
I have a table of PEOPLE.
Besides the Person ID, each record has five Date fields- Married ,
Graduated, Started Job, Got Famous, Got Rich
I want to write a query that shows what the current status of each person
is.
In other words, show which event has happened most recently for each person
Any help would be , and might keep me from getting fired.No datefields in the table? Can you show one or two rows as sample in your
table?
Thanks,
Sree
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Hi, no-one wants to see you get fired...
SELECT PersonID, max(Married) FROM (
select PersonID, Married from PEOPLE
union
select PersonID, Graduated from PEOPLE
union
select PersonID, [Started Job] from PEOPLE
union
select PersonID, [Got Famous] from PEOPLE
union
select PersonID, [Got Rich] from PEOPLE) as Derived
GROUP BY PersonID
HTH. Ryan
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||you can use this code:
SELECT ID,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then 'Married'
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then 'Graduated'
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then '[Started Job]'
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then '[Got Famous]'
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then '[Got Rich]'
Else 'nothing happend'
End
As LastEvent,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then Married
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then Graduated
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then [Started Job]
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then [Got Famous]
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then [Got Rich]
End
As LastEventDate
FROM PEOPLE
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Thanks - this seems to be the shorter of the two, saving typing, allowing
more time to beg for my job.
Both are instructive.
thnanks a lot
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||Hi this is almost there, but it returns dates. I need the column name
returened.
Like: Married
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||I need the status, not the date
OK I could use both.
I may get fired after all
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:

>Hi,
>Query puzzler here.
>I have a table of PEOPLE.
>Besides the Person ID, each record has five Date fields- Married ,
>Graduated, Started Job, Got Famous, Got Rich
>I want to write a query that shows what the current status of each person
>is.
>In other words, show which event has happened most recently for each person
>Any help would be , and might keep me from getting fired.
Hi Tales,
The person who designed that table should get fired, not you.
First, let's create a view to get a better normalised version of the
data:
CREATE VIEW Normalized
AS
SELECT PersonID, Married AS EventDate, 'Married' AS Event
FROM PEOPLE
UNION ALL
SELECT PersonID, Graduated, 'Graduated'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Started Job", 'Started Job'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Famous", 'Got Famous'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Rich", 'Got Rich'
FROM PEOPLE
go
With this, getting the required information becomes a lot easier:
SELECT a.PersonID, a.EventDate, a.Event
FROM Normalized AS a
WHERE a.EventDate = (SELECT MAX(b.EventDate)
FROM Normalized AS b
WHERE b.PeopleID = a.PeopleID)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||thanks a lot|||Hugo,
Is it just me, or are the columns Married, Graduated, StartedJob, GotFamous,
and GotRich unlikely columns in any database?
Unlikely, of course, unless the database is academic only.
Maybe Tales simply made up these column names because they couldn't tell us
the actual columns for proprietary reasons, but I am willing to bet this is
a classroom example, and no one's job is at risk.
I believe Tales just got this newsgroup to do his/her homework.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:r3adt15gapiv1bvvkcitvvrfq33rg850d8@.
4ax.com...
> On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:
> Hi Tales,
> The person who designed that table should get fired, not you.
>