---
title: Common Queries
slug: redshift/6Rpr-common-queries
icon: {"lucideIcon":"ScanSearch"}
docTags: 
createdAt: 2026-10-01T12:34:12.758Z
---

### New file case analysis

Closed SOAP records by vet, with linked sale value:

```sql
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:

```sql
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:

```sql
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)

```sql
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;
```