HOME

Back to Manual Table of Contents

Query Examples

As we write custom queries for various reasons, they get posted here so that you can use them.

All claims sent on a given day:
SELECT * FROM claim
WHERE DateSent ='2005-04-19'
AND ClaimStatus='S'

All treatment planned procedures, ordered by patient
SELECT PatNum,ADACode,ProcFee,Surf,ToothNum
FROM procedurelog
WHERE ProcStatus=1
ORDER BY PatNum

Birthday postcards for a one week range. In the example, from 10/06 to 10/13:
SELECT LName,FName,Address,Address2,City,State,Zip,Birthdate
FROM patient
WHERE SUBSTRING(Birthdate,6,5) >= '10-06'
AND SUBSTRING(Birthdate,6,5) <= '10-13'
AND PatStatus=0
ORDER BY LName,FName

Daily patient payments organized by chart number. In the example, for 9/29/2004:
SELECT payment.PayDate,patient.ChartNumber,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS plfname,
payment.PayType,payment.CheckNum,payment.PayAmt
FROM payment,patient
WHERE
payment.PatNum = patient.PatNum
&& payment.PayAmt > 0
&& payment.PayDate = '2004-09-29'

Daily Insurance payments organized by chart number. In the example, for 8/27/2004:
SELECT patient.ChartNumber,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS Name,
claimpayment.CheckDate,
carrier.CarrierName,claimpayment.CheckNum,
claimproc.ClaimNum,SUM(claimproc.InsPayAmt) as $Amt
FROM claimpayment,claimproc,insplan,patient,carrier
WHERE claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum
AND claimproc.PlanNum = insplan.PlanNum
AND claimproc.PatNum = patient.PatNum
AND carrier.CarrierNum = insplan.CarrierNum
AND (claimproc.Status = '1' OR claimproc.Status = '4')
AND claimpayment.CheckDate = '2004-08-27'
GROUP BY claimproc.ClaimNum

Aging report which includes date of last payment
SELECT CONCAT(LName,', ',FName,' ',MiddleI)
,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90
,BalTotal,InsEst,BalTotal-InsEst AS $pat,
DATE_FORMAT(MAX(paysplit.ProcDate),'%m/%d/%Y') AS lastPayment
FROM patient
LEFT JOIN paysplit
ON paysplit.PatNum=patient.PatNum
WHERE (patstatus != 2)
AND (Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005' OR BalTotal < '-.005')
GROUP BY patient.PatNum
ORDER BY LName,FName

Aging report which includes chart numbers
SELECT ChartNumber,CONCAT(LName,', ',FName,' ',MiddleI) AS Patient
,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90
,BalTotal,InsEst,BalTotal-InsEst AS $pat
FROM patient WHERE (patstatus != 2)
AND (Bal_0_30 > '.005' OR Bal_31_60 > '.005' OR Bal_61_90 > '.005' OR BalOver90 > '.005'
OR BalTotal < '-.005')
ORDER BY LName,FName

Daily procedures report which includes chart numbers
SELECT procedurelog.ProcDate,patient.ChartNumber,CONCAT(patient.LName,
', ',patient.FName,' ',patient.MiddleI) AS plfname, procedurelog.ADACode,
procedurelog.ToothNum,procedurecode.Descript,provider.Abbr,
procedurelog.ProcFee-SUM(claimproc.WriteOff) AS $fee
FROM procedurelog,patient,procedurecode,provider
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7'
WHERE procedurelog.ProcStatus = '2'
AND patient.PatNum=procedurelog.PatNum
AND procedurelog.ADACode=procedurecode.ADACode
AND provider.ProvNum=procedurelog.ProvNum
AND procedurelog.ProcDate = '2004-09-28'
GROUP BY procedurelog.ProcNum
ORDER BY procedurelog.ProcDate,plfname

The account balances for all patients with an appointment on a specific day:
SELECT appointment.AptDateTime,patient.LName,patient.FName,patient.EstBalance
FROM appointment,patient
WHERE appointment.PatNum=patient.PatNum
AND appointment.AptDateTime LIKE '2005-02-28%'
AND AptStatus != 6
AND AptStatus != 3

All lab cases with a status of received, and appointment not complete
SELECT PatNum,AptDateTime FROM appointment
WHERE Lab=2 AND AptStatus!=2
ORDER BY AptDateTime