How to obtain just the last record grouped by

[Massimiliano Malloni]

Sorry for my english
I have a table that contains data of career about the person (staff)

like this ...
EMATR EANID EMEID EGIID ecc. .. ecc. .. ecc. .. ecc. ..
1 1999 12 31 2002 12 31 1
1 2003 1 1 0 0 0 3
2 1999 12 31 2002 12 31 1
2 2003 1 1 0 0 0 3
4 1999 12 31 2000 7 31 1
5 1999 12 31 2001 1 31 1
6 1999 12 31 2002 12 31 1
6 2003 1 1 0 0 0 3
7 1999 12 31 2002 12 31 1

where
EMATR is a not unique key (person ID)
EANID, EMEID, EGIID (assembled) are the date of the last advance of
career

I want extract from the table below only the actual position,
therefore a view that return this rows

EMATR EANID EMEID EGIID ecc. .. ecc. .. ecc. .. ecc. ..
1 2003 1 1 0 0 0 3
2 2003 1 1 0 0 0 3
4 1999 12 31 2000 7 31 1
5 1999 12 31 2001 1 31 1
6 2003 1 1 0 0 0 3
7 1999 12 31 2002 12 31 1

I hope of to have been explain
Thank you from Maximiliano (italy)
RE-Sorry for my english
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

[Hugo Kornelis]

On Wed, 1 Dec 2004 18:07:43 +0000 (UTC), Massimiliano Malloni wrote:

Sorry for my english
I have a table that contains data of career about the person (staff)

like this ...
EMATR EANID EMEID EGIID ecc. .. ecc. .. ecc. .. ecc. ..
1 1999 12 31 2002 12 31 1
1 2003 1 1 0 0 0 3
2 1999 12 31 2002 12 31 1
2 2003 1 1 0 0 0 3
4 1999 12 31 2000 7 31 1
5 1999 12 31 2001 1 31 1
6 1999 12 31 2002 12 31 1
6 2003 1 1 0 0 0 3
7 1999 12 31 2002 12 31 1

where
EMATR is a not unique key (person ID)
EANID, EMEID, EGIID (assembled) are the date of the last advance of
career

I want extract from the table below only the actual position,
therefore a view that return this rows

EMATR EANID EMEID EGIID ecc. .. ecc. .. ecc. .. ecc. ..
1 2003 1 1 0 0 0 3
2 2003 1 1 0 0 0 3
4 1999 12 31 2000 7 31 1
5 1999 12 31 2001 1 31 1
6 2003 1 1 0 0 0 3
7 1999 12 31 2002 12 31 1

I hope of to have been explain
Thank you from Maximiliano (italy)
RE-Sorry for my english

Hi Maximiliano,

I advise you to change this table design. Dates should be stored in one
column, using the datetime format. Any other format tends to become a real
pain when you need to query the dates or do logic with them. A much better
design would be (using English columns names - all Italian words I know
are for food <g>)

CREATE TABLE Maximiliano
(PersonID int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime DEFAULT NULL
,other columns .....
,PRIMARY KEY (PersonID, StartDate)
,UNIQUE (PersonID, EndDate)
,FOREIGN KEY (PersonID) REFERENCES Persons
,CHECK (StartDate < EndDate)
)

Your data would know look like this:

PersonID StartDate EndDate other columns
1 1999-12-31 2002-12-31 1
1 2003-01-01 NULL 3
2 1999-12-31 2002-12-31 1
2 2003-01-01 NULL 3
4 1999-12-31 2000-07-31 1
5 1999-12-31 2001-01-31 1
6 1999-12-31 2002-12-31 1
6 2003-01-01 NULL 3
7 1999-12-31 2002-12-31 1
The query to return the most recent row for each person:

SELECT PersonID, StartDate, EndDate, other columns
FROM Maximiliano AS a
WHERE NOT EXISTS
(SELECT *
FROM Maximiliano AS b
WHERE b.PersonID = a.PersonID
AND b.StartDate > a.StartDate)

If you need to do it with your current table design, replace a.StartDate
with the following complicated expression:

DATEADD(dd, a.EGIID - 1, DATEADD(mm, a.EMEID - 1, DATEADD(yy, a.EANID -
2000, '20000101')))

and do the same for b.StartDate (this time prefixing the three columns
with b of course)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)