Common Queries
4 min
New file case analysis
Closed SOAP records by vet, with linked sale value:
SELECT
v.first_name || ' ' || v.last_name AS vet_name,
COUNT(*) AS closed_cases,
CAST(SUM(s.total) AS NUMERIC(18,2)) AS linked_sale_total
FROM new_file_cases nfc
JOIN vets v ON v.id = nfc.responsible_id
LEFT JOIN sale s ON s.id = nfc.sale_id
WHERE nfc.status = 3 -- closed
AND nfc.deleted_at IS NULL
AND nfc.closed_at >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY v.first_name, v.last_name
ORDER BY closed_cases DESC;Diagnoses
Most common diagnosis text on closed records — free-text, so this groups on exact string match, which will undercount if vets phrase the same diagnosis differently:
SELECT
nfc.diagnosis,
COUNT(*) AS occurrences
FROM new_file_cases nfc
WHERE nfc.status = 3
AND nfc.deleted_at IS NULL
AND nfc.diagnosis IS NOT NULL
AND nfc.diagnosis != ''
GROUP BY nfc.diagnosis
ORDER BY occurrences DESC
LIMIT 50;If you need this cleanly categorized rather than free-text, check whether your clinic uses the diagnostics reference list operationally — if so, that structured selection may be captured in a custom field via new_field_values rather than in new_file_cases.diagnosis itself; confirm your clinic's specific SOAP template before assuming either path.
Visit types
Volume by visit type, joining SOAP records to the appointment's service:
SELECT
av.name AS visit_type,
COUNT(*) AS visit_count
FROM new_file_cases nfc
JOIN appointment a ON a.id = nfc.appointment_id
JOIN available_services av ON av.id = a.visit_type_id
WHERE nfc.deleted_at IS NULL
AND nfc.opened_at >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY av.name
ORDER BY visit_count DESC;Treatment plans (prescriptions issued)
SELECT
p.name AS product_name,
pr.quantity,
pr.max_refills,
pr.status,
pr.prescribed_at
FROM prescriptions pr
JOIN products p ON p.id = pr.product_id
WHERE pr.deleted_at IS NULL
AND pr.prescribed_at >= DATEADD(day, -30, CURRENT_DATE)
ORDER BY pr.prescribed_at DESC;