To Fix Employee Seniority and Decide when each one will move to Higher Rank

Help needed from #EXCEL, #MACRO, #DB, #Database experts
See this file from DropBox

This is the Seniority List of Employees.

  • In the first Column, We have the employees Unique ID
  • In the Second Column, we have the employees Date of Birth
  • In the Third Column, we have the Seniority of the Employees as on June  2018

The Promotion for the employees are given based on their seniority

  • Assume that Employees in Seniority 1 to 3000 are Called Rank I
  • Assume that Employees in Seniority 3001 to 6000 are Called Rank II
  • Assume that Employees in Seniority 6001 to 9000 are Called Rank III
  • Assume that Employees in Seniority 9001 to 12000 are Called Rank IV

Each Employee Moves up the seniority when some one senior to him retires
Employees Retire on Reaching 58 years subject to the following Rules

  • Those who are born from 2 to 31st of a month retire at the end of the month after completing 58 year
  • Those are are born on first of a month retire on the last day of previous month

In Other words

  • Those who are born from 02-06-1960 to 01-07-1960 will retire on 30-06-2018
  • Those who are born from 02-07-1960 to 01-08-1960 will retire on 31-07-2018
  • Those who are born from 02-08-1960 to 01-09-1960 will retire on 31-08-2018
  • Those who are born from 02-09-1960 to 01-10-1960 will retire on 30-09-2018
  • Those who are born from 02-10-1960 to 01-11-1960 will retire on 31-10-2018
  • Those who are born from 02-11-1960 to 01-12-1960 will retire on 30-11-2018
  • Those who are born from 02-12-1960 to 01-01-1961 will retire on 31-12-2018

So What happens

Assume 40 people in Rank I and 20 People in Rank II (this is assumption. Real Data will be varying) retire on 30-06-2018

  • On 01 July 2018
  • We need to move those in Seniority 3001 to 3040 (in this case Unique ID 3000 to 3039) from Rank II to Rank I
  • Now, In Rank II, 40 people have been promoted to Rank I and Another 20 people have retired
  • So, Those in Seniority 6001 to 6060 (in this case Unique ID 6000 to 6059) will be moved from Rank III to Rank II
  • And, Those in Seniority 9001 to 9060 will move from Rank IV to Rank III

This has to be done at every Month

In this way
We will have to find a date on which every individual will move to the next higher Rank

  • As far as those who are already in Rank I, there is no further promotion
  • For those in Rank II, we need to find when each of them will move to Rank I
  • For those in Rank III, we need to find when each of them will move to Rank II and then to Rank I
  • For those in Rank IV, we need to find when each of them will move to Rank III and then to Rank II and then to Rank I

We need to list against each person

  • in Rank II (Seniority 3001 to 6000) when he/she will is get on to Rank I (1 to 3000) ie 1 date
  • in Rank III (Seniority 6001 to 9000) When he/she will be reach Rank II (3001-6000) and then Rank I (1 – 3000) ie 2 dates
  • in Rank IV (Seniority 9001 to 12000) When he/she will reach Rank III (6001-9000), Rank II (3001-6000) and then to Rank I (1-3000)  ie three dates

Feel free to ask doubts
We would be much grateful for the dates
But
If you can also teach how to do, it will be even more helpful

Anti Rabies Vaccine for Dog Bite. Availability in Primary Health Centre and Medical College

Some Times Truth is Stranger than Fiction
So
Let me share a real fact.

-oOo-

I joined Tamil Nadu Medical Services in 2004 and worked for three years in Primary Health Centres. In Primary Health Centres, Run by Department of Health, Government of Tamil Nadu, we used to give ARV (Anti Rabies Vaccine) any time the patient walks in.

And then
I came to Madras Medical College, a tertiary care institution and was shocked to find that Vaccines are given only from 8 AM to 11 AM and that too only on Working Days. . . I could not comprehend the rationale. I am sure that Dogs in areas round the medical college hospital do not follow Calendar and Watch when biting on unsuspecting individuals

-oOo-



So, you have two hospitals run by the Same Government, Same Ministry, Same Minister, Same Health Secretary

  1. A 24x7x365 Tertiary Care Hospital, with nearly 1000 doctors working and around 5000 beds, where Vaccines for dog bite are given only 3 hours for 6 days a week
  2. A Primary Health Centre, with just two doctors, where vaccines for dog bite are given any time the patient walks in

Logic says that you will expect better service in a Tertiary Care Hospital
But
In Reality, Primary Health Centres Provide Better Service

So, the administrative fact that is really fiction is that Tertiary Health Care gives better service 🙂

I did try to analyse why this happened and found the reason too

It was because

Anti Rabies Vaccine Multi Dose Vial
Anti Rabies Vaccine Multi Dose Vial

Earlier, Vaccines came in Multi Dose Vial. That is in a single bottle (or ampoule) there were many doses. So, they took the bottle out of the fridge and wanted to finish it off quickly. So they made people come within a narrow time window
But
Now, vaccines come in single dose vial. Only one dose in a bottle (or ampoule). So they can open the vial, give the injection and need not worry about keep it safe again

Anti Rabies Vaccines started in Medical Colleges a long time ago, So they followed the narrow time window. They did not think to change it when multi dose vials got replaced by single dose vials. They did not apply their mind. They did not take the new scientific advancement to their advantage. It is like a person who started using bicycle, continuing in bicycle even after two wheelers were invented

Anti Rabies Vaccine Single Dose Vial
Anti Rabies Vaccine Single Dose Vial

But
Primary Health Centres, where they recently started Anti Rabies Vaccines and at that time, they had single dose vials. So they did not have any time window

This also underlies lessons in Management :

  1. All Protocols need to be reviewed at regular intervals and see whether a new and better protocol be introduced taking into account a recent invention or scientific advancement
  2. Smaller institutions may have better protocols. So learning should be two way and the bigger institutions need not always push their ideas