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
7/29/2005:
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 = '2005-07-29'
Daily Insurance payments organized by chart number. In the example, for
7/29/2005:
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
= '2005-07-29'
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 =
'2005-07-29'
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
Production by fee schedule for one month. This is a useful report, but please
note that the amounts are taken from claims you've sent. So, it will never
include private pay, and if you billed with UCR fees showing, it will use those
fees, which are higher. Also, secondary claims are included in the results,
which might affect accuracy
SELECT
definition.ItemName,SUM(claimproc.FeeBilled) AS Production
FROM
definition,claimproc,insplan
WHERE definition.Category=7
AND
definition.DefNum=insplan.FeeSched
AND
insplan.PlanNum=claimproc.PlanNum
AND claimproc.ProcDate >=
'2005-04-01'
AND claimproc.ProcDate < '2005-05-01'
GROUP BY
definition.DefNum
ORDER BY definition.ItemOrder
Treatment planned procedures. It sorts by the cost of the procedure, which is
usually what you want. You can increase the limit to get more rows in your
results.
SELECT ProcFee,ADACode,PatNum
FROM
procedurelog
WHERE ProcStatus=1
ORDER BY ProcFee DESC
LIMIT
300
Public Health Raw Population Data (this is quite complex and can be
simplified if you don't care about broken appointments)
CREATE TEMPORARY TABLE tempbroken(
PatNum mediumint unsigned
NOT NULL,
NumberBroken smallint NOT NULL,
PRIMARY KEY
(PatNum)
);
INSERT INTO tempbroken SELECT PatNum,COUNT(*)
FROM
adjustment WHERE AdjType=14
AND AdjDate >= '2005-09-01'
AND AdjDate
<= '2005-09-30'
GROUP BY PatNum;
SELECT
patient.PatNum,MIN(procedurelog.ProcDate) AS
ProcDate,
CONCAT(provider.LName,', ',provider.FName) as
ProvName,
County,county.CountyCode,GradeSchool,school.SchoolCode,
GradeLevel,Birthdate,Race,Gender,Urgency,BillingType,
patient.NextAptNum='-1'
AS Done,
tempbroken.NumberBroken
FROM patient,provider
LEFT JOIN
procedurelog ON procedurelog.PatNum=patient.PatNum
LEFT JOIN school ON
patient.GradeSchool=school.SchoolName
LEFT JOIN county ON
patient.County=county.CountyName
LEFT JOIN tempbroken ON
tempbroken.PatNum=patient.PatNum
WHERE (procedurelog.ProcStatus='2'
AND
procedurelog.ProvNum=provider.ProvNum
AND procedurelog.ProcDate
>='2005-09-01'
AND procedurelog.ProcDate <='2005-09-30')
OR
tempbroken.NumberBroken>0
GROUP BY patient.PatNum
ORDER By
ProcDate;
DROP TABLE tempbroken;
Daily Procedures: Grouped by Procedure Code
SELECT
definition.ItemName,procedurelog.ADACode,
procedurecode.Descript,Count(*),
AVG(procedurelog.ProcFee)
AS $AvgFee,
SUM(procedurelog.ProcFee) AS $TotFee
FROM
procedurelog,procedurecode,definition
WHERE procedurelog.ProcStatus =2
AND
procedurelog.ADACode=procedurecode.ADACode
AND
definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate =
'2005-09-19'
GROUP BY procedurelog.ADACode
ORDER BY
definition.ItemOrder,procedurelog.ADACode
A list of all referrals you have received for a month (in the example, its
9/1/05 to 9/30/05). Shows how many patients referred by each source.
SELECT referral.LName, referral.FName, COUNT(*)
FROM referral,
refattach
WHERE referral.ReferralNum=refattach.ReferralNum
AND
refattach.IsFrom=1
AND refattach.RefDate >= '2005-09-01'
AND
refattach.RefDate < '2005-10-01'
GROUP BY referral.ReferralNum
Count of patients, grouped by billing type
SELECT BillingType,COUNT(*)FROM patient
WHERE PatStatus !=
4
GROUP BY BillingType
Fixes a slight database inconsistency which would cause a patient balance to
be wrong (Warning! Do NOT alter this query) Can be run as often as you
want.
DELETE FROM claimproc
WHERE
claimproc.ClaimNum!=0
AND NOT EXISTS(SELECT * FROM claim WHERE
claim.ClaimNum=claimproc.ClaimNum)
List of referral sources, how many patients referred, and how much income
from each source
SELECT
referral.LName,referral.FName,
COUNT(DISTINCT refattach.PatNum) AS
HowMany,
SUM(procedurelog.ProcFee) AS $HowMuch
FROM
referral,refattach,procedurelog
WHERE
referral.ReferralNum=refattach.ReferralNum
AND
procedurelog.PatNum=refattach.PatNum
AND refattach.IsFrom=1
AND
procedurelog.ProcStatus=2
AND procedurelog.ProcDate >='2005-01-01'
AND
procedurelog.ProcDate < '2006-01-01'
GROUP BY
referral.ReferralNum
ORDER BY HowMany Desc
List of patients with insurance benefits remaining. Example is for the year
2005 and assumes everyone has calendar year benefits. Requires version 3.9.
DROP TABLE IF EXISTS tempused;
DROP TABLE IF EXISTS
tempplanned;
CREATE TEMPORARY TABLE tempused(
PatPlanNum mediumint
unsigned NOT NULL,
AmtUsed double NOT NULL,
PRIMARY KEY
(PatPlanNum));
CREATE TEMPORARY TABLE tempplanned(
PatNum mediumint
unsigned NOT NULL,
AmtPlanned double NOT NULL,
PRIMARY KEY
(PatNum));
INSERT INTO tempused
SELECT
patplan.PatPlanNum,SUM(IFNULL(claimproc.InsPayAmt,0))
FROM
claimproc,patplan
WHERE claimproc.PatNum=patplan.PatNum
AND
claimproc.PlanNum=patplan.PlanNum
AND (claimproc.Status=1
OR
claimproc.Status=3
OR claimproc.Status=4)
AND
claimproc.ProcDate>='2005-01-01'
AND claimproc.ProcDate<
'2006-01-01'
GROUP BY patplan.PatPlanNum;
INSERT INTO
tempplanned
SELECT patient.PatNum,SUM(procedurelog.ProcFee)
FROM
patient,procedurelog
WHERE patient.PatNum=procedurelog.PatNum
AND
procedurelog.ProcStatus=1
GROUP BY patient.PatNum;
SELECT
patient.LName,patient.FName,
insplan.AnnualMax AS
$AnnualMax,
tempused.AmtUsed AS
$AmtUsed,
insplan.AnnualMax-tempused.AmtUsed AS
$AmtRemain,
tempplanned.AmtPlanned AS $TreatPlan
FROM
patient,insplan,patplan,tempused,tempplanned
WHERE
patient.PatNum=patplan.PatNum
AND insplan.PlanNum=patplan.PlanNum
AND
tempused.PatPlanNum=patplan.PatPlanNum
AND
tempplanned.PatNum=patient.PatNum
AND insplan.AnnualMax>0
AND
tempplanned.AmtPlanned>0
AND
insplan.AnnualMax-tempused.AmtUsed>0
ORDER BY tempplanned.AmtPlanned
DESC;
Fixes a slight database inconsistency that might cause insurance used
estimate to be too high. (Warning! Do NOT alter this query) Can be run as often
as you want.
UPDATE claimproc
SET InsPayEst=0
WHERE
NoBillIns=1
One user ended up with duplicate recall entries, and we're not sure how. This
query checks your database to see if you have any duplicates. A perfect database
will return no rows. If you do have repetitions, we will need to remove them for
you.
SELECT COUNT(*) AS repetitions,PatNum
FROM
recall
GROUP BY PatNum
HAVING repetitions >1
Number patients seen for a given date range (two years in the example). This
is a good way to measure active patients.
SELECT
COUNT(DISTINCT PatNum) FROM procedurelog
WHERE ProcStatus=2
AND
ProcDate>='2004-01-01'
AND ProcDate< '2006-01-01'
For Payment Plans, this lists guarantors of plans for which the date of the
first payment will soon be due. In the example, it lists all plans where the
date of the first charge is in the month of March, 2006.
SELECT patient.LName,patient.FName,MIN(payplancharge.ChargeDate) AS
FirstCharge
FROM payplancharge,patient
WHERE
payplancharge.Guarantor=patient.PatNum
GROUP BY PayPlanNum
HAVING
FirstCharge >= '2006-03-01'
AND FirstCharge < '2006-04-01'
For Payment Plans, this lists guarantors of plans for which the date of the
last payment is approaching. In the example, it lists all plans where the date
of the last charge is in the month of July, 2006.
SELECT
patient.LName,patient.FName,MAX(payplancharge.ChargeDate) AS LastCharge
FROM
payplancharge,patient
WHERE payplancharge.Guarantor=patient.PatNum
GROUP
BY PayPlanNum
HAVING LastCharge >= '2006-07-01'
AND LastCharge <
'2006-08-01'
For Payment Plans, lists all charges due for a date range and includes
billing type. Example is for the month of March.
SELECT
LName,FName,BillingType,ChargeDate
FROM payplancharge,patient
WHERE
payplancharge.Guarantor=patient.PatNum
AND ChargeDate >=
'2006-03-01'
AND ChargeDate < '2006-04-01'
ORDER BY
BillingType,LName,FName
A list of all subscibers who have a particular plan. In the example, it's for
Delta, but you can substitute your own search string between the %'s.
SELECT carrier.CarrierName,patient.*
FROM
patient,carrier,insplan
WHERE patient.PatNum=insplan.Subscriber
AND
insplan.CarrierNum=carrier.CarrierNum
AND carrier.CarrierName LIKE
'%Delta%'
ORDER BY carrier.CarrierName
Fixes a rare problem where a procedure gets attached to the appointment of a
different patient.
UPDATE appointment,procedurelog
SET
procedurelog.AptNum=0
WHERE procedurelog.AptNum=appointment.AptNum
AND
procedurelog.PatNum != appointment.PatNum
A list of referrals during a specific date range. Used to export to text file
for merge letters. Jan, 2006 in the example
SELECT
patient.PatNum, patient.LName, patient.FName, patient.MiddleI,
patient.Preferred, patient.Salutation, patient.Address, patient.Address2,
patient.City, patient.State, patient.Zip ,referral.LName as RefLName,
referral.FName as RefFName, referral.MName as RefMName, referral.Title as
RefTitle, referral.Address as RefAddress,referral.Address2 as RefAddress2,
referral.City as RefCity, referral.ST as RefST, referral.Zip as RefZip,
referral.Specialty as RefSpecialty
FROM patient,referral,refattach
WHERE
patient.PatNum=refattach.PatNum
AND
referral.ReferralNum=refattach.ReferralNum
AND refattach.IsFrom='1'
AND
refattach.RefDate >= '2006-01-01'
AND refattach.RefDate <
'2006-02-01'
AND referral.NotPerson = '0'
ORDER BY
RefSpecialty,RefLName,RefFName
An alternative way of finding procedures not billed to insurance. Only works
on small databases which were not converted from other programs. Might not be
very useful.
SELECT procedurelog.PatNum,
claimproc.ProcNum, procedurelog.ProcDate,
procedurelog.ProcStatus,
patient.LName, patient.FName,
carrier.CarrierName,
insplan.CarrierNum
FROM procedurelog
LEFT JOIN patient ON
procedurelog.PatNum = patient.PatNum
LEFT JOIN claimproc ON
procedurelog.ProcNum = claimproc.ProcNum
LEFT JOIN patplan ON
patient.PatNum=patPlan.PatNum
LEFT JOIN insplan ON patplan.PlanNum =
insplan.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum =
carrier.CarrierNum
GROUP BY procedurelog.PatNum, claimproc.ProcNum,
procedurelog.ProcDate,
procedurelog.ProcStatus, patient.LName,
patient.FName,
carrier.CarrierName, insplan.CarrierNum
HAVING
claimproc.ProcNum Is Null AND procedurelog.ProcStatus=2
ORDER BY
procedurelog.ProcDate
Patient payments for one day, ordered by type. For instance, cash, check, or
credit card.
SELECT
PayDate,PayType,PayAmt,CheckNum,BankBranch,PatNum
FROM payment
WHERE
PayDate = '2006-02-23'
ORDER BY PayAmt
Patient payments for one day grouped by type, so only the totals for each
type show.
SELECT PayDate,PayType,SUM(PayAmt)
FROM
payment
WHERE PayDate = '2006-02-23'
GROUP BY PayType
ORDER BY
PayAmt