3 SQL Queries Every Clinic Manager Should Have

Most clinic managers run their business off a handful of canned reports from their EHR. Those reports are fine for billing audits, but they rarely answer the operational questions that decide whether the clinic is healthy: Are we filling our slots? Are providers being utilized? Are claims getting paid on time?

The good news is that the data is already there. With three SQL queries — run directly against your EHR's reporting database or a warehouse copy — you can stop guessing.

1. Same-Day Cancellation & No-Show Rate by Provider

A 15% no-show rate sounds bearable in aggregate. Broken out by provider, you'll often find one or two with a 30%+ rate — usually because of how their slots are scheduled, not because their patients are flakier.

SELECT
  p.provider_name,
  COUNT(*) AS total_appts,
  SUM(CASE WHEN a.status IN ('no_show', 'cancelled_same_day') THEN 1 ELSE 0 END) AS lost_appts,
  ROUND(100.0 * SUM(CASE WHEN a.status IN ('no_show', 'cancelled_same_day') THEN 1 ELSE 0 END) / COUNT(*), 1) AS lost_pct
FROM appointments a
JOIN providers p ON p.provider_id = a.provider_id
WHERE a.scheduled_date >= DATEADD(month, -3, CURRENT_DATE)
GROUP BY p.provider_name
ORDER BY lost_pct DESC;

What to watch: any provider above your clinic average by 5 percentage points or more. That's usually a scheduling-template problem, not a patient problem.

2. Provider Utilization (Booked vs Available Hours)

If your providers' calendars look full but your revenue is flat, this is the query that explains the gap.

SELECT
  p.provider_name,
  SUM(s.available_minutes) / 60.0 AS available_hours,
  SUM(s.booked_minutes) / 60.0 AS booked_hours,
  ROUND(100.0 * SUM(s.booked_minutes) / NULLIF(SUM(s.available_minutes), 0), 1) AS utilization_pct
FROM schedule_slots s
JOIN providers p ON p.provider_id = s.provider_id
WHERE s.slot_date BETWEEN DATEADD(month, -1, CURRENT_DATE) AND CURRENT_DATE
GROUP BY p.provider_name
ORDER BY utilization_pct ASC;

What to watch: utilization under 70% almost always means the schedule template is too generous (too many "buffer" or admin blocks) or your no-shows aren't being backfilled. Either way, it's recoverable revenue.

3. Days from Service to Payment (by Payer)

The single best leading indicator of cash-flow problems. If one payer is silently drifting from a 22-day cycle to a 38-day cycle, you want to catch it in week one, not at month-end.

SELECT
  pa.payer_name,
  COUNT(*) AS claims_paid,
  ROUND(AVG(DATEDIFF(day, c.service_date, pmt.payment_date)), 1) AS avg_days_to_pay,
  ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY DATEDIFF(day, c.service_date, pmt.payment_date)), 1) AS p90_days_to_pay
FROM claims c
JOIN payments pmt ON pmt.claim_id = c.claim_id
JOIN payers pa ON pa.payer_id = c.payer_id
WHERE pmt.payment_date >= DATEADD(month, -2, CURRENT_DATE)
GROUP BY pa.payer_name
ORDER BY p90_days_to_pay DESC;

What to watch: the P90 column. The average will lie to you — one or two slow-paying payers can drag the whole AR. Looking at the 90th percentile shows you the tail risk.

Where to Run These

If your EHR exposes a reporting database (Epic Clarity, Cerner CCL/HealtheAnalytics, Athena's data exports, eClinicalWorks SQL views), you can run these directly. If not, the smallest useful step is a daily export into a single-table warehouse (Postgres, Snowflake, BigQuery — any of them work) so you're not pounding the production OLTP system.

At A2Z Data Inc, we set up exactly this kind of analytics layer on top of EHR systems — quietly, without disrupting clinical workflows. If your reports are answering the wrong questions, let's talk.