Queries on this page are run from the User Query window. As we write custom queries for various reasons, they get posted here so that you can use them. Some of them might not work because of changes to the database structure.
1. All claims sent on a given day:
SELECT * FROM claim
WHERE DateSent ='2005-04-19'
AND ClaimStatus='S'
2. All treatment planned procedures, ordered by patient. See also #50 & #56..
SELECT PatNum,ProcCode,ProcFee,Surf,ToothNum
FROM procedurelog,procedurecode
WHERE ProcStatus=1
AND procedurelog.CodeNum=procedurecode.CodeNum
ORDER BY PatNum
3. 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
4. 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'
5. 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
6. 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
7. 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
8. Daily procedures report which includes chart numbers
SET @FromDate= '2008-01-01', @ToDate='2008-01-31'; /*change dates here*/
SELECT pl.ProcDate,pa.ChartNumber,CONCAT(pa.LName,', ',pa.FName,' ',pa.MiddleI) AS PatName, ProcCode,
pl.ToothNum,pc.Descript,pr.Abbr,pl.ProcFee-SUM(claimproc.WriteOff) AS $fee
FROM patient pa,procedurecode pc, provider pr, procedurelog pl
LEFT JOIN claimproc ON pl.ProcNum=claimproc.ProcNum
AND claimproc.Status='7'
WHERE pl.ProcStatus = '2'
AND pl.CodeNum=pc.CodeNum
AND pa.PatNum=pl.PatNum
AND pr.ProvNum=pl.ProvNum
AND pl.ProcDate >=@FromDate
AND pl.ProcDate <=@ToDate
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate,PatName
9. 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
10. All lab cases with a status of received, and appointment not complete
SELECT PatNum,AptDateTime,AptStatus FROM appointment a
WHERE AptStatus!='2' AND (SELECT COUNT(*) FROM labcase c WHERE c.AptNum=a.AptNum)>0
ORDER BY AptDateTime
11. 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
12. 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,ProcCode,PatNum
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE ProcStatus=1
ORDER BY ProcFee DESC
LIMIT 300
13. 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;
14. Daily Procedures: Grouped by Procedure Code
SET @OnDate= '2007-08-21';
SELECT definition.ItemName,procedurecode.ProcCode,
procedurecode.Descript,Count(*),
AVG(procedurelog.ProcFee) AS $AvgFee,
SUM(procedurelog.ProcFee) AS $TotFee
FROM procedurelog,procedurecode,definition
WHERE procedurelog.ProcStatus =2
AND procedurelog.CodeNum=procedurecode.CodeNum
AND definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate = @OnDate
GROUP BY procedurecode.ProcCode
ORDER BY definition.ItemOrder,procedureCode.ProcCode;
15. 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
16. For public health clinics, the production by gradeschool.
SELECT SUM(procedurelog.ProcFee),patient.GradeSchool
FROM procedurelog,patient
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcStatus=2
AND procedurelog.ProcDate >= '2005-10-01'
AND procedurelog.ProcDate < '2005-11-01'
GROUP BY patient.GradeSchool
ORDER BY patient.GradeSchool
17. For public health clinics, the number of patients seen at each grade
school for a date range.
SELECT patient.GradeSchool,COUNT(DISTINCT patient.PatNum)
AS patients
FROM patient,procedurelog
WHERE patient.PatNum=procedurelog.PatNum
AND procedurelog.ProcDate >= '2005-10-01'
AND procedurelog.ProcDate < '2005-11-01'
GROUP BY patient.GradeSchool
18. Count of patients, grouped by billing type
SELECT BillingType,COUNT(*)FROM patient
WHERE PatStatus != 4
GROUP BY BillingType
20. 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
21. List of patients with insurance benefits remaining. Assumes everyone
has calendar year benefits.
DROP TABLE IF EXISTS tempused;
CREATE TEMPORARY TABLE tempused(
PatPlanNum mediumint unsigned NOT NULL,
AmtUsed double NOT NULL,
PRIMARY KEY (PatPlanNum));
DROP TABLE IF EXISTS tempplanned;
CREATE TEMPORARY TABLE tempplanned(
PatNum mediumint unsigned NOT NULL,
AmtPlanned double NOT NULL,
PRIMARY KEY (PatNum));
DROP TABLE IF EXISTS tempannualmax;
CREATE TEMPORARY TABLE tempannualmax(
PlanNum mediumint unsigned NOT NULL,
AnnualMax double NOT NULL);
INSERT INTO tempused
SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayAmt,0))
FROM claimproc
LEFT JOIN patplan ON patplan.PatNum = claimproc.PatNum
AND patplan.PlanNum =
claimproc.PlanNum
WHERE claimproc.Status IN (1, 3, 4)
AND claimproc.ProcDate BETWEEN makedate(year(curdate()), 1)
AND makedate(year(curdate())+1, 1) /*current calendar year*/
GROUP BY patplan.PatPlanNum;
INSERT INTO tempplanned
SELECT patient.PatNum, SUM(procedurelog.ProcFee)
FROM procedurelog
LEFT JOIN patient ON patient.PatNum =
procedurelog.PatNum
WHERE procedurelog.ProcStatus = 1 /*treatment planned*/
GROUP BY patient.PatNum;
INSERT INTO tempannualmax
SELECT benefit.PlanNum, benefit.MonetaryAmt
FROM benefit, covcat
WHERE covcat.CovCatNum = benefit.CovCatNum
AND benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND covcat.EbenefitCat=1
AND benefit.MonetaryAmt <> 0
ORDER BY benefit.PlanNum;
SELECT patient.LName, patient.FName,Address,City,State,Zip,
LPAD(FORMAT(tempannualmax.AnnualMax, 2), 12, " ") AS "Annual
Max",
LPAD(FORMAT(tempused.AmtUsed, 2), 12, " ") AS "Amount
Used",
LPAD(FORMAT(tempannualmax.AnnualMax-tempused.AmtUsed, 2), 12, " ")
AS "Amt Remaining",
LPAD(FORMAT(tempplanned.AmtPlanned, 2), 12, " ") AS "Treatment
Plan"
FROM patient,tempannualmax,patplan,tempused,tempplanned
WHERE patient.PatNum=patplan.PatNum
AND tempannualmax.PlanNum=patplan.PlanNum
AND tempused.PatPlanNum=patplan.PatPlanNum
AND tempplanned.PatNum=patient.PatNum
AND tempannualmax.AnnualMax>0
AND tempplanned.AmtPlanned>0
AND tempannualmax.AnnualMax-tempused.AmtUsed>0
ORDER BY tempplanned.AmtPlanned DESC;
22. 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'
23. 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'
24. 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'
25. 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
26. 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. DOES NOT GIVE patients who have the plan, see #118
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
27. 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
28. 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
29. 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
30. 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
31. A list of all outstanding preauthorizations
SELECT *
FROM claim
WHERE ClaimType = 'PreAuth'
AND ClaimStatus != 'R'
ORDER BY DateService
32. List of writeoffs for a daterange and for one provider. Leave
out the line containing ProvNum to get for all providers
SELECT PatNum,ProvNum,PlanNum,WriteOff AS $Amt,DateCP,DateEntry FROM claimproc
WHERE WriteOff >0
AND DateCP >=
'2006-01-01'
AND DateCP < '2006-04-01'
AND ProvNum=1
ORDER BY DateCP
33. Quarterly Production by billing type. Does not include adjustments.
SELECT YEAR(procedurelog.ProcDate)AS "Year", QUARTER(procedurelog.ProcDate) AS "Quarter", definition.ItemName AS "Billing Type", SUM(procedurelog.ProcFee) AS "$Production"
FROM procedurelog, patient, definition
WHERE ProcStatus = 2 /* complete procs*/
AND YEAR(procedurelog.ProcDate) = 2006 /* change year as needed */
AND procedurelog.PatNum =
patient.PatNum
AND definition.DefNum =
patient.BillingType
GROUP BY QUARTER(procedurelog.ProcDate), patient.BillingType
ORDER BY QUARTER(procedurelog.ProcDate), patient.BillingType
34. Calculates the total income from each carrier in a given period
of time. Might be useful for comparing 1099s received from insurance
at the end of the year.
SELECT CarrierName, SUM(CheckAmt) AS $Income
FROM claimpayment
WHERE CheckDate >= '2006-01-01'
AND CheckDate < '2007-01-01'
GROUP BY CarrierName
35. List of families seen in the last three years. Useful for generating
a list of patients for Christmas cards. After saving the resulting
datafile, you would use the letter merge feature of Word to actually
print the cards or labels. The first name is not very useful, since
it might be anyone in the family. You would probably address it as <<LName>> Household,
or something similar.
SELECT LName,FName, Address, Address2, City, State, Zip
FROM patient
WHERE PatStatus=0
/*only patients with procedures within the last three years*/
AND EXISTS(SELECT * FROM procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.ProcDate > CURDATE() - INTERVAL 3 YEAR)
GROUP BY Guarantor
ORDER BY LName,FName
36. A list of patients seen between two dates. Submitted by Jorge
Bonilla, DMD.
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.lname, patient.fname,
patient.address, patient.address2, patient.city, patient.state, patient.zip
from patient, procedurelog
WHERE procedurelog.PatNum =
patient.PatNum
AND patient.patstatus = '0'
AND procedurelog.procdate > '2005-01-01'
AND procedurelog.procdate < '2006-12-31'
GROUP BY patient.PatNum
ORDER BY patient.lname
37. A list of all guarantors with an active status. This is another
way of getting a Christmas card list without filtering out patients
who have not been in for a while.
SELECT LName, FName, Address, Address2, City,
State, Zip
FROM patient
WHERE PatStatus=0
GROUP BY Guarantor
ORDER BY LName
38. List and count
of patients that were referred to you and who sent them. Change
the dates to get the date range you want. Submitted by Jorge Bonilla,
DMD.
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.PatNum, patient.lname,
patient.fname, refattach.referralnum from patient, procedurelog,
refattach
WHERE procedurelog.PatNum = patient.PatNum
AND refattach.PatNum = patient.PatNum
AND patient.patstatus = '0'
AND procedurelog.procdate > '2005-01-01'
AND procedurelog.procdate < '2006-12-31'
GROUP BY patient.PatNum
ORDER BY patient.PatNum
39. List of patients and their addresses who
have not been seen since a certain date (the example uses
Sept 1, 2005). It will also give you the date
of their last visit.
Submitted by Jorge Bonilla, DMD
SELECT patient.LName, patient.FName, patient.Address, patient.Address2,
patient.City, patient.State, patient.Zip, procedurelog.procdate
FROM patient,procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.procstatus = '2'
GROUP BY procedurelog.PatNum
HAVING MAX(procdate) < '2005-09-01'
40. List of all patients, their balances, and their most recent payments.
SELECT patient.PatNum, Concat(patient.LName,', ',patient.FName), patient.Bal_0_30,
patient.Bal_31_60, patient.Bal_61_90, patient.BalOver90, patient.BalTotal,
payment.PayAmt, payment.PayDate
FROM patient
LEFT JOIN payment ON payment.PatNum=patient.PatNum
AND payment.PayDate=(SELECT MAX(payment2.PayDate)
FROM payment AS payment2
WHERE payment.PatNum = payment2.PatNum)
ORDER BY patient.PatNum;
41. List of subscribers for a given carrier and groupnum. Note subscribers, not patients. See 118 also
SELECT carrier.CarrierName,patient.PatNum
FROM patient,carrier,insplan
WHERE patient.PatNum=insplan.Subscriber
AND insplan.CarrierNum=carrier.CarrierNum
AND carrier.CarrierName LIKE 'Blue%'
AND insplan.GroupNum LIKE '%409%'
ORDER BY carrier.Name,carrier.GroupNum,patient.LName
42. Sums of Aging report without having to print the entire report.
SELECT SUM(Bal_0_30) as 'Total 0-30', SUM(Bal_31_60) as 'Total
31-60',
SUM(Bal_61_90) as 'Total 61-90', SUM(BalOver90) as 'Total BalOver90',
SUM(BalTotal) as 'TOTAL of BALANCES', SUM(InsEst) as 'Total Ins.
Est.', SUM(BalTotal-InsEst) AS 'Total Pat. Est.'
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')
43. Appointment history for one patient. Change the 581 to the
appropriate PatNum before running.
SELECT a.AptStatus, DATE_FORMAT(a.AptDateTime,
'%m-%d-%Y') as 'Date', DATE_FORMAT(a.AptDateTime, '%l:%i %p') as 'Time',
CHAR_LENGTH(a.Pattern)*(SELECT ValueString FROM preference WHERE PrefName =
'AppointmentTimeIncrement')
AS 'Min', ProcDescript as 'Procedures', Note as 'Notes' From appointment
a WHERE a.PatNum = '581'
44. Payment plan information
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT DISTINCT pp.Guarantor, pp.PayPlanNum,
(SELECT MIN(ChargeDate) FROM payplancharge WHERE pp.PayPlanNum=PayPlanNum AND ChargeDate >= CurDate()) AS 'NextPayDate',
(SELECT SUM(Principal) FROM payplancharge WHERE pp.PayPlanNum=PayPlanNum) as 'OrigLoanAmount',
(SELECT SUM(SplitAmt) FROM paysplit WHERE pp.PayPlanNum=PayPlanNum) as 'SumPayMade'
FROM payplan pp, patient, payplancharge ppc
WHERE pp.Guarantor=patient.PatNum AND pp.PayPlanNum=ppc.PayPlanNum;
UPDATE tmp SET SumPayMade=0 WHERE IsNull(SumPayMade);
SELECT tmp.Guarantor, tmp.PayPlanNum, OrigLoanAmount, SumPayMade,
(OrigLoanAmount-SumPayMade) as 'Remaining', NextPayDate, ppc.principal as NextPayPrinc,
ppc.interest AS NextPayInt, p.EstBalance as 'PastDue',
CONCAT(p.Address, p.Address2, " ", p.City, ", ", p.State, " ", p.zip) as Address
FROM tmp, patient p, payplancharge ppc
WHERE p.PatNum=tmp.Guarantor
AND ppc.PayPlanNum = tmp.PayPlanNum
AND tmp.NextPayDate=ppc.ChargeDate;
45. For a user set time period, this query will return the number of procedures, the total fees, and the percentages of each for each category of dental codes.
SET @FromDate= '2007-08-21', @ToDate='2007-08-22'; /*change dates here*/ SET @TotNum=0, @TotFee=0; DROP TABLE IF EXISTS tmp1; DROP TABLE IF EXISTS tmp2; /*Create a temp table containing summary info by procedure*/ CREATE TABLE tmp1 SELECT MID(procedurecode.ProcCode,2,4) AS 'cat', Count(*) as 'num',
SUM(procedurelog.ProcFee) as 'fee'
FROM procedurelog,procedurecode,definition
WHERE procedurelog.ProcStatus =2
AND procedurelog.CodeNum=procedurecode.CodeNum
AND definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate >= @FromDate
AND procedurelog.ProcDate <= @ToDate
GROUP BY cat
ORDER BY cat;
/*Create a temp table grouping the procedures and info by range*/ CREATE TABLE tmp2 (Category CHAR(40), Fees FLOAT NOT NULL, Number FLOAT NOT NULL, PercIncome FLOAT NOT NULL, PercQuant FLOAT NOT NULL); /*Create Prepared insert Statements and execute*/ /*A do loop could be used here but would not neccesarily work on all DB types*/ PREPARE ins FROM 'INSERT INTO tmp2 (Category, Fees, Number) SELECT ?, SUM(Fee), SUM(num) FROM tmp1 WHERE cat>? AND cat<?'; SET @cat='Diagnostic', @StCode=99, @EndCode=1000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Preventative', @StCode=999, @EndCode=2000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Restorative', @StCode=1999, @EndCode=3000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Endodontics', @StCode=2999, @EndCode=4000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Periodontics', @StCode=3999, @EndCode=5000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Prosthodontics (Removable)', @StCode=4999, @EndCode=5900; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Maxillofacial Prosthestics', @StCode=5899, @EndCode=6000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Implant Services', @StCode=5999, @EndCode=6200; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Prosthodontics (Fixed)', @StCode=6199, @EndCode=7000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Oral and Maxillofacial Surgery', @StCode=6999, @EndCode=8000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Orthodontics', @StCode=7999, @EndCode=9000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Adjunctive General Services', @StCode=8999, @EndCode=10000; EXECUTE ins USING @cat, @StCode, @EndCode; /*Insert totals, can't use procedure when changing @variable values*/ INSERT INTO tmp2 (Category, Fees, Number) SELECT 'Total', @TotFee:=SUM(fee), @TotNum:=SUM(num) FROM tmp1 WHERE cat>99 AND cat<10000; /*Cacluate the percentages*/ UPDATE tmp2 SET PercIncome= CASE WHEN @TotFee = 0 THEN 0 ELSE
FORMAT(100*Fees/@TotFee,2) END;
UPDATE tmp2 SET PercQuant= CASE WHEN @TotNum = 0 THEN 0 ELSE
FORMAT(100*Number/@TotNum,2) END;
/*Display Results*/
SELECT * FROM tmp2;
DROP TABLE IF EXISTS tmp1; DROP TABLE IF EXISTS tmp2;
46. Mailing information for guarantors of active
patients.
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT DISTINCT Guarantor FROM patient WHERE PatStatus
=0;
SELECT LName, FName, Address, Address2,
City, State, Zip FROM patient, tmp
WHERE patient.PatNum=tmp.Guarantor
AND Length(ZIP)>4
ORDER BY LName;
47. Daily payments summarized by type
SET @date='2007-09-25';
SELECT definition.ItemName AS PaymentType,
SUM(paysplit.SplitAmt) AS PaymentAmt
FROM payment,definition,paysplit
WHERE paysplit.DatePay=@date
AND payment.PayNum=paysplit.PayNum
AND definition.DefNum=payment.PayType
GROUP BY payment.PayType
UNION
SELECT 'Ins Checks',
SUM(claimproc.InsPayAmt) AS InsAmt
FROM claimproc
WHERE claimproc.DateCP=@date
AND (claimproc.Status=1 OR claimproc.Status=4)
48. New patients for a time span. Excludes ones who made an appointment, but then no-showed.
SET @FromDate='2008-03-01', @ToDate='2008-03-31';
SET @pos=0;
SELECT @pos:=@pos+1 AS numberofpatients, patient.PatNum, patient.DateFirstVisit
FROM patient, procedurelog
WHERE procedurelog.PatNum = patient.PatNum
AND patient.patstatus = '0'
AND procedurelog.procdate BETWEEN @FromDate
AND @ToDate
AND procedurelog.procstatus=2
AND patient.DateFirstVisit > @FromDate
AND procedurelog.procfee > 0
GROUP BY patient.PatNum
ORDER BY patient.DateFirstVisit
49. Patients who haven't been in for 5 years. Submitted by Jorge Bonilla, DMD.
SET @pos=0;
SELECT @pos:=@pos+1 as numberofpatients, patient.LName,patient.FName,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip, MAX(procedurelog.procdate)
FROM patient,procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND procedurelog.procstatus = '2'
GROUP BY procedurelog.PatNum
HAVING MAX(procdate) < '2003-01-01'
ORDER BY patient.address, patient.address2
50. Treatment planned procedures, not scheduled apt (includes no apt or planned apt only or unscheduled list)see also #56
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) As Patient,
pc.ProcCode as 'Code', abbrdesc as 'Description', DATE_FORMAT(pl.procdate,'%m-%d-%Y') AS 'Date',
ap.AptStatus, ProcFee
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE (isnull(ap.aptnum) OR AptStatus=6 OR AptStatus=3)
ORDER BY aptstatus, patient.LName, patient.FName ASC;
51. Answers the question: during a given period, what is the production generated by different sources (like ads, signage, yellow pages, other referrals) including residual (that is, even if the initial referral was made during a different period)
Summary Fees, Adjustments and Writeoffs by patient name(PatNum) with first visit date sorted by referrer
SET @FromDate='2007-08-21', @ToDate='2007-08-21';
DROP TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS tmp2;
CREATE TABLE tmp SELECT CONCAT(patient.LName, ', ', patient.FName) AS 'PatientName',DATE_FORMAT(patient.DateFirstVisit,
'%m-%d-%Y') AS 'FirstVisit', CONCAT( tmpRef.LName, ', ', tmpRef.FName) AS "Referror", SUM(pl.ProcFee) AS SumFee,
(SELECT SUM(AdjAmt) FROM adjustment a
WHERE a.PatNum=patient.PatNum AND a.AdjDate >= @FromDate AND a.AdjDate <= @ToDate) AS SumAdjust,
(SELECT SUM(WriteOff) FROM claim c
WHERE c.PatNum=patient.PatNum AND c.DateService >= @FromDate AND c.DateService <= @ToDate) AS SumWriteOff, patient.PatNum
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum AND pl.ProcStatus = 2 AND pl.ProcDate >= @FromDate AND pl.ProcDate <= @ToDate
LEFT JOIN
(SELECT ItemOrder, refattach.ReferralNum, LName, FName, refattach.PatNum FROM refattach INNER JOIN referral ON refattach.ReferralNum = referral.ReferralNum)
tmpRef ON patient.PatNum=tmpRef.PatNum AND tmpRef.ItemOrder = 1
GROUP BY patient.PatNum ORDER BY 'Referror', 'PatientName';
DROP TABLE IF EXISTS tmpRef;
CREATE TABLE tmp2 SELECT CONCAT(patient.LName, ', ', patient.FName) AS 'PatientName',DATE_FORMAT(patient.DateFirstVisit,
'%m-%d-%Y') AS 'FirstVisit', CONCAT(tmpRef.LName, ', ', tmpRef.FName ) AS "Referror", FORMAT(0,2) AS SumFee,
(SELECT SUM(AdjAmt) FROM adjustment a
WHERE a.PatNum=patient.PatNum AND a.AdjDate >= @FromDate AND a.AdjDate <= @ToDate) AS SumAdjust,
(SELECT SUM(WriteOff) FROM claim c
WHERE c.PatNum=patient.PatNum AND c.DateService >= @FromDate AND c.DateService <= @ToDate) AS SumWriteOff, patient.PatNum
FROM patient
LEFT JOIN
(SELECT ItemOrder, refattach.ReferralNum, LName, FName, refattach.PatNum FROM refattach INNER JOIN referral ON refattach.ReferralNum = referral.ReferralNum)
tmpRef ON patient.PatNum=tmpRef.PatNum AND tmpRef.ItemOrder = 1
GROUP BY patient.PatNum ORDER BY 'Referror', 'PatientName';
INSERT INTO tmp (PatientName, FirstVisit, Referror, SumFee, SumAdjust, SumWriteOff, PatNum)
SELECT tmp2.PatientName, tmp2.FirstVisit, tmp2.Referror, tmp2.SumFee, tmp2.SumAdjust, tmp2.SumWriteOff, tmp2.PatNum FROM tmp2
LEFT JOIN tmp ON tmp2.PatNum= tmp.PatNum WHERE tmp.PatNum IS NULL AND (tmp2.SumAdjust <>0 OR tmp2.SumWriteOff <>0);
INSERT INTO tmp (PatientName, FirstVisit, Referror, SumFee, SumAdjust, SumWriteOff)
SELECT CONCAT('Totals: ',COUNT(DISTINCT PatientName), ' Patients') as 'PatientName',
CONCAT(DateDiff(@ToDate,@FromDate)+1, ' Days') AS 'FirstVisit',
CONCAT(COUNT(DISTINCT Referror), ' Referrors') AS 'Referror',
SUM(SumFee) AS 'SumFee', SUM(SumAdjust) AS 'SumAdjust', SUM(SumWriteOff) AS 'SumWriteOff' FROM tmp;
SELECT PatientName, FirstVisit, Referror, FORMAT(SumFee,2) AS SumFee,
FORMAT(SumAdjust,2) AS SumAdJust, FORMAT(SumWriteOff,2) AS SumWriteOff
FROM tmp WHERE (SumAdjust <>0 OR SumWriteOff <>0 OR SumFee <>0);
DROP TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS tmp2;
52. Return list of statements sent by mail in last 30 days, or other interval
SELECT CONCAT(pat.LName, ', ', pat.FName) AS "Patient",
DATE_FORMAT(MAX(DATE(s.DateSent)),'%m/%d/%Y') AS "Last Stmt Date",
DATE_FORMAT(DATE(ADDDATE(MAX(DATE(s.DateSent)),
INTERVAL 30 DAY)),'%m/%d/%Y') AS "Due Date"
FROM patient pat, statement s
WHERE s.PatNum = pat.PatNum AND DATE(s.DateSent) > DATE(ADDDATE(CURDATE(), INTERVAL -30 DAY))
AND s.Mode_ = 0 /*mail*/
GROUP BY pat.PatNum
ORDER BY
"Patient";
For VERSIONS 5.5 and previous use
SELECT CONCAT(pat.LName, ', ', pat.FName) AS
"Patient",
DATE_FORMAT(MAX(DATE(comm.CommDateTime)),'%m/%d/%Y') AS "Last
Stmt Date", DATE_FORMAT(DATE(ADDDATE(
MAX(DATE(comm.CommDateTime)), INTERVAL
30 DAY)),'%m/%d/%Y') AS "Due Date"
FROM patient pat, commlog comm
WHERE
comm.PatNum = pat.PatNum
AND DATE(comm.CommDateTime) >
DATE(ADDDATE(CURDATE(), INTERVAL -30 DAY))
AND comm.CommType=0
/*stmt*/
AND comm.Mode_ = 2
/*mail*/
AND comm.SentOrReceived = 1
/*sent*/
GROUP BY pat.PatNum
ORDER BY
"Patient";
53. Production for date range with pay splits and tooth and surface for date range
SET @FromDate='2008-01-01' ,
@ToDate='2008-01-15';
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) As
Patient, pc.proccode, pl.procdate, pl.procfee, ps.splitamt, surf, toothnum FROM
patient
INNER JOIN procedurelog pl ON
patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum=
pc.CodeNum
LEFT JOIN paysplit ps ON pl.ProcNum=ps.ProcNum
WHERE pl.ProcDate >=@FromDate
AND pl.ProcDate <= @ToDate;
ORDER BY patient.LName, patient.FName ASC;
54. Payments by procedure for date range
SET @FromDate='2007-01-01' ,
@ToDate='2007-01-15';
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) As
Patient, pc.proccode, pl.procdate, pl.procfee, ps.splitamt FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN
procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN paysplit ps ON
pl.ProcNum=ps.ProcNum
WHERE pl.ProcDate >= @FromDate
AND pl.ProcDate <=@ToDate
ORDER BY patient.LName, patient.FName ASC;
55. All insurance claimed procedures with UCR fee for date range, even if a different fee was used or sent
usefull for comparisons, you can also add the column pl.ProcFee to see what was actually charged
SET @FromDate='2007-01-01' , @ToDate='2007-01-15';
SELECT
claim.PatNum,DateService,ProvTreat,pc.ProcCode,cp.InsPayEst,f.Amount AS 'UCR
FEE'
FROM claim
INNER JOIN claimproc cp ON
claim.ClaimNum=cp.ClaimNum
INNER JOIN procedurelog pl ON
cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum=
pc.CodeNum
INNER JOIN fee f ON pc.CodeNum=f.CodeNum
INNER JOIN definition
d ON f.FeeSched=d.DefNum AND d.ItemName='Standard'
WHERE DateService >=
@FromDate
AND DateService <=@ToDate;
56. Returns all treatment planned procedures for active patients without a scheduled OR planned apt, with phone nums
usefull for those transitioning to planned appointments
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) AS
Patient, hmphone, wkphone, wirelessphone, SUM(procfee) AS '$FeeSum',
Concat(Address, ' ', Address2) AS Address, City, State, Zip FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN
procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1
LEFT JOIN
appointment ap ON patient.PatNum=ap.PatNum AND (ap.AptStatus=1 OR ap.AptStatus=6
)
WHERE ap.AptNum IS NULL AND patient.PatStatus=0
GROUP BY
patient.PatNum
ORDER BY patient.LName, patient.FName
ASC;
57. Payments received after a certain date, with chart number, sorted by carrier
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
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
DATE(claimpayment.CheckDate) > '2007-10-29'
GROUP BY claimproc.ClaimNum
ORDER By CarrierName;
58. Received Lab Cases with sent and received date with patient number (use raw Selection option to see PatNum)
SELECT patient.PatNum, CONCAT(LName, ', ',FName, ' ', MiddleI) As Patient ,
DateTimeSent, DateTimeRecd
FROM labcase
LEFT JOIN patient ON labcase.PatNum=patient.PatNum WHERE DateTimeRecd >
DateTimeSent;
59. Patient lifetime revenue and TP'd procedure totals (also
includes PatNum and patient age, demographic marketing query)
SELECT PatNum, Round(DATEDIFF( CURDATE(), Birthdate)/365) AS AGE, (SELECT SUM(SplitAmt)
FROM paysplit WHERE paysplit.PatNum=patient.PatNum) AS '$Lifetime Revenue',
(SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=1 AND
procedurelog.PatNum=patient.PatNum) AS '$Treatment Planned' FROM
patient ORDER BY LName;
60. Treatment Planned Procedures with Referror and Insurance
SELECT pl.PatNum,pc.ProcCode,pl.ProcFee,
pl.Surf,pl.ToothNum,CASE WHEN (patient.HasIns='I') THEN 'Yes' ELSE 'No' END
AS HasInsurance, CONCAT(tmpRef.LName, ', ', tmpRef.FName) AS "Referror"
FROM patient
INNER JOIN
procedurelog pl ON patient.PatNum=pl.PatNum AND pl.ProcStatus = 1
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN (SELECT ItemOrder, refattach.ReferralNum, LName, FName, refattach.PatNum
FROM refattach INNER JOIN referral ON refattach.ReferralNum =referral.ReferralNum) tmpRef
ON patient.PatNum=tmpRef.PatNum AND tmpRef.ItemOrder = 1
ORDER BY patient.LName, patient.FName ASC;
DROP TABLE IF EXISTS tmpRef;
61. Query scheduled procedures for code with date range (remove
aptstatus constraint to return all appointment types)
SET @CODE ='%D0120%';
SET @pos=0, @StartDate='2008-01-01' , @EndDate='2008-01-15';
SELECT @pos:=@pos+1 AS 'Count', CONCAT(LName, ', ',FName, ' ', MiddleI) As
Patient, pc.proccode, ap.AptDateTime, ap.AptStatus
FROM patient
INNER
JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN
procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON
pl.AptNum=ap.AptNum AND ap.AptStatus=1
AND proccode LIKE (@Code)
AND
DATE(ap.AptDateTime) >= @StartDate
AND DATE(ap.AptDateTime) <=
@EndDate
AND AptStatus=1
ORDER BY aptstatus,
patient.LName, patient.FName ASC;
62. Patients payments by type with patient name (PatNum) for a day
Replaces info lost with new reports (5.4?)
SET @date='2008-01-22';
SELECT
paysplit.PatNum,definition.ItemName AS PaymentType,
SUM(paysplit.SplitAmt)
AS PaymentAmt
FROM payment,definition,paysplit
WHERE paysplit.DatePay=@date
AND payment.PayNum=paysplit.PayNum
AND definition.DefNum=payment
GROUP BY paysplit.PatNum
UNION
SELECT PatNum, 'Ins Checks', SUM(claimproc.InsPayAmt) AS PaymentAmt
FROM
claimproc
WHERE claimproc.DateCP=@date
AND (claimproc.Status=1 OR
claimproc.Status=4)
GROUP BY PatNum;
63. Removes ALL optional patient ids from the insurance USE WITH CAUTION
UPDATE patplan set PatID='';
64. Number of active patients of each age
DROP TABLE IF EXISTS tmp1;
CREATE TABLE tmp1 SELECT
IF((YEAR(CURDATE()) - YEAR(p.birthdate)) > 0, (YEAR(CURDATE()) -
YEAR(p.birthdate)) -
(MID(CURDATE(), 6, 5) < MID(p.birthdate, 6, 5)),
IF((YEAR(CURDATE()) - YEAR( p.birthdate)) < 0,
(YEAR(CURDATE()) -
YEAR(p.birthdate)) + (MID(p.birthdate, 6, 5) < MID(CURDATE(), 6, 5)),
(YEAR(CURDATE()) - YEAR(p.birthdate)))) AS AgeCount
FROM patient p where
patstatus=0;
SELECT AgeCount AS 'Age', COUNT(AgeCount) AS '# Active Patients'
FROM tmp1 GROUP BY AgeCount;
DROP TABLE IF EXISTS tmp1;
65. Shows all records with duplicate first and last name
TO see PatNum, choose 'raw' option button in query
window
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp
SELECT COUNT(*) AS count,
LName, FName FROM patient WHERE patstatus<>4 GROUP BY LName, FName HAVING
count>1;
SELECT p.* FROM tmp, patient p
WHERE tmp.LName=p.LName AND
tmp.FName=p.FName AND patstatus<>4
ORDER BY LName,FName;
DROP TABLE
IF EXISTS tmp;
66. Active patients list who have seen a hygenist within a
date range
SET @pos=0, @FromDate='2008-01-01' , @ToDate='2008-01-15';
SELECT @pos:=@pos+1 AS numberofpatients, pa.PatNum, MAX(procdate) AS 'Last
Seen',
FROM patient pa, procedurelog pl, provider pv
WHERE pl.PatNum =
pa.PatNum
AND pl.ProvNum=pv.ProvNum
AND pv.specialty=1
AND
pa.patstatus = '0'
AND pl.procdate BETWEEN @FromDate
AND @ToDate
AND
pl.procstatus=2
GROUP BY pa.PatNum
ORDER BY
pa.Lname;
67. Subscribers grouped by employer, gives # of active
patients who are subscribers with insurance through each employer
SELECT EmpName AS 'Employer', COUNT(EmpName) AS '# Emp Patients' FROM insplan
i, employer e, patient p WHERE p.PatNum=i.subscriber AND
i.EmployerNum=e.EmployerNum AND p.PatStatus=0 GROUP BY EmpName
subscribers grouped by employer, lists active patients who are subscribers with insurance through each employer
SELECT EmpName AS 'Employer', PatNum FROM insplan i, employer e, patient p
WHERE p.PatNum=i.subscriber AND
i.EmployerNum=e.EmployerNum AND p.PatStatus=0 ORDER By
EmpName;
68. New patients for date range with ref source and sum of first visit fees
>>will not return patients
with no fees on first visit date or if there is no referral source listed
(designed for lending bank reporting req)
SET @pos=0,
@FromDate='2008-01-01' , @ToDate='2008-01-31';
SELECT p.PatNum,
datefirstvisit, sum(procfee),
r.LName AS RefLName, r.FName AS
RefFName
FROM patient p ,referral r ,refattach ra, procedurelog pl
WHERE
p.PatNum=ra.PatNum
AND r.ReferralNum=ra.ReferralNum
AND
ra.IsFrom='1'
AND p.PatNum=pl.PatNum
AND
date(pl.procdate)=date(p.datefirstvisit)
AND pl.procdate BETWEEN @FromDate
AND @ToDate AND procstatus='2'
GROUP BY p.PatNum
ORDER BY p.lname
69. Count of patients by Carrier with procedures
completed in date range
SET @Start='2007-01-01' ,
@Before='2008-01-01';
/*Adjust above Dates AS needed*/
SELECT
carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients'
FROM
carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER
JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON
claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON
claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON
procedurelog.CodeNum=procedurecode.CodeNum
AND procedurelog.ProcDate >=
@Start
AND procedurelog.ProcDate < @Before
AND ProcStatus=2
GROUP BY
CarrierName
ORDER BY
CarrierName;
70. Income by insurance company for date range
SET
@Start='2007-01-01' , @Before='2008-01-01';
/*Adjust above Dates as
needed*/
SELECT CarrierName, SUM(CheckAmt) AS $Income
FROM
claimpayment
WHERE CheckDate >= @Start
AND CheckDate <
@Before
GROUP BY CarrierName;
71. Carrier and patient list seen in date range
SET @Start='2007-01-01' , @Before='2008-01-01';
/*Adjust above Dates as needed*/
SET @pos=0;
SELECT @pos:=@pos+1 AS Count, carrier.CarrierName, patient.PatNum
FROM carrier, insplan, claim, claimproc, patient
WHERE insplan.PlanNum=claim.PlanNum AND
insplan.CarrierNum=carrier.CarrierNum AND
claimproc.ClaimNum=claim.ClaimNum AND
claimproc.PatNum=patient.PatNum AND
/*this is in case we need additional patient information*/
/*you can add any patient or insurance fields to the SELECT statement*/
ProcDate >= @Start AND
ProcDate < @Before
GROUP BY patient.Patnum, CarrierName
ORDER BY CarrierName;
72. Count of active patients seen in a date range grouped by billing type
SELECT billingtype, COUNT(DISTINCT patient.PatNum) AS Count
FROM patient, procedurelog
WHERE procedurelog.Patnum = patient.PatNum AND
patient.PatStatus = '0' AND
procedurelog.ProcDate > '2008-01-01' AND
procedurelog.ProcDate < '2008-12-31' AND
procedurelog.ProcStatus=2
GROUP BY BillingType
73. Recieved Preauths over last X months
SET @EndDate=CurDate();
SET @StartDate=(CurDate()- INTERVAL 3 MONTH);
SELECT PatNum, DateSent, DateReceived, ClaimFee, InsPayEst, ClaimNote
FROM claim
WHERE claimtype='PreAuth' AND claimstatus='R' AND
DateReceived>=@StartDate AND
DateReceived<=@EndDate;
74. Insured Patients with insurance information
SELECT patient.PatNum, patient.Guarantor,carrier.CarrierName, patient.PriProv,insplan.GroupName
FROM carrier, insplan, patient
WHERE insplan.CarrierNum= carrier.CarrierNum
AND insplan.Subscriber= patient.PatNum
AND patient.Status= 0
ORDER BY patient.lname;
75. Patient payments in date range
SET @StartDate='2007-02-21';
SET @EndDate='2008-02-21';
SET @PatientNumber=6179;
SELECT * FROM paysplit WHERE
DatePay>=@StartDate AND
DatePay<=@EndDate AND
PatNum=@PatientNumber;
76. New patients in date range with address and ref source
SET @pos=0, @FromDate='2008-01-01' , @ToDate='2008-01-31';
SELECT p.PatNum, DATE_FORMAT(p.DateFirstVisit,
'%m-%d-%Y') AS FirstVisit,
CONCAT(p.Address, p.Address2) as Address,
p.city,p.state,p.zip,
r.LName as RefLName, r.FName as RefFName
FROM patient p
LEFT JOIN refattach ra ON p.PatNum=ra.PatNum
LEFT JOIN referral r ON r.ReferralNum=ra.ReferralNum AND ra.IsFrom='1'
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
ORDER BY p.LName
77. Returns guranators of patients who have not recieved an email with a specific word in subject line within 45 days who have an appointment in a date range
It is too specific for general use, but gives an idea of how you can use a query to make sure you have sent registration information by email.
SET @DateStart='2008-01-01' , @DateEnd='2008-01-31';
SELECT patient.PatNum
FROM patient
INNER JOIN patient pg ON patient.Guarantor=pg.PatNum
INNER JOIN appointment ON patient.PatNum=appointment.PatNum
AND aptstatus=1
AND aptdatetime>=@DateStart
AND aptdatetime>=@DateEnd
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum
AND procstatus=2
LEFT JOIN emailmessage e ON pg.PatNum=e.PatNum
AND e.MsgDateTime>=DATE_SUB(curdate(), INTERVAL 45 DAY)
AND e.Subject LIKE('%Welcome%')
WHERE ISNULL(procedurelog.ProcDate) AND
ISNULL(e.Subject);
78. Patients with no scheduled apt who have not been in for a time period (X months) with the date of their last scheduled apt.
This is useful for making a patient list before archiving patients, to call and try one last time.
You can also make this into a mailing list simply by changing the patient fields to include address etc.
DROP TABLE IF EXISTS tmp1, tmp2;
SET @pos=0;
CREATE TABLE tmp1 SELECT DISTINCT PatNum FROM appointment WHERE AptStatus=1;
CREATE TABLE tmp2 SELECT PatNum, MAX(AptDateTime) AS 'AptDateTime' FROM appointment WHERE AptStatus=2 GROUP BY PatNum;
SELECT @pos:=@pos+1 as numberofpatients, patient.PatNum, patient.HmPhone, tmp2.AptDateTime AS LastApt,
(TO_DAYS(curdate()) - TO_DAYS(tmp2.AptDateTime)) AS 'DaysSince'
FROM patient
LEFT JOIN tmp1 ON patient.PatNum=tmp1.PatNum
LEFT JOIN tmp2 ON patient.PatNum=tmp2.PatNum
WHERE tmp1.PatNum IS NULL
AND (tmp2.AptDateTime)
ORDER BY patient.LName, patient.FName ASC;
DROP TABLE IF EXISTS tmp1, tmp2;
79. Completed procedures for given code for date range with note, other columns easily added
SET @CODE ='D1111';
SET @StartDate='2007-01-01' , @EndDate='2008-02-15';
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) As Patient, pc.proccode,
pl.procdate, SSN, pn.note
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN procnote pn ON pl.procnum=pn.procnum
AND proccode=@CODE
AND DATE(pl.procdate ) >= @StartDate
AND DATE(pl.procdate ) <= @EndDate
AND procstatus=2
ORDER BY patient.LName, patient.FName ASC;
80. Insurace claim procedures with UCR fee, InsEst and InsAmtPaid.txt
SET @StartDate='2007-01-01' , @EndDate='2008-02-15';
SELECT claim.PatNum,DateService,ProvTreat,pc.ProcCode,cp.InsPayEst,f.Amount as 'UCR FEE', cp.inspayamt
FROM claim
INNER JOIN claimproc cp ON claim.ClaimNum=cp.ClaimNum
INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN fee f ON pc.CodeNum=f.CodeNum
INNER JOIN definition d ON f.FeeSched=d.DefNum AND d.ItemName='Standard'
WHERE DateService>=@StartDate AND
DateService<=@EndDate
81. All treatment planned and scheduled, all treatment planned total, returns these two dollar amounts, active patients
Note: the latter amount includes the former
/*This is a point in time query, what do I have scheduled, what do I have TP'd for active patients*/
DROP TABLE IF EXISTS tmp1; /*Create a temp table containing summary info by procedure*/
CREATE TABLE tmp1
SELECT 'TPd And Scheduled' AS ProcCondition, sum(ProcFee) AS $TotalFees FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
AND AptStatus=1 AND PatStatus=0;
INSERT INTO tmp1(ProcCondition, $TotalFees)
SELECT 'Treatment Planned' as ProcCondition, sum(ProcFee) as $TotalFees FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE (isnull(ap.aptnum) OR AptStatus=6 OR AptStatus=3)
AND PatStatus=0;
/*Display Results*/
SELECT * FROM tmp1;
DROP TABLE IF EXISTS tmp1;
82. Date Range limited: treatment planned and scheduled, treatment planned total, returns these two dollar amounts for all patients
The date range of the first number is applied to the appointment scheduled date, the date range for the second amount limits by what date the TP procedures were made
Note: the date you TP a procedure was not captured until version 5.5, so use this with caution
/*Caution: the results will change depending on how long after the period you run the query*/
SET @StartDate='2008-01-01' , @EndDate='2008-01-15';
DROP TABLE IF EXISTS tmp1; /*Create a temp table containing summary info by procedure*/
CREATE TABLE tmp1
SELECT 'TPd And Scheduled' AS ProcCondition, SUM(ProcFee) AS $TotalFees FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
AND DATE(pl.DateTP) >= @StartDate
AND DATE(pl.DateTP) <= @EndDate
AND AptStatus=1;
INSERT INTO tmp1(ProcCondition, $TotalFees)
SELECT 'Treatment Planned' as ProcCondition, sum(ProcFee) as $TotalFees FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE (isnull(ap.aptnum) OR AptStatus=6 OR AptStatus=3)
AND DATE(pl.DateTP) >= @StartDate
AND DATE(pl.DateTP) <= @EndDate;
/*Display Results*/
SELECT * FROM tmp1;
DROP TABLE IF EXISTS tmp1;
83. Patients from city seen in date range, theoretically helpful for splitting practice
/*Caution: the results will change depending on how long after the period you run the query*/
SET @StartDate='2008-01-01' , @EndDate='2008-01-15';
DROP TABLE IF EXISTS tmp1; /*Create a temp table containing summary info by procedure*/
CREATE TABLE tmp
SELECT 'TPd And Scheduled' AS ProcCondition, SUM(ProcFee) AS $TotalFees FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
AND DATE(pl.DateTP) >= @StartDate
AND DATE(pl.DateTP) <= @EndDate
AND AptStatus=1;
INSERT INTO tmp1(ProcCondition, $TotalFees)
SELECT 'Treatment Planned' as ProcCondition, sum(ProcFee) as $TotalFees FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE (isnull(ap.aptnum) OR AptStatus=6 OR AptStatus=3)
AND DATE(pl.DateTP) >= @StartDate
AND DATE(pl.DateTP) <= @EndDate;
/*Display Results*/
SELECT * FROM tmp1;
DROP TABLE IF EXISTS tmp1;
SELECT DISTINCTROW patient.PatNum, patient.city FROM procedurelog
Inner Join Patient ON procedurelog.PatNum=patient.PatNum
WHERE ProcStatus=2
AND ProcDate>='2007-09-21'
AND ProcDate< '2008-03-21'
AND patient.City Like('%Orlando%')
84. Scheduled procedures with associated appointments and patients for date range for given code
SET @StartDate='2008-04-01', @EndDate='2008-04-30', @Code='%D0120%';
SELECT pa.PatNum, pc.ProcCode, pl.ProcFee AS $Fee, ap.AptDateTime FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE DATE(ap.AptDateTime) >= @StartDate
AND DATE(ap.AptDateTime) <= @EndDate
AND AptStatus=1
AND pc.ProcCode LIKE(@Code)
ORDER BY pa.LName;
DROP TABLE IF EXISTS tmp;
85. Patient and insurance Payments for date range for given provider
SET @Fromdate='2008-01-01', @Todate='2008-01-31' ;
SET @ProviderAbbreviation='DENTIST NAME ABBR HERE';
CREATE TABLE tmp SELECT paysplit.PatNum, definition.ItemName AS PaymentType, ProvNum, paysplit.DatePay as 'Date',
SUM(paysplit.SplitAmt) AS PaymentAmt
FROM payment,definition,paysplit
WHERE paysplit.DatePay>=@Fromdate
AND paysplit.DatePay<=@Todate
AND payment.PayNum=paysplit.PayNum
AND definition.DefNum=payment.PayType
GROUP BY paysplit.PatNum, definition.ItemName
UNION
SELECT PatNum, 'Ins Checks' as PaymentType,ProvNum, claimproc.DateCP AS 'Date', SUM(claimproc.InsPayAmt) as PaymentAmt
FROM claimproc
WHERE claimproc.DateCP>=@Fromdate
AND claimproc.DateCP<=@Todate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY PatNum;
SELECT tmp.PatNum, PaymentType, Abbr, 'Date', PaymentAmt from tmp, patient, provider
WHERE tmp.PatNum=patient.PatNum and provider.ProvNum=tmp.ProvNum
AND Abbr=@ProviderAbbreviation
ORDER BY Date ASC, patient.LName;
DROP TABLE IF EXISTS tmp;
86. Adjustments summed by type
SET @Start='2008-01-01' , @End='2008-01-31';
/*Adjust above Dates as needed*/
SELECT AdjType, SUM(AdjAmt) FROM adjustment
WHERE AdjDate >=@Start
AND AdjDate <=@End
GROUP BY AdjType
ORDER BY SUM(AdjAmt);
87. Count of patients seen in date range by carrier
SET @Start='2007-01-01' , @Before='2008-01-01';
/*Adjust above Dates as needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients'
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND procedurelog.ProcDate >= @Start
AND procedurelog.ProcDate < @Before
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
88. Sum of payments made by carrier and sum of payments with that insurance
SET @Fromdate='2008-01-01', @Todate='2008-01-31' ;
/*Adjust above Dates as needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients'
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND procedurelog.ProcDate >= @Start
AND procedurelog.ProcDate < @Before
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
89. SET @Fromdate='2008-01-01', @Todate='2008-01-31' ;
SET @ProviderAbbreviation='DENTIST NAME ABBR HERE';
CREATE TABLE tmp SELECT paysplit.PatNum, definition.ItemName AS PaymentType, ProvNum, paysplit.DatePay as 'Date',
SUM(paysplit.SplitAmt) AS PaymentAmt
FROM payment,definition,paysplit
WHERE paysplit.DatePay>=@Fromdate
AND paysplit.DatePay<=@Todate
AND payment.PayNum=paysplit.PayNum
AND definition.DefNum=payment.PayType
GROUP BY paysplit.PatNum, definition.ItemName
UNION
SELECT PatNum, 'Ins Checks' as PaymentType,ProvNum, claimproc.DateCP AS 'Date', SUM(claimproc.InsPayAmt) AS PaymentAmt
FROM claimproc
WHERE claimproc.DateCP>=@Fromdate
AND claimproc.DateCP<=@Todate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY PatNum;
SELECT tmp.PatNum, PaymentType, Abbr, 'Date', PaymentAmt FROM tmp, patient, provider
WHERE tmp.PatNum=patient.PatNum AND provider.ProvNum=tmp.ProvNum
AND Abbr=@ProviderAbbreviation
ORDER BY Date ASC, patient.LName;
DROP TABLE IF EXISTS tmp;
90. Insurance income for time period summed by guarantor and carrier
SET @FromDate='2008-01-01', @ToDate='2008-01-31' ;
SELECT carrier.CarrierName, patient.Guarantor,SUM(claimproc.InsPayAmt)
FROM patient
INNER JOIN insplan ON insplan.Subscriber=patient.PatNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum
WHERE patient.PatStatus=0 AND
claimproc.DateCP>=@Fromdate AND
claimproc.DateCP<=@Todate AND
(claimproc.Status=1 OR claimproc.Status=4)
GROUP BY patient.Guarantor, CarrierName
ORDER BY CarrierName
91. Aging Report for Balance over 90 with no payment in last 30
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT CONCAT(LName,', ',FName,' ',MiddleI) AS PatName
,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90
,BalTotal,InsEst,BalTotal-InsEst AS $pat,
MAX(paysplit.ProcDate) AS LastPayment
FROM patient
LEFT JOIN paysplit
ON paysplit.PatNum=patient.PatNum
WHERE (patstatus != 2)
AND ( BalOver90 > '.005')
GROUP BY patient.PatNum
ORDER BY LName,FName;
SELECT PatName
,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90
,BalTotal,InsEst, $pat,
DATE_FORMAT(LastPayment,'%m/%d/%Y') AS LastPayment FROM tmp WHERE DATE(LastPayment)<(CURDATE()- INTERVAL 30 DAY);
DROP TABLE IF EXISTS tmp;
92. Find an Insurance check by Check Number
SET @Check='%123%';
SELECT patient.PatNum,
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 claimpayment.CheckNum LIKE @Check
GROUP BY claimproc.ClaimNum ORDER By CarrierName;
93. Insurance and Patient income for time period summed by guarantor and carrier
NOTE:Patient income will be repeated for multiple insurance carriers in family
SET @FromDate='2007-01-01', @ToDate='2008-01-31' ;
SELECT carrier.CarrierName, patient.Guarantor,SUM(claimproc.InsPayAmt),
(SELECT SUM(paysplit.SplitAmt) FROM paysplit WHERE paysplit.PatNum=patient.PatNum AND paysplit.DatePay
BETWEEN @FromDate AND @ToDate GROUP BY paysplit.PatNum) AS "PatPayment"
FROM patient
INNER JOIN insplan ON insplan.Subscriber=patient.PatNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum
WHERE patient.PatStatus=0 AND
claimproc.DateCP>=@Fromdate AND
claimproc.DateCP<=@Todate AND
(claimproc.Status=1 OR claimproc.Status=4)
GROUP BY Guarantor, CarrierName
ORDER BY CarrierName;
94. Gives Count of Insured and not Insured New Patients for date range
SET @FromDate='2007-01-01', @ToDate='2008-01-31' ;
SELECT SUM(Z.INSUREDCOUNT) AS InsuredCountByDate, SUM(Z.NOTINSUREDCOUNT) AS NotInsuredCountByDate
FROM
(
SELECT COUNT(DISTINCT patnum) AS INSUREDCOUNT, 0 AS NOTINSUREDCOUNT
FROM procedurelog
WHERE patnum IN (
SELECT A.patnum
FROM patient A, patplan B
WHERE A.patnum = B.patnum AND A.datefirstvisit BETWEEN @FromDate AND @ToDate AND B.Ordinal=1 )
UNION
SELECT 0 AS INSUREDCOUNT, COUNT(DISTINCT patnum) AS NOTINSUREDCOUNT
FROM procedurelog
WHERE patnum IN (
SELECT patnum
FROM patient
WHERE patnum NOT IN (SELECT patnum FROM patplan) AND datefirstvisit BETWEEN @FromDate AND @ToDate )
) Z;
95. EXPORT PATIENT DATA TO IMPORT INTO ANOTHER PROGRAM:
The file created as a result of this query will be c:\TEMP\patients.csv
One should ensure that the c:\TEMP folder exists before running this query.
SELECT LName,FName,WkPhone,HmPhone,WirelessPhone
FROM patient WHERE PatStatus=0 INTO OUTFILE "c:\\TEMP\\patients.csv" FIELDS TERMINATED BY ',';
96. Find an Insurance check by Amount and Date
SELECT CarrierName,Sum(InsPayAmt) as $Patient, CheckAmt as $CheckAmt, CheckNum, CheckDate, Note, claimproc.patnum
FROM claimpayment
LEFT JOIN claimproc ON claimpayment.ClaimPaymentNum=claimproc.ClaimPaymentNum
WHERE CheckDate>='2008-03-01' AND
CheckAmt='241.00'
GROUP BY Patnum;
97. Get Patient info based on subscriber id (or part of it)
Add more fields from patient table as needed
SET @SubscriberID=('%123%');
SELECT p.PatNum, ip.SubscriberID FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN insplan ip ON ip.PlanNum=pp.PlanNum
WHERE ip.SubscriberID LIKE @SubscriberID
ORDER BY patient.LName, patient.FName;
98. Procedures with notes for date range (completed procs)
SET @FromDate= '2008-01-01', @ToDate='2008-01-31'; /*change dates here*/
SELECT pl.ProcDate,CONCAT(pa.LName,', ',pa.FName,' ',pa.MiddleI) AS PatName, ProcCode,
pl.ToothNum,Note
FROM patient pa,procedurecode pc, provider pr, procedurelog pl
LEFT JOIN procnote pn ON pl.ProcNum=pn.ProcNum
WHERE pl.ProcStatus = '2'
AND pl.CodeNum=pc.CodeNum
AND pa.PatNum=pl.PatNum
AND pr.ProvNum=pl.ProvNum
AND pl.ProcDate >=@FromDate
AND pl.ProcDate <=@ToDate
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate,PatName;
99. Find plans without assigned benefits
SELECT PlanNum, SubScriber FROM insplan WHERE AssignBen=0
100. Patients with saved Treatment Plans and date of plan
SET @FromDate= '2008-01-01', @ToDate='2008-01-31'; /*change dates here*/
SET @pos=0;
SELECT @pos:=@pos+1 AS Number, PatNum, DateTP, Heading, Note
FROM treatplan
WHERE DateTP Between @FromDate AND @ToDate;
101.Patient, provider, balance, like shown in patient payment window
DROP TABLE IF EXISTS tempfambal;
CREATE TABLE tempfambal(
FamBalNum int NOT NULL auto_increment,
PatNum int NOT NULL,
Guarantor int NOT NULL,
ProvNum int NOT NULL,
AmtBal double NOT NULL,
PRIMARY KEY (FamBalNum));
INSERT INTO tempfambal (PatNum,Guarantor,ProvNum,AmtBal)
SELECT patient.PatNum,patient.Guarantor,procedurelog.ProvNum,SUM(ProcFee)
FROM procedurelog,patient
WHERE patient.PatNum=procedurelog.PatNum
AND ProcStatus=2
GROUP BY patient.PatNum,ProvNum;
INSERT INTO tempfambal (PatNum,Guarantor,ProvNum,AmtBal)
SELECT patient.PatNum,patient.Guarantor,claimproc.ProvNum,-SUM(InsPayAmt)-SUM(Writeoff)
FROM claimproc,patient
WHERE patient.PatNum=claimproc.PatNum
AND (Status=1 OR Status=4 OR Status=5)/*received,supplemental,capclaim*/
GROUP BY patient.PatNum,ProvNum;
INSERT INTO tempfambal (PatNum,Guarantor,ProvNum,AmtBal)
SELECT patient.PatNum,patient.Guarantor,adjustment.ProvNum,SUM(AdjAmt)
FROM adjustment,patient
WHERE patient.PatNum=adjustment.PatNum
GROUP BY patient.PatNum,ProvNum;
INSERT INTO tempfambal (PatNum,Guarantor,ProvNum,AmtBal)
SELECT patient.PatNum,patient.Guarantor,paysplit.ProvNum,-SUM(SplitAmt)
FROM paysplit,patient
WHERE patient.PatNum=paysplit.PatNum
GROUP BY patient.PatNum,ProvNum;
/*payplan princ reduction*/
INSERT INTO tempfambal (PatNum,Guarantor,ProvNum,AmtBal)
SELECT patient.PatNum,patient.Guarantor,payplancharge.ProvNum,-SUM(Principal)
FROM payplancharge,patient
WHERE patient.PatNum=payplancharge.PatNum
GROUP BY patient.PatNum,ProvNum;
SELECT tempfambal.Guarantor,tempfambal.PatNum,tempfambal.ProvNum,SUM(AmtBal) AS $AmtBal
FROM tempfambal
INNER JOIN patient guarantor ON guarantor.PatNum=tempfambal.Guarantor
GROUP BY tempfambal.PatNum,tempfambal.ProvNum
/*Exclude Zero Balances, Optional, remove row to return all*/
HAVING (SUM(AmtBal)>0.009 OR SUM(AmtBal)<(-0.009))
ORDER BY guarantor.LName, guarantor.FName,tempfambal.ProvNum;
DROP TABLE IF EXISTS tempfambal;
102. CommLog notes by Type
SELECT commlog.PatNum, Note, ItemName FROM commlog
INNER JOIN definition ON commlog.CommType=definition.DefNum
INNER JOIN patient ON commlog.Patnum=patient.PatNum
WHERE ItemName Like 'Financial' OR ItemName Like 'Insurance'
ORDER BY patient.LName, patient.FName
103. New patients with Clinic and Referral source for date range,
Exludes patients who have never actually had a procedure performed
SET @pos=0, @FromDate='2008-01-01' , @ToDate='2008-01-31';
SELECT p.PatNum, DATE_FORMAT(p.DateFirstVisit,
'%m-%d-%Y') AS FirstVisit, c.Description,
r.LName as RefLName, r.FName as RefFName
FROM patient p
INNER JOIN procedurelog pl on pl.PatNum=p.PatNum
LEFT JOIN refattach ra ON p.PatNum=ra.PatNum
LEFT JOIN referral r ON r.ReferralNum=ra.ReferralNum AND ra.IsFrom='1'
LEFT JOIN clinic c ON p.ClinicNum=c.ClinicNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND p.PatStatus=0
AND pl.procdate BETWEEN @FromDate
AND @ToDate
AND pl.procstatus=2
AND pl.procfee > 0
GROUP BY p.PatNum
ORDER BY c.Description, p.LName;
104. Count of new patients by clinic
Exludes patients who have never actually had a procedure performed
SET @FromDate='2008-01-01' , @ToDate='2008-01-31';
SELECT c.Description AS 'Clinic', COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
INNER JOIN procedurelog pl on pl.PatNum=p.PatNum
LEFT JOIN clinic c ON p.ClinicNum=c.ClinicNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND p.PatStatus=0 AND pl.procdate BETWEEN @FromDate AND @ToDate
AND pl.procstatus=2 AND pl.procfee > 0
GROUP BY c.Description
UNION
(SELECT Concat('Total: ', @FromDate, ' to ', @ToDate) AS 'Clinic', (SELECT COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
INNER JOIN procedurelog pl on pl.PatNum=p.PatNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND p.PatStatus=0 AND pl.procdate BETWEEN @FromDate AND @ToDate
AND pl.procstatus=2 AND pl.procfee > 0));
105. Patients names, addresses & phone(s) seen today
SELECT CONCAT(pa.LName,', ',pa.FName,' ',pa.MiddleI) AS PatName,
CONCAT(pa.Address, pa.Address2, " ", pa.City, ", ", pa.State, " ", pa.zip) AS Address,
hmphone, wkphone, wirelessphone
FROM patient pa
LEFT JOIN procedurelog pl ON pa.PatNUm=pl.PatNum
WHERE pl.ProcStatus = '2'
AND DATE(pl.ProcDate)=DATE(curdate())
GROUP BY PatName
ORDER BY PatName
106. New Patient count, insured and not insured over date range
SET @FromDate='2008-01-01' , @ToDate='2008-01-31';
SELECT SUM(Z.INSUREDCOUNT) AS InsuredCountByDate, SUM(Z.NOTINSUREDCOUNT) AS NotInsuredCountByDate
FROM
(SELECT COUNT(DISTINCT patnum) AS INSUREDCOUNT, 0 AS NOTINSUREDCOUNT
FROM procedurelog
WHERE patnum IN (
SELECT A.patnum
FROM patient A, patplan B
WHERE A.patnum = B.patnum AND (A.datefirstvisit BETWEEN @FromDate AND @ToDate) AND B.Ordinal=1 )
UNION
SELECT 0 AS INSUREDCOUNT, COUNT(DISTINCT patnum) AS NOTINSUREDCOUNT
FROM procedurelog
WHERE patnum IN (
SELECT patnum
FROM patient
WHERE patnum NOT IN (SELECT patnum FROM patplan) AND datefirstvisit BETWEEN DATE('2008-01-01') AND DATE('2008-03-31') )
) Z;
107. Referred procedures by date range
SET @FromDate='2008-01-01' , @ToDate='2008-01-31';
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) As Patient,
pc.ProcCode as 'Code', abbrdesc as 'Description', DATE_FORMAT(pl.procdate,'%m-%d-%Y') AS 'Date',
ProcFee
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
WHERE pl.ProcStatus=5 AND
pl.ProcDate BETWEEN @FromDate AND @ToDate
ORDER BY pl.ProcDate, patient.LName, patient.FName ASC;
108. Patients with NO images
(nonindexed table, so will take a VERY long time to run, upward of one minute)
SELECT p.PatNum FROM patient p LEFT JOIN document a ON p.PatNum=a.PatNum
WHERE a.PatNum IS NULL;
109. Patients' image count(only returns patients with images, runs fast)
SELECT document.PatNum, CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) AS PatName,
COUNT(document.PatNum) AS 'Images'
FROM document
LEFT JOIN patient p ON document.PatNum=p.PatNum
GROUP BY document.PatNum
ORDER BY p.LName, p.FName;
110. Select procedures with date and description where the sum fee paid is greater than the fee
helpful for incorrect medicaid estimates
SET @StartDate='2008-01-01' , @EndDate='2008-06-15';
SELECT claim.PatNum,DateService,pc.ProcCode,ProcFee,f.Amount as 'UCR FEE', SUM(cp.inspayamt)
FROM claim
INNER JOIN claimproc cp ON claim.ClaimNum=cp.ClaimNum
INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN fee f ON pc.CodeNum=f.CodeNum
INNER JOIN definition d ON f.FeeSched=d.DefNum AND d.ItemName='Standard'
WHERE DateService>=@StartDate AND
DateService<=@EndDate AND (cp.Status=1 OR cp.Status=1)
GROUP BY cp.ProcNum
HAVING SUM(cp.InsPayAmt)>ProcFee;
111. Same query as 110, but adds condition: With patient balances less than 0 (account credits)
Select procedures with date and description where the sum fee paid is greater than the fee
helpful for incorrect medicaid estimates, uses last balance from updated aging
claimproc status is recieved or supplemental
SET @StartDate='2008-01-01' , @EndDate='2008-06-15';
SELECT claim.PatNum,DateService,pc.ProcCode,ProcFee,f.Amount as 'UCR FEE', SUM(cp.inspayamt), ga.BalTotal
FROM claim
INNER JOIN claimproc cp ON claim.ClaimNum=cp.ClaimNum
INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN patient pa ON pa.PatNum=pl.PatNum
INNER JOIN patient ga ON pa.Guarantor=ga.PatNum
INNER JOIN fee f ON pc.CodeNum=f.CodeNum
INNER JOIN definition d ON f.FeeSched=d.DefNum AND d.ItemName='Standard'
WHERE DateService>=@StartDate AND
DateService<=@EndDate AND ga.BalTotal<0 AND (cp.Status=1 OR cp.Status=4)
GROUP BY cp.ProcNum
HAVING SUM(cp.InsPayEst)-ProcFee>.01;
112. Returns procedures with estimated insurance greater than charged fee
claim proc status is not recieved AND the proc is complete AND the status of the claim is sent
SET @StartDate='2008-01-01' , @EndDate='2008-06-15';
SELECT claim.PatNum,DateService,pc.ProcCode,ProcFee, SUM(cp.inspayest)
FROM claim
INNER JOIN claimproc cp ON claim.ClaimNum=cp.ClaimNum
INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
WHERE DateService>=@StartDate AND
DateService<=@EndDate AND cp.Status=0 AND pl.ProcStatus=2 AND ClaimStatus='S'
GROUP BY cp.ProcNum
HAVING SUM(cp.InsPayEst)-ProcFee>.01
113. Outstanding insurance claims by carrier
SELECT cl.PatNum,cl.DateSent, ca.CarrierName, ca.Phone FROM claim cl
INNER JOIN patient p ON p.PatNum=cl.PatNum
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE cl.ClaimStatus='S'
ORDER BY ca.CarrierName,p.LName;
114. Outstanding insurance claims for a particular carrier
SET @Carrier='%Blue Cross%';
SELECT cl.PatNum,cl.DateSent, ca.CarrierName, ca.Phone FROM claim cl
INNER JOIN patient p ON p.PatNum=cl.PatNum
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE cl.ClaimStatus='S' AND ca.CarrierName LIKE @Carrier
ORDER BY ca.CarrierName,p.LName;
115. Patients(guarantors) with balances over $1 aged >90 and no payments in last 30 days
SELECT ps.PatNum AS 'Number',CONCAT(p.LName, ", ", p.FName) AS 'Name',p.Guarantor AS 'Guarantor', g.BalOver90 AS '$FamBalOver90',
DATE_FORMAT(MAX(ps.DatePay), '%m-%d-%Y') AS 'Date' , g.BalTotal AS '$FamBalTotal'
FROM patient g
INNER JOIN patient p ON p.Guarantor=g.PatNum
LEFT JOIN paysplit ps ON ps.PatNum=p.PatNum
WHERE p.BalOver90>1
GROUP BY ps.PatNum
HAVING MAX(ps.DatePay)<(CURDATE()-INTERVAL 1 MONTH)
ORDER BY p.Lname, p.FName;
116. Anticipated and incurred Writeoffs
USE with production report to get real production if you want to count writeoffs as the claim is made, not when they come in
BUT replace the number for in the production report for writeoffs with this total (when you print) or use the report below to just give total
(New report available in 5.7.0 gives these PPO numbers without this query)
SET @Start='2008-04-01', @End='2008-04-30';
SELECT PatNum,ProvNum,PlanNum,WriteOff AS $Amt,DATE_FORMAT(ProcDate,'%m/%d/%Y') AS Proc_Date,DATE_FORMAT(DateEntry,'%m/%d/%Y') AS Entry_Date
FROM claimproc
WHERE (WriteOff >0)
AND (ProcDate BETWEEN @Start AND @END)
AND (Status=0 OR Status = 1 OR Status = 4) /*includes sent, recieved and supplemental claims*/
ORDER BY ProcDate;
117. Total Anticipated and incurred writeoffs for procedures completed in range
SET @Start='2008-04-01', @End='2008-04-30';
SELECT SUM(WriteOff) AS '$TotalWriteoffs'
FROM claimproc
WHERE WriteOff >0
AND (ProcDate BETWEEN @Start AND @END)
AND (Status=0 OR Status = 1 OR Status = 4) /*includes sent, recieved and supplemental claims*/
ORDER BY ProcDate
118. Mailing list of guarantors of patients with a particular insplan, unlike 21, returns the guarantors of all
patients with plan, not the subscriber, also if you drop the plan it goes off the list
SELECT DISTINCTROW gu.salutation, gu.LName, gu.FName, gu.Address,
gu.Address2, gu.City, gu.zip, gu.Birthdate, ip.DateEffective, ca.CarrierName
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN insplan ip ON ip.PlanNum=pp.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN patient gu ON p.Guarantor=gu.PatNum
WHERE ca.CarrierName LIKE '%PLAN%'
ORDER BY ca.CarrierName, gu.LName;