#### Sample Stuck in Processing
-- Find samples that haven't progressed in over 2 hours
SELECT s.sample_id, s.patient_id, s.status, s.created_date, s.modified_date
FROM samples s
WHERE s.status IN ('PROCESSING', 'PENDING', 'ERROR')
AND s.modified_date < SYSDATE - (2/24);
Why Useful: 80% of user complaints involve "where is my sample" - this finds stuck samples immediately.
#### Sample Without Results
-- Samples that should have results but don't
SELECT s.sample_id, s.patient_id, s.created_date, COUNT(t.test_id) as ordered_tests, COUNT(r.result_id) as completed_results
FROM samples s
LEFT JOIN tests t ON s.sample_id = t.sample_id
LEFT JOIN results r ON t.test_id = r.test_id
WHERE s.status = 'COMPLETE'
GROUP BY s.sample_id, s.patient_id, s.created_date
HAVING COUNT(t.test_id) > COUNT(r.result_id);
Why Useful: Identifies data integrity issues where workflow status doesn't match actual completion.
#### Patient Search by Multiple Criteria
-- Flexible patient search for support calls
SELECT patient_id, first_name, last_name, dob, phone, created_date
FROM patients
WHERE (UPPER(last_name) LIKE UPPER('%{lastname}%') OR patient_id = '{patient_id}')
AND (dob = TO_DATE('{dob}', 'YYYY-MM-DD') OR '{dob}' IS NULL)
ORDER BY created_date DESC;
Why Useful: Users often provide partial information - this handles multiple search scenarios.
#### Duplicate Patient Detection
-- Find potential duplicate patients
SELECT p1.patient_id, p1.first_name, p1.last_name, p1.dob, COUNT(*) as match_count
FROM patients p1
JOIN patients p2 ON (p1.last_name = p2.last_name AND p1.first_name = p2.first_name AND p1.dob = p2.dob)
WHERE p1.patient_id != p2.patient_id
GROUP BY p1.patient_id, p1.first_name, p1.last_name, p1.dob
ORDER BY match_count DESC;
Why Useful: Duplicate patients cause sample assignment errors and workflow confusion.
#### Missing Test Definitions
-- Tests ordered but not in catalog
SELECT t.test_code, COUNT(*) as order_count, MAX(t.created_date) as last_ordered
FROM tests t
LEFT JOIN test_catalog tc ON t.test_code = tc.test_code
WHERE tc.test_code IS NULL
GROUP BY t.test_code
ORDER BY order_count DESC;
Why Useful: When users can't order tests, this identifies missing catalog entries.
#### Inactive Test Check
-- Verify test is available for ordering
SELECT tc.test_code, tc.test_name, tc.active_flag, tc.effective_date, tc.expiration_date
FROM test_catalog tc
WHERE tc.test_code = '{test_code}'
AND (tc.active_flag != 'Y' OR tc.expiration_date < SYSDATE OR tc.effective_date > SYSDATE);
Why Useful: Tests may be temporarily unavailable due to configuration issues.
#### Stalled Batches
-- Batches that should be complete but aren't
SELECT b.batch_id, b.status, b.created_date, COUNT(s.sample_id) as sample_count,
COUNT(CASE WHEN s.status = 'COMPLETE' THEN 1 END) as completed_samples
FROM batches b
JOIN samples s ON b.batch_id = s.batch_id
WHERE b.status IN ('PROCESSING', 'STARTED')
AND b.created_date < SYSDATE - (4/24)
GROUP BY b.batch_id, b.status, b.created_date
HAVING COUNT(s.sample_id) = COUNT(CASE WHEN s.status = 'COMPLETE' THEN 1 END);
Why Useful: Identifies batches where all samples are done but batch status hasn't updated.
#### Slow Query Detection
-- Find long-running queries (if query logging enabled)
SELECT sql_text, elapsed_time, executions, avg_elapsed_time
FROM v$sql
WHERE elapsed_time > 5000000 -- 5 seconds
ORDER BY elapsed_time DESC;
Why Useful: When system is slow, identifies problematic queries.
#### Table Growth Monitoring
-- Monitor table sizes for capacity planning
SELECT table_name, num_rows, avg_row_len,
ROUND(num_rows * avg_row_len / 1024 / 1024, 2) as size_mb
FROM user_tables
WHERE table_name IN ('SAMPLES', 'RESULTS', 'TESTS', 'PATIENTS')
ORDER BY size_mb DESC;
Why Useful: Proactive monitoring prevents storage-related outages.
#### User Activity Check
-- Recent user login activity
SELECT username, last_login_date, login_count, account_status
FROM user_accounts
WHERE last_login_date > SYSDATE - 30
ORDER BY last_login_date DESC;
Why Useful: When users report login issues, verify account status.
#### Orphaned Records Detection
-- Samples without valid patients
SELECT s.sample_id, s.patient_id, s.created_date
FROM samples s
LEFT JOIN patients p ON s.patient_id = p.patient_id
WHERE p.patient_id IS NULL;
Why Useful: Data corruption often shows up as orphaned foreign keys.
#### Missing Critical Data
-- Patients missing required demographics
SELECT patient_id, first_name, last_name, dob, phone
FROM patients
WHERE first_name IS NULL OR last_name IS NULL OR dob IS NULL
ORDER BY created_date DESC;
Why Useful: Incomplete patient data causes workflow failures.
#### System Status Overview
-- Overall system health snapshot
SELECT
'Samples Today' as metric, COUNT(*) as value FROM samples WHERE TRUNC(created_date) = TRUNC(SYSDATE)
UNION ALL
SELECT 'Pending Samples', COUNT(*) FROM samples WHERE status = 'PENDING'
UNION ALL
SELECT 'Error Samples', COUNT(*) FROM samples WHERE status = 'ERROR'
UNION ALL
SELECT 'Active Batches', COUNT(*) FROM batches WHERE status IN ('PROCESSING', 'STARTED')
UNION ALL
SELECT 'Results Today', COUNT(*) FROM results WHERE TRUNC(created_date) = TRUNC(SYSDATE);
Why Useful: Single query gives you immediate system health overview for daily checks.
1. System Status Overview
2. Sample Status Issues
3. Stalled Batches
1. Patient Search queries
2. Sample Status queries
3. Test Availability checks
1. Performance queries
2. Data integrity checks
3. Table growth monitoring
1. Slow Query Detection
2. Orphaned Records Detection
3. Missing Critical Data checks
---
Created: 2025-08-06
Purpose: Support team diagnostic toolkit