appointment date / hire dates for salary vs other data

[trevorcampbell]

So in most of the data (CPD DB), we have a field called appointment_date (date).

In the salary data, we have a few somewhat confusing fields:

• Age @ Hire (integer)
• Start Date(9161) (date)
• SPP (date)
• Org Hire (date)

We need to answer two questions:

• which of Org Hire and Start Date(9161) correspond to appointment_date in the rest of the data?
• which of Org Hire and Start Date(9161) correspond to age @ hire?

In the response letter for the salary FOIA request from Chicago Dept HR, we get the following information:

• "Age at hire means the age the individual was when they started employment with the City"
• "A police officer is considered an employee of the City of Chicago the day they enter the Policy Academy"
• Start Date (9161) is the day an individual began as a police officer, by entering the academy"
• "SPP stands for start date in present position"
• "Org Hire stands for the first date an individual started employment in any dept within the City of Chicago"

So based on this, I would assume that Start Date(9161) should correspond to appointment_date in the rest of the data, since the rest of the data is from a CPD DB (so I would guess they won't have data on earlier hire dates with the City). Based on the explanations above, I would guess Age @ Hire is their age at the Org Hire date.

But in my own investigation, I found that Org Hire date matches more closely with appointment_date from profiles in the CPD database.

Currently the salary linking uses Org Hire as appointment_date, and compares Age @ Hire to Org Hire to get birthyear. But we may want to revisit that at some point.

[trevorcampbell]

Aside from the above, there are some odd entries. I'm mostly just adding this here for documentation purposes, so that maybe later on we can come back and hand-fix this one.

One in particular: JEFFREY GOUGIS

He appears in the rest of the data with a consistent appointment date of 2006-03-20.

In the salary data, he appears with an Org Hire (which I've labelled appointment_date tentatively) of 2006-03-20, but a Start Date (9161) of 1997-08-04. It seems like the 9161 start date should always be after the org hire date.

Then in his 2017 salary entry, the dates are swapped so that his org hire date is 1997 and officer start date is 2006. But I have salary records of him with the title POLICE OFFICER (ASSIGNED AS DETECTIVE) in 2002 and 2003.

This one is quite odd...

[trevorcampbell]

More info on this -- If I look at all salary records, I have:

• 2871 records where Start Date (9161) < Org Hire (and both exist)
• 14247 records where Start Date (9161) > Org Hire (and both exist)
• 192182 records where they both exist and are equal
• 3156 records without a Start Date (9161)
• 52 records without a Org Hire
• 212511 salary records overall

[trevorcampbell]

TODO for this issue to be detailed enough that we can start solving it / decide to do nothing: Check how much the Org Hire vs Start Date (9161) agrees with appointment_date in the rest of the data.