##The Problem Like any large organization, the City of Asheville needs to track time off accruals for the approximately 1100 employees that work for the city. This includes vacation time, sick leave, and compensatory time (aka Comp time) for both exempt and non-exempt employees. These accruals represent a liability for the organization and managers need to track comp time in particular to make sure that large numbers of comp hours are not accrued by individual employees. In particular, if an employee has more than 40 hours of accrued comp time, it should be flagged for review.
The goal of this exercise is to analyze accrual data (see below) and deliver that analysis and any related visualizations. Part of the solution should allow a division manager or department director to review accrual balances for their employees. Additional insights or visualizations are welcome.
Once the data are organized to provide meaningful metrics/visualizations, we will need to monitor accruals on an ongoing basis. Ideally, each manager or department director could easily review accrual balances with a live data feed that is constantly updated.
##Data
Employee Time Off Accrual Data can all be found here: https://drive.google.com/folderview?id=0B-uuat4PtUk1VmwyTGdBb29xaEE&usp=sharing
Files include the following tables:
- Employee Master - employee data
- Accrual Balances - actual hours accrued by accrual type
- Accrual Types - types of accrual balances
- Department to Division Map - relates department and division codes
Keep in mind that you may not need to use all data in each table. These data come from our ERP system.
##Rules
Keep it simple. We are not looking for a finished product but want to learn a little more about your skills and how you approach problems. Think of your delivery as something “mininmal” that is on the road to awesome. You may submit results via GitHub, Google Drive, DropBox, (or any similar file sharing service) or any way that you prefer.
- Move the data into a MSSQL Server Database. You can download SQL Server Express from Microsoft for free. If you’d rather approach this differently, let us know why.
- Use any reporting tool you like or any other visualization tools/techniques.
- You may use, borrow anything you want but the work should be done by you.
- You may email us with any questions. sbarnwell@ashevillenc.gov or use this repo’s issues.
##Submissions
Detach and send us the database.
Send us any reports or other visualizations that you think would be helpful.
Feel free to send us any documentation or comments on design, set-up, or your approach to the problem.
Have fun!