Query TWO LastEffectiveDate felids for data.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query TWO LastEffectiveDate felids for data.

Author

Topic

smilbuta
Starting Member

5 Posts

Posted - 2007-11-08 : 15:52:10

Hello community.

Here is a sample of the table.

EFFDT   Status   Name
1/3/07  GRA      Tom
1/1/07  PRI      Tom
1/2/07  PRI      Tom
2/1/07  PRI      Bob
2/2/07  PRI      Bob
2/3/07  GRA      Bob

I need to Query this table on the effective date retriving the last GRA record and PRI record for each name.

My final results should look as follows:


EFFDT   Status   Name
1/3/07  GRA      Tom
1/2/07  PRI      Tom
2/2/07  PRI      Bob
2/3/07  GRA      Bob

Any advice would be most welcome.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 15:58:13

SELECT EFFDT, Status, Name FROM (
SELECT EFFDT, Status, Name, ROW_NUMBER() OVER (PARTITION BY Status, Name ORDER BY EFFDT DESC) AS RecID
) AS d WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

smilbuta
Starting Member

5 Posts

Posted - 2007-11-08 : 16:12:57

THanx Peso, but I dont belive that helps my situation In using SQL2000, OVER is a 2005 clause no?. WHat if i have more than 3 records for a particular Name. I could have this situation:


EFFDT   Status   Name
1/3/07  PRI      Tom
1/1/07  GRA      Tom
1/2/07  PRI      Tom
2/1/07  GRA      Tom
2/2/07  PRI      Tom

My results need to be:


EFFDT   Status   Name
2/1/07  GRA      Tom
2/2/07  PRI      Tom

smilbuta
Starting Member

5 Posts

Posted - 2007-11-08 : 16:19:32

I just realized i posted in the 05 section, WOuld a Moderator please move this to the 2000 section, Sorry.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 16:25:35

[code]SELECT MAX(EFFDT) AS EFFDT,
Status,
Name
FROM Table1
GROUP BY Status,
Name[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources