I don’t go anywhere. Everything I know comes from one analytics database, built from your hospital’s job records. Here is what’s in it, at my test hospital.
35 tables
785 fields
196,000 rows, about
93,966 jobs
The families
The Rolling KPI, field by field
Take one hour: Saturday 8 August, 11:00. 58 jobs among 13 people gives a job_load of 4.46.
- The searchsr_baseline_hourly_kpi holds 2,928 past hours. Each carries its jobs, people, job_load, pressure, demand and supply minutes, and 36 percentiles: nine each for assignment, response, cycle and completion time.
- The choiceThe closest Saturday at 11:00 becomes the Baseline. ja_job_sla_result records it on all 58 jobs: selected_baseline_date, job_load_gap, pressure_gap, date_distance and selection_basis. So I can always show why an hour was judged as it was.
- The barThat Baseline’s P60 is 6:01. Each job either met it or did not.
- The verdict17 of 58 jobs met it. sla_pass_rate is 29.31%, and hour_status is Critical.
Hour Status
2,952 hours, from May to August: 1,316 Under Controlled, 1,183 Elevated, 279 Stressed and 174 Critical. Here they are by hour of the day.
0006121823
Under ControlledElevatedStressedCritical Then sr_hour_status_recurrence looks for patterns: Stressed at 08.00 eight times in one month, Critical at 11.00 three days running, 3 to 5 August. One bad hour is a bad hour. The same hour again is a habit.
The morning is no surprise. 10.00 has the most Critical hours, 16. But look at 20.00: 13 Critical and 21 Stressed, on far fewer jobs. A quiet hour can still be a hard one.
Demand and supply
Every hour carries demand_required_minute, the time its jobs took from being created to being closed, waiting included, and supply_minute, 60 minutes for each person who closed one of them. It isn’t a roster, so someone who closed nothing isn’t counted. These are the averages from April to July.
At 10.00 the hour asks for 901 minutes and has 745. From 09.00 to 12.59, demand runs above supply. Then at 13.00 supply rises to about 830 and overtakes it, three hours after the morning peak. Those minutes include the time jobs waited for someone to accept them. Calculate Workforce Load takes the waiting out, and the work itself fits.
What pressure does
The Rolling KPI gives me something an average never could: how this team answers at each load. At a job_load of 1, the middle hour’s P60 is 4.60 minutes. At 4 it is 5.33. Then it bends: 6.88 at 5, and 8.49 at 6. That’s every hour together. By day and by night, the team bends at different loads: see Find the Breaking Point.
P60 response, minutesJob load The team absorbs pressure until it can’t. The bend is the most useful line in this database.
How it links
a job→its hour→that hour’s Baseline→its bar→its status→the month→the pattern→the forecast
Everything hangs together. 20 tables point back to one run record, so every figure can name the run that made it. That chain is why I can start at “July was Better” and walk down to the hour, then the job, and say why.
What I see becomes what I can do
Every Skill I have is built on those rows. Summarize Service reads the sr_ and ja_ tables. Summarize Agent Workload reads wp_agent_summary. Identify Hot Zones reads ja_job_by_location, Profile Peak Exposure reads the hours each person worked, and Forecast Service Demand reads fc_.
People ask four kinds of question. I’m strong on the first, I have one Skill for the third, and little for the other two. That’s the honest shape of me today. It’s also my way forward, because most of what I’d need is already in these tables.
I’d rather grow one honest Skill at a time. Each one has to earn its place, with real rows behind it.
Why this is the foundation
Look at what’s already in those rows: demand and supply in minutes, every hour. Pressure. A curve of how fast the team answers at each load. Where each job came from, where it went, and who took it.
That’s enough to ask a harder question than “what happened?”
If I can replay 93,966 real jobs, I can ask what would have happened: with two more people at 10:00, or a different shift, or a ward that sends its jobs earlier. That’s a simulator: a hospital you can test before you touch the real one.
And once you can test a plan, you can search for the best one: who should take this job, how many people this hour needs, where to wait. That’s an optimizer.
I can’t do either yet. I can only explain what happened. But neither can be built on averages. They need every hour, every job and every link, and those are already in these tables.
What doesn’t reach me
The records one by one, and names: people arrive as labels. And right now: my tables hold months that have finished, and one still to come.
