Common Queries

7 min

Vets per clinic

SELECT
    c.name AS clinic_name,
    v.first_name || ' ' || v.last_name AS vet_name,
    cv.vet_role_id,
    cv.is_available_for_appointments,
    cv.is_part_time
FROM clinicvetmapping cv
JOIN clinics c ON c.id = cv.clinic_id
JOIN vets v    ON v.id = cv.vet_id
WHERE cv.deleted_at IS NULL
ORDER BY c.name, v.last_name;

Room utilization

Appointments booked per room over the trailing 30 days — useful for spotting an under- or over-booked room before it becomes a scheduling bottleneck:

SELECT
    r.name AS room_name,
    COUNT(a.id) AS appointments_booked
FROM rooms r
LEFT JOIN appointment a
    ON a.room_id = r.id
    AND a.deleted_at IS NULL
    AND a.datetime >= DATEADD(day, -30, CURRENT_DATE)
WHERE r.deleted_at IS NULL
GROUP BY r.name
ORDER BY appointments_booked DESC;

Calendar event volume by category

Non-appointment calendar entries (blocked time, SOAP-linked tasks), grouped by category:

SELECT
    dc.name AS category,
    COUNT(*) AS event_count
FROM custom_events ce
JOIN diary_categories dc ON dc.id = ce.diary_category_id
WHERE ce.deleted_at IS NULL
  AND ce.datetime_start >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY dc.name
ORDER BY event_count DESC;

If custom_events doesn't carry a direct diary_category_id on your schema version, confirm the actual join column before relying on this — it wasn't in the original column list this page was built from and is inferred from the table's purpose.

Notification send rate

Sent vs. pending notifications by type, over the trailing 7 days:

SELECT
    np.notification_type,
    COUNT(*) FILTER (WHERE np.is_sent = TRUE)  AS sent,
    COUNT(*) FILTER (WHERE np.is_sent = FALSE) AS pending,
    COUNT(*)                                   AS total
FROM notification_periods np
WHERE np.send_at >= DATEADD(day, -7, CURRENT_DATE)
GROUP BY np.notification_type
ORDER BY total DESC;

Clinic notification configuration

What's configured to send, and when, per clinic:

SELECT
    clinic_id,
    notification_type,
    period,
    period_type
FROM clinic_notification_periods
WHERE deleted_at IS NULL
ORDER BY clinic_id, notification_type;

Vet roles in use

SELECT
    rfvp.title,
    rfvp.immutable,
    COUNT(cv.vet_id) AS vets_assigned
FROM roles_for_vet_permissions rfvp
LEFT JOIN clinicvetmapping cv ON cv.vet_role_id = rfvp.id
GROUP BY rfvp.title, rfvp.immutable
ORDER BY vets_assigned DESC;