Seniority list with incremental number changing if hiring/quitting 1

[yoshi88]

Hi I have a database of employes and I need to sort them with a seniority number. This number will vary if employes quit or new one are hired. My table looks like this:

Dates (the name of the table)
No_employe (the primary key)
No_union (there is 2 different unions)
Date_seniority
Rank (if 2 employes have the same seniority date, they will have number 1, 2, 3, ... if not it's 0)

I need to order by No_union, Date_seniority and Rank after that I would like to have a number starting at 1 for the employe with the more seniority and going +1 for the second ...

Then I will use this number in some report and also in a few queries.

I tried a few things mention in earlier treads but I'm not really good with VB coding. Can someone help me with that problem?

Francois

 

[PHV]

Can you please post some examples of input data and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[yoshi88]

Here is an example:

AA BBBBBBBBB C D
55 21-05-1999 0 1
55 22-09-2000 0 2
55 01-08-2001 1 3
55 01-08-2001 2 4
55 01-08-2001 3 5

Where:
AA is the Union code
BBBBBBBBB is the seniority date
C is the rank
D would be the incremntal number

If I add a new employe with this date 30-01-2000 I want the query to look like this.

AA BBBBBBBBB C D
55 21-05-1999 0 1
55 30-01-2000 0 2
55 22-09-2000 0 3
55 01-08-2001 1 4
55 01-08-2001 2 5
55 01-08-2001 3 6


Thanks

 

[PHV]

So the Rank and Increment shouldn't be stored in the table but always calculated on the fly to prevents discrepancies when employes quit or new one are hired.
Something like this ? (typed not tested)
SELECT No_employe, No_union, Date_seniority,
(Select Count(*) From employes R Where R.No_union=E.No_union
And R.Date_seniority=E.Date_seniority And R.No_employe<E.No_employe) As Rank,
(Select Count(*) From employes I Where I.No_union=E.No_union
And I.Date_seniority<=E.Date_seniority And
(I.No_employe<=E.No_employe Or I.Date_seniority<E.Date_seniority)) As Increment
FROM employes E
ORDER BY 2, 3, 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[yoshi88]

Hi PH, your code works really well, I had to adjust it a bit because the rank is decide with a draw if many employes have the same seniority date. Here is the code after the adjustment. It's now working well even if I don't understand everything about this code especially the last AND (...).

SELECT E.No_employe, E.No_union, E.Date_seniority, E.Rank, (Select Count(*) From Dates I Where I.No_union=E.No_union And I.Date_seniority<=E.Date_seniority And (I.Rank<=E.Rank Or I.Date_seniority<E.Date_seniority)) AS Increment
FROM Dates AS E
ORDER BY E.No_union, E.Date_seniority, E.Rank;

Thanks a lot

Francois

 

[acjim]

PHV,

I've been trying to twist the ranking feature into life all day! Have a star for allowing some extra insight.

Thanks!