Ranking... One More Time Please

[QVCJDN]

There are several posts that have discussed ranking within a query, but I'm
not too sharp at SQL and I'm having a difficult time. My query is below. I'm
trying to create a table that would show a ranking for employees by company
seniority date, without displaying the actual date.

SELECT [PeopleSoft Six-Digit].Name, [PeopleSoft Six-Digit].[6 Digit ADP],
[PeopleSoft Six-Digit].[Company Seniority Date], [PeopleSoft Six-Digit].[FLSA
Stat]
FROM [PeopleSoft Six-Digit]
WHERE ((([PeopleSoft Six-Digit].[FLSA Stat])<>"Exempt"))
ORDER BY [PeopleSoft Six-Digit].[Company Seniority Date];

Thanks for any help.

 

[Tom Ellison]

Dear QVC:

How about:

SELECT [Name], [6 Digit ADP], [FLSA Stat]
(SELECT COUNT(*)
FROM [PeopleSoft Six-Digit] T1
WHERE T1.[FLSA Stat] <> "Exempt"
AND T1.[Company Seniority Date] < T.[Company Seniority Date]) + 1
AS Rank
FROM [PeopleSoft Six-Digit] T
WHERE [PeopleSoft Six-Digit].[FLSA Stat] <> "Exempt"
ORDER BY [Company Seniority Date]

You can remove the + 1 for a 0 based ranking.

If two people have the same Seniority Date then they will have the same
rank, and the next ranking value will be skipped.

Tom Ellison