LIMS SQL Troubleshooting Queries
Critical Diagnostic Queries for Support
1. Sample Status Issues (Most Common)
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.
2. Patient Lookup Problems
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.
3. Test Availability Issues
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.
4. Batch Processing Problems
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.
5. Performance Diagnostic Queries
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.
6. Security and Access Issues
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.
7. Data Integrity Checks
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.
8. Quick Health Check Query
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.
Query Usage Priority
Daily Monitoring (Run these every morning)
- System Status Overview
- Sample Status Issues
- Stalled Batches
User Complaint Response (Use when tickets come in)
- Patient Search queries
- Sample Status queries
- Test Availability checks
Proactive Maintenance (Weekly)
- Performance queries
- Data integrity checks
- Table growth monitoring
Emergency Response (During outages)
- Slow Query Detection
- Orphaned Records Detection
- Missing Critical Data checks
Created: 2025-08-06
Purpose: Support team diagnostic toolkit