Oldest date

J

John

I want to build a query to return a list of employees and the dates they
reached a certain level. In simple terms My table looks like this:

ID
Name
Level
Date

Unfortunatley, there are some other rows inserted for each member that
duplicates the levels (the table does some other stuff). So the data looks
like this:

1 Joe 3 01/01/2005
1 Joe 2 01/01/2004
1 Joe 2 01/01/2003
1 Joe 1 01/01/2000
2 Bob 5 01/01/2008
2 Bob 4 01/01/2007
2 Bob 4 01/01/2006
2 Bob 4 01/01/2005
2 Bob 3 01/01/2002

I can get the first date for each employee by using something like this

SELECT ID,Name,Level,Min(date) as Dateoflevel
FROM MbrTbl
GROUP BY ID,Name,Level

How do I return the oldest date for each level for each employee so I would
get this:

1 Joe 3 01/01/2005
1 Joe 2 01/01/2003
1 Joe 1 01/01/2000
2 Bob 5 01/01/2008
2 Bob 4 01/01/2005
2 Bob 3 01/01/2002
 
L

Lord Kelvan

your query already gives you that data

min([date]) gives you the olderst date max([date]) gives you the most
recent date

i tested your query and it gives me

1 Joe 3 01/01/2005
1 Joe 2 01/01/2003
1 Joe 1 01/01/2000
2 Bob 5 01/01/2008
2 Bob 4 01/01/2005
2 Bob 3 01/01/2002

i just added order by in as well

SELECT ID, [Name], [Level], Min([date]) AS Dateoflevel
FROM MbrTbl
GROUP BY ID, [Name], [Level]
ORDER BY ID, [Level] DESC;

Regards
Kelvan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top