Common Queries

SELECT
    CASE a.status
        WHEN 0 THEN 'Created at clinic' WHEN 1 THEN 'Client - online'
        WHEN 2 THEN 'Client - mobile app' WHEN 3 THEN 'Clinic rescheduled'
        WHEN 4 THEN 'Client requested reschedule' WHEN 5 THEN 'Confirmed'
        WHEN 6 THEN 'Client cancelled' WHEN 7 THEN 'Clinic cancelled'
        WHEN 8 THEN 'Auto created' WHEN 9 THEN 'No show' ELSE 'Unknown'
    END AS appointment_status,
    COUNT(*) AS appointment_count
FROM appointment a
WHERE a.deleted_at IS NULL
  AND a.datetime >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY a.status
ORDER BY appointment_count DESC;

SELECT
    v.first_name || ' ' || v.last_name AS vet_name,
    COUNT(*) FILTER (WHERE a.status = 9)                       AS no_shows,
    COUNT(*)                                                    AS total_appointments,
    CAST(COUNT(*) FILTER (WHERE a.status = 9) AS NUMERIC(18,4))
        / NULLIF(COUNT(*), 0)                                   AS no_show_rate
FROM appointment a
JOIN vets v ON v.id = a.vet_id
WHERE a.deleted_at IS NULL
  AND a.datetime >= DATEADD(day, -90, CURRENT_DATE)
GROUP BY v.first_name, v.last_name
ORDER BY no_show_rate DESC;

SELECT
    CASE a.source WHEN 0 THEN 'Clinic' WHEN 1 THEN 'Web' WHEN 2 THEN 'Mobile app' ELSE 'Unknown' END AS booking_source,
    COUNT(*) AS appointment_count
FROM appointment a
WHERE a.deleted_at IS NULL
  AND a.datetime >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY a.source
ORDER BY appointment_count DESC;