Skip to content

Instantly share code, notes, and snippets.

@markwhat1

markwhat1/query_examples.sql

Last active Mar 24, 2022
Star
Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
Open Dental Query Examples
Raw
query_examples.sql
This file has been truncated, but you can view the full file.
Home Welcome to the Open Dental Query Examples Page
These query examples were written for other practices, usually for a specific purpose. There may be additional changes needed to return the results you want.
Queries are sorted chronologically, with queries written for older versions listed first and queries written for newer versions listed last.
We recommend looking at higher numbered queries first, because older queries may not work in new versions.
If you find a query you like, copy/paste the query into Open Dental to run it. See User Queries
If needed, change any required variables before running. Look at the query comments for variable descriptions.
If you need help finding a query, modifying a query, or would like a custom query, fill out and submit a Query Request Form
Limit query results by category. Up to 3 categories can be selected. Results will only include queries that match ALL selected categories.
Return Queries About:
No Category Limit
AND
No Category Limit
AND
No Category Limit
Result Count: 1359
QueryID Query, with Title and Notes
All claims that are currently open that were sent on a given day - change DateSent value as needed
/*1*/ SELECT * FROM claim
WHERE DateSent ='2005-04-19'
AND ClaimStatus='S'
All treatment planned procedures, ordered by patient - See also #50 & #56
/*2 All treatment planned procedures, ordered by patient*/
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 02/10/2022:MattG*/
SELECT
CONCAT( -- Build a name string like OD's default
UPPER(SUBSTRING(p.LName, 1, 1))
,SUBSTRING(p.LName, 2, LENGTH(p.LName)-1)
,','
,IF( -- Display preferred if we have one
LENGTH(p.Preferred) > 0
,CONCAT(
' \''
,UPPER(SUBSTRING(p.Preferred, 1, 1))
,SUBSTRING(p.Preferred, 2, LENGTH(p.Preferred)-1)
,'\' '
)
,' '
)
,UPPER(SUBSTRING(p.FName, 1, 1))
,SUBSTRING(p.FName, 2, LENGTH(p.FName)-1)
,IF( -- Display middle initial if we have one
LENGTH(p.MiddleI) > 0
,CONCAT(
' '
,UPPER(SUBSTRING(p.MiddleI, 1, 1))
,SUBSTRING(p.MiddleI, 2, LENGTH(p.MiddleI)-1)
)
,''
)
) AS 'Patient Name'
,p.PatNum AS 'Patient ID'
,pc.ProcCode
,pl.ProcFee
,pl.Surf
,pl.ToothNum
FROM procedurelog pl
INNER JOIN procedurecode pc
ON pl.CodeNum=pc.CodeNum
INNER JOIN patient p
ON p.PatNum = pl.PatNum
WHERE pl.ProcStatus=1 -- Treatment Planned
ORDER BY pl.PatNum
Birthday postcards for a one week range - In the example, from 10/06 to 10/13
/*3*/ 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 and date entry. - In the example, for 7/29/2005
/*4 Daily patient payments organized by chart number and date entry.*/
/*Query code written/modified 10/21/2016*/
SELECT payment.PayDate,
payment.DateEntry,
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
AND payment.PayAmt > 0
AND payment.DateEntry =CURDATE() /* Replace =CURDATE() with ='2016-12-31' including apostrophes */
ORDER BY payment.DateEntry, patient.ChartNumber
Daily Insurance payments organized by chart number - In the example, for 7/29/2005
/*5*/ 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 - The last payment date is only for payments entered on the guarantor of the family.
/*6*/ 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 -
/*7*/ 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 -
/*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*(pl.UnitQty+pl.BaseUnits)-(CASE WHEN ISNULL(claimproc.WriteOff) THEN 0 ELSE (SUM(claimproc.WriteOff)) END) AS $fee
FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
INNER JOIN provider pr ON pr.ProvNum=pl.ProvNum
LEFT JOIN claimproc ON pl.ProcNum=claimproc.ProcNum
AND claimproc.Status='7'
WHERE pl.ProcStatus = '2'
AND pl.ProcDate >=@FromDate
AND pl.ProcDate <=@ToDate
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate,PatName
The account balances for all patients with an appointment on a specific day -
/*9 Estimated balances for patients with an appointment on a specific day*/
/*Query code written/modified: 04/04/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @AptDateTime = '2018-04-04';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SET @AsOf=CURDATE();
SET @PayPlanVersion=IFNULL((SELECT ValueString FROM preference WHERE prefName LIKE 'PayPlansVersion'),1);
SELECT appointment.AptDateTime,
patient.LName,
patient.FName,
FORMAT(IFNULL(aging.PatBal,0),2) AS PatEstBalance
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
LEFT JOIN (
SELECT
D.PatNum,
ROUND(D.PatBal,2) AS PatBal
FROM (
/*Get the family level charges, credits, and ins estimates*/
SELECT p.PatNum,
p.LName,
p.FName,
SUM(B.PatBal) AS PatBal
FROM (
/*Get Patient level charges and credits*/
SELECT RawPatTrans.PatNum,
SUM(RawPatTrans.TranAmount) AS PatBal
FROM (
/*Get the completed procedure dates and charges for the entire office history*/
SELECT 'Fee' AS TranType,pl.PatNum AS PatNum,pl.ProcDate AS TranDate,
pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2
UNION ALL
/*Paysplits for the entire office history*/
SELECT 'Pay' AS TranType,ps.PatNum AS PatNum,ps.DatePay AS TranDate,
(CASE WHEN @PayPlanVersion IN (1,3) THEN (CASE WHEN ps.PayPlanNum=0 THEN -ps.SplitAmt ELSE 0 END) ELSE -ps.SplitAmt END) AS TranAmount
FROM paysplit ps
WHERE ps.SplitAmt!=0
UNION ALL
/*Get the adjustment dates and amounts for the entire office history*/
SELECT 'Adj' AS TranType,a.PatNum AS PatNum,a.AdjDate AS TranDate,
a.AdjAmt AS TranAmount
FROM adjustment a
WHERE a.AdjAmt!=0
UNION ALL
/*Claim payments for the entire office history*/
SELECT 'InsPay' AS TranType,cp.PatNum AS PatNum,cp.DateCp AS TranDate,
(CASE WHEN cp.payplannum = 0 THEN -cp.InsPayAmt ELSE 0 END)-cp.Writeoff AS TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7)/*received,supplemental,CapClaim, or CapComplete*/
UNION ALL
/*Version 1: Payment plan principal for the entire office history for those using the original payplans accounting*/
SELECT 'PayPlan1' AS TranType,pp.PatNum AS PatNum,pp.PayPlanDate AS TranDate,
-pp.CompletedAmt AS TranAmount
FROM payplan pp
WHERE @PayPlanVersion = 1
AND pp.CompletedAmt!=0
#GROUP BY pp.PayPlanNum
UNION ALL
/*Version 2: Payment plan charges for those using line-item account for payplans*/
SELECT 'PayPlan2' AS TranType, ppc.PatNum AS PatNum,
ppc.ChargeDate TranDate,
(CASE WHEN ppc.ChargeType != 0 THEN -ppc.Principal #if credit, subtract the principal
WHEN pplan.PlanNum=0 THEN ppc.Principal+ppc.Interest ELSE 0 END) #if debit and a patient plan, add principal and interest
AS TranAmount
FROM payplancharge ppc
LEFT JOIN payplan pplan ON pplan.PayPlanNum=ppc.PayPlanNum
WHERE @PayPlanVersion = 2
AND ChargeDate <= @AsOf
UNION ALL
/*Version 3: Age credits only*/
SELECT 'PayPlan3' TranType, ppc.PatNum AS PatNum,
ppc.ChargeDate TranDate,
-ppc.Principal TranAmount
FROM payplancharge ppc
LEFT JOIN payplan pp ON pp.PayPlanNum=ppc.PayPlanNum
WHERE ppc.ChargeDate <= @AsOf
AND ppc.ChargeType = 1 /*Credit*/
AND @PayPlanVersion = 3
) RawPatTrans
WHERE TranDate<=@AsOf
GROUP BY RawPatTrans.PatNum
) B
LEFT JOIN (
/*Get patient level InsPayEst and Writeoffs from unreceived claims.*/
SELECT cp.PatNum,SUM(cp.InsPayEst) AS InsEst, SUM(cp.Writeoff) AS Writeoff
FROM claimproc cp
WHERE cp.PatNum!=0
AND ((cp.Status=0 AND cp.ProcDate<=@AsOf) OR (cp.Status=1 AND cp.DateCP>@AsOf))
AND cp.ProcDate<=@ASOf
GROUP BY cp.PatNum
) C ON C.PatNum=B.PatNum
INNER JOIN patient p ON p.PatNum=B.PatNum
GROUP BY p.PatNum
)D
)aging ON aging.PatNum = patient.PatNum
WHERE appointment.AptDateTime LIKE CONCAT(@AptDateTime,'%')
AND AptStatus != 6 /*Planned*/
AND AptStatus != 3 /*UnschedList*/
All lab cases with a status of received, and appointment not complete -
/*10*/ 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
OBSOLETE Production by fee schedule for date range - OBSOLETE 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 incl
/*11 OBSOLETE, if you need a query like this one, you must request it from us or compose it */ 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.
/*12*/ SELECT ProcFee,ProcCode,PatNum
FROM procedurelog pl
LEFT JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE ProcStatus=1
ORDER BY ProcFee DESC;
Public Health Raw Population Data. - SEE REPLICATION WARNING for versions before 14.3.1. This query will not work on versions 7.8.1 or higher of Open Dental as the school table was removed.
/*13 Public Health Raw Population Data. This query will not work on versions 7.8.1 or higher of Open Dental as the school table was removed.*/
/*WARNING On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
DROP TABLE IF EXISTS tempbroken;
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 IF EXISTS tempbroken;
Daily Procedures: Grouped by Procedure Code -
/*14 Daily Procedures: Grouped by Procedure Code. Like Internal*/
SET @FromDate = '2020-04-01', @ToDate = '2020-04-20'; -- Enter dates between '' in YYYY-MM-DD format
/*---DO NOT MODIFY BELOW THIS LINE---*/
/*Query code written/modified: 04/20/2020:SalinaK*/
SELECT
def.ItemName AS 'Category',
pc.ProcCode AS 'Code',
pc.Descript AS 'Description',
COUNT(*) AS 'Quantity',
FORMAT(AVG(pl.ProcFee * (pl.UnitQty + pl.BaseUnits)),2) AS '$AvgFee_',
FORMAT(SUM(pl.ProcFee * (pl.UnitQty + pl.BaseUnits)),2) AS '$TotFee_'
FROM procedurelog pl
INNER JOIN procedurecode pc
ON pl.CodeNum = pc.CodeNum
INNER JOIN definition def
ON def.DefNum = pc.ProcCat
WHERE pl.ProcStatus = 2 -- Completed procedures
AND pl.ProcDate BETWEEN @FromDate AND @ToDate -- Date limitation
GROUP BY pc.ProcCode
ORDER BY def.ItemOrder, pc.ProcCode;
A list of all referrals you have received for a user specific date range - For Versions 17.1 and greater. Please update your version accordingly. Shows how many patients referred by each source in the date range.
/*15 A list of all referrals you have received for a user specific date range.
For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 02/22/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2018-01-01', @ToDate='2018-01-31';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SELECT referral.LName,
referral.FName,
COUNT(*)
FROM referral
INNER JOIN refattach ON referral.ReferralNum=refattach.ReferralNum
AND refattach.RefType = 1 /*Is From referrals*/
AND refattach.RefDate BETWEEN @FromDate AND @ToDate
GROUP BY referral.ReferralNum
For public health clinics, the production by gradeschool. - For versions greater than 7.3, replace GradeSchool with SiteNum to get the query to work.
/*16*/ 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
For public health clinics, the number of patients seen at each grade school for a date range -
/*17*/ 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
Count of patients, grouped by billing type - Includes inactive and archived patients
/*18*/ SELECT BillingType,COUNT(*)FROM patient
WHERE PatStatus != 4
GROUP BY BillingType
List of new patients for a given date range - Does not confirm patient was actually seen
/*19*/ SELECT * FROM patient
WHERE DateFirstVisit >= '2005-10-01'
AND DateFirstVisit < '2005-11-01'
AND patient.patstatus = '0'
Referral sources with how many unique patients referred and gross production for the specified date range - Similar to internal Referral Analysis Report. For Versions 17.1 and greater. Please update your version accordingly.
/*20 List of referral sources, how many unique patients referred, and how much production from each source*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 02/22/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2018-01-01', @ToDate='2018-01-31';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SELECT r.LName,
r.FName,
COUNT(DISTINCT ra.PatNum) AS HowMany,
FORMAT(SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)),2) AS $HowMuch
FROM referral r
INNER JOIN refattach ra ON r.ReferralNum=ra.ReferralNum
AND ra.RefType = 1 /*Is From referrals*/
INNER JOIN procedurelog pl ON pl.PatNum=ra.PatNum
AND pl.ProcStatus = 2 /*Completed*/
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY r.ReferralNum
ORDER BY HowMany DESC
Number of patients seen for a given date range (two years in the example). - Patients will only be counted once for the date range, not once for every visit. This is a good way to measure active patients.
/*22*/ 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.
/*23*/ 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. -
/*24*/ 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.
/*25*/ 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 subscribers who have a particular carrier. In the example, it's for Delta, but you can substitute your own search string between the %'s. - Will show dropped plans. Returns subscribers, DOES NOT GIVE patients who have the plan, see #118
/*26*/ SELECT c.CarrierName,p.*
FROM patient p
INNER JOIN inssub iss ON iss.Subscriber=p.PatNum
INNER JOIN insplan ip ON ip.PlanNum=iss.PlanNum
INNER JOIN carrier c ON ip.CarrierNum=c.CarrierNum
WHERE c.CarrierName LIKE '%Delta%'
ORDER BY c.CarrierName
A list of referrals during a specific date range. - For Versions 17.1 and greater. Please update your version accordingly. Best for exporting to a text file for use in a letter merge.
/*27 A list of referrals during a specific date range.*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 04/03/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2016-04-19', @ToDate='2016-04-21';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
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
INNER JOIN refattach ON patient.PatNum=refattach.PatNum
AND refattach.RefType = 1 /*RefFrom*/
AND refattach.RefDate BETWEEN @FromDate AND @ToDate
INNER JOIN referral ON referral.ReferralNum=refattach.ReferralNum
AND referral.NotPerson = '0' /*False*/
ORDER BY referral.Specialty, referral.LName, referral.FName
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.
/*28*/ 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 inssub ON patplan.InsSubNum=inssub.InsSubNum
LEFT JOIN insplan ON inssub.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 - For instance, cash, check, or credit card.
/*29*/ 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 -
/*30*/ SELECT PayDate,PayType,SUM(PayAmt)
FROM payment
WHERE PayDate = '2006-02-23'
GROUP BY PayType
ORDER BY PayAmt
A list of all outstanding preauthorizations -
/*31*/ SELECT *
FROM claim
WHERE ClaimType = 'PreAuth'
AND ClaimStatus != 'R'
ORDER BY DateService
List of writeoffs for a daterange and for one provider - Leave out the line containing ProvNum to get for all providers
/*32*/ 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
Quarterly Production by billing type. - Does not include adjustments.
/*33*/ 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
List of families seen in the last three years - Useful for generating a list of patients for Christmas cards.
/*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 because it is the guarantors name, not the patient's name. */
/* This query gives names and addresses of guarantors for each family where at least one active patient has been seen (with procedures completed) within the past three years, and where the guarantor address has a valid zip code. Only visits after today's date three years ago are included. */
SET @FromDate = CURDATE() - INTERVAL 3 YEAR;
SET @ToDate = CURDATE();
SELECT g.LName,g.FName, g.Address, g.Address2, g.City, g.State, g.Zip
FROM patient p
INNER JOIN patient g ON p.Guarantor=g.PatNum
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE p.PatStatus=0
/*only patients with procedures completed within the last three years.*/
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2
AND Length(g.Zip)>4
GROUP BY g.PatNum
ORDER BY g.LName,g.FName
A list of patients seen between two dates (based on procedures completed in date range). -
/*36 A list of patients seen between two dates (based on procedures completed in date range).*/
SET @FromDate='2017-01-01',@ToDate='2017-12-31';
SET @pos=0;
SELECT @pos:=@pos+1 AS '#Pats', A.*
FROM(
SELECT p.LName, p.FName, p.Address, p.Address2, p.City, p.State, p.Zip
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
AND pl.ProcStatus=2
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
WHERE p.PatStatus=0
GROUP BY p.PatNum
ORDER BY p.LName,p.FName
) A;
A list of all guarantors of patients 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.
/*37 A list of all guarantors of patients 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. */
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 5/6/2009, 11/23/2021:MatG*/
SELECT
g.PatNum
,g.LName
,g.FName
,g.Address
,g.Address2
,g.City
,g.State
,g.Zip
FROM patient p
INNER JOIN patient g
ON p.Guarantor = g.PatNum
WHERE p.PatStatus=0 -- Patient
GROUP BY g.PatNum
ORDER BY g.LName, g.FName;
List and count of patients that were seen by you in a date range and their referrals - For Versions 17.1 and greater. Please update your version accordingly.
/*38 List and count of patients that were seen by you in a date range and their referrals*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 04/03/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate = '2018-01-01', @ToDate = '2018-01-05';
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Row#',
A.*
FROM (
SELECT patient.PatNum,
patient.lname,
patient.fname,
RefDate DateReferred,
(CASE WHEN RefType = 0 THEN 'To'
WHEN RefType = 1 THEN 'From'
ELSE 'RefCustom'
END) AS ReferredType,
refattach.referralnum
FROM patient
INNER JOIN procedurelog ON procedurelog.PatNum = patient.PatNum
AND procedurelog.ProcDate BETWEEN @FromDate AND @ToDate
INNER JOIN refattach ON refattach.PatNum = patient.PatNum
INNER JOIN referral r ON refattach.ReferralNum = r.ReferralNum
AND patient.patstatus = '0' /*Active*/
GROUP BY patient.PatNum, r.ReferralNum, refattach.RefAttachNum
ORDER BY ReferredType, r.LName, r.FName, patient.LName, patient.FName
)A
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
/*39 List of patients and their addresses who have not been seen since a certain date */
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) < '2011-09-01'
List of subscribers for a given carrier and groupnum. - Note: This is a list of subscribers, not patients. See 118 also.
/*41 List of subscribers for a given carrier and groupnum. Note: This is a list of subscribers, not patients. See 118 also.*/
SET @CarrierName = '%Blue%',
@GroupNum = '%%';
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 09/21/2021:MattG*/
SELECT
c.CarrierName
,p.PatNum
FROM patient p
INNER JOIN inssub
ON inssub.Subscriber=p.PatNum
INNER JOIN insplan ip
ON ip.PlanNum=inssub.PlanNum
INNER JOIN carrier c
ON ip.CarrierNum=c.CarrierNum
WHERE c.CarrierName LIKE @CarrierName
AND ip.GroupNum LIKE @GroupNum
ORDER BY c.CarrierName, ip.GroupNum,p.LName;
Sums of Aging report without having to print the entire report. - If you do not update aging first, results will not be current
/*42*/ 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')
Appointment history for one patient - Change the 581 to the appropriate PatNum before running
/*43 Appointment history for one patient. Change the 581 to the appropriate PatNum before running.*/
SET @PatNum = '581'; -- Set patnum between quotes
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 5/6/2009, 02/24/2022:MattG*/
SELECT
a.patnum
,a.AptStatus
,DATE_FORMAT(a.AptDateTime, '%m-%d-%Y') AS 'Date'
,DATE_FORMAT(a.AptDateTime, '%l:%i %p') AS 'Time'
,CHAR_LENGTH(a.Pattern)*5 AS 'Min'
,ProcDescript AS 'Procedures'
,Note AS 'Notes'
FROM appointment a
WHERE a.PatNum = @PatNum
Mailing information for guarantors of active patients. -
/*46. Mailing information for guarantors of active patients*/
SELECT g.LName, g.FName, g.Address, g.Address2, g.City, g.State, g.Zip FROM patient p
INNER JOIN patient g ON g.PatNum=p.Guarantor
WHERE p.PatStatus=0 AND LENGTH(g.Zip)>4
GROUP BY g.PatNum
ORDER BY g.LName, g.FName;
Daily payments summarized by type -
/*47*/ 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)
New patients for time span who were actually seen - Excludes ones who made an appointment, but then no-showed.
/*48 New patients for time span who were actually seen*/
/*Query code written/modified: 10/21/2015*/
SET @FromDate='2015-01-01', @ToDate='2015-01-30';
SET @pos=0;
SELECT @pos:=@pos+1 AS PatCount, A.*
FROM(
SELECT 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
)A;
Patient contact info for patients not seen since a certain date - Excludes broken/missed codes. Submitted by Jorge Bonilla, DMD.
/*49 Patient contact info for patients not seen since a certain date. Excludes broken/missed codes.*/
SET @Date='2017-12-31';
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 02/15/2018, 10/15/2021:MattG*/
#
SET @pos=0;
SELECT
@pos:=@pos+1 AS 'NumberOfPatients'
,a.LName
,a.FName
,a.Address
,a.Address2
,a.City
,a.State
,a.Zip
,a.HmPhone
,a.WirelessPhone
,a.WkPhone
,a.PatStatus
,a.AddrNote
,a.LastVisit
FROM (
SELECT p.LName,
p.FName,
p.Address,
p.Address2,
p.City,
p.State,
p.Zip,
p.HmPhone,
p.WirelessPhone,
p.WkPhone,
p.PatStatus,
p.AddrNote,
MAX(pl.ProcDate) AS LastVisit
FROM patient p
INNER JOIN procedurelog pl
ON pl.PatNum=p.PatNum
AND pl.ProcStatus = 2 /* Complete */
INNER JOIN procedurecode pc
ON pc.CodeNum = pl.CodeNum
AND pc.ProcCode NOT IN ('D9986','D9987') -- Broken/missed codes
WHERE p.PatStatus != 4 /* No deleted patients */
GROUP BY pl.PatNum
HAVING MAX(pl.ProcDate) < @Date
ORDER BY p.address, p.address2
)a
Treatment planned procedures, that are not in a scheduled apt -
/*50 Treatment planned procedures, that are not in a scheduled apt */
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) AS Patient,
pc.ProcCode AS 'Code',
abbrdesc AS 'Description',
ToothNum,
DATE_FORMAT(pl.ProcDate,'%m-%d-%Y') AS 'Date',
ap.AptStatus,
ProcFee
FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
AND ProcStatus=1
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) /*No apt, unscheduled, planned*/
AND PatStatus=0 /*patient status*/
ORDER BY aptstatus, patient.LName, patient.FName ASC;
Answers the question: during a given period, what is the production generated by different referral sources - For Versions 17.1 and greater. Please update your version accordingly. Includes net production for each source, limited to the date range.
/*51 Answers the question: during a given period, what is the production generated by different referral sources.*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Includes net production for each source, limited to the date range.*/
/*Query code written/modified: 04/03/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2018-01-01', @ToDate='2018-01-05';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
(
SELECT a.PatientName, a.FirstVisit, a.Referror,
FORMAT(a.Fees, 2) AS Fees,
FORMAT(a.Adjustments, 2) AS Adjustments,
FORMAT(a.WriteOffs, 2) AS WriteOffs,
FORMAT((a.Fees+a.Adjustments-a.Writeoffs), 2) AS NetProduction
FROM(
SELECT CONCAT(p.LName,', ',p.FName) AS PatientName,
p.DateFirstVisit AS FirstVisit,
CONCAT(r.LName,', ',r.FName) AS Referror,
SUM(CASE WHEN RawPatTrans.TranType='Fee' THEN RawPatTrans.TranAmount ELSE 0 END) AS Fees,
SUM(CASE WHEN RawPatTrans.TranType='Adj' THEN RawPatTrans.TranAmount ELSE 0 END) AS Adjustments,
SUM(CASE WHEN RawPatTrans.TranType='Writeoff' THEN RawPatTrans.TranAmount ELSE 0 END) AS WriteOffs
FROM
/*transaction table*/
(
/*Get the completed procedure dates and charges for the entire office history*/
SELECT 'Fee' AS TranType, pl.PatNum PatNum,pl.ProcDate TranDate,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
UNION ALL
/*Get the adjustment dates and amounts for the entire office history*/
SELECT 'Adj' AS TranType, a.PatNum PatNum,a.AdjDate TranDate,a.AdjAmt TranAmount
FROM adjustment a
WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
UNION ALL
/*Claim writeoffs for the entire office history*/
SELECT 'Writeoff' AS TranType,cp.PatNum PatNum,cp.DateCp TranDate,cp.Writeoff TranAmount
FROM claimproc cp
WHERE cp.Status IN (0,1,4) /*Not Received yet, Received or Supplemental*/
AND cp.ProcDate BETWEEN @FromDate AND @ToDate
) AS RawPatTrans
INNER JOIN patient p ON p.PatNum=RawPatTrans.PatNum
INNER JOIN refattach ra ON p.PatNum=ra.PatNum
AND ra.RefType=1 /*RefFrom*/
AND ra.ItemOrder=(
SELECT MIN(ra.ItemOrder)
FROM refattach ra
WHERE ra.RefType=1 /*RefFrom*/
AND ra.PatNum=p.PatNum
GROUP BY p.PatNum
)
INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
GROUP BY p.PatNum
) a
WHERE (a.Fees!=0 OR a.Adjustments!=0 OR a.Writeoffs!=0)
)
UNION ALL
(
SELECT CONCAT(' Totals: ',/*< AltKey 255 blank character in string to order last*/SUM(a.PatientName),' Patients') AS PatientName,
a.FirstVisit,
CONCAT(COUNT(DISTINCT a.Referror),' Referrors') AS 'Referror',
FORMAT(SUM(a.Fees), 2) AS 'Fees',
FORMAT(SUM(a.Adjustments), 2) AS 'Adjustments',
FORMAT(SUM(a.WriteOffs), 2) AS 'WriteOffs',
FORMAT((SUM(a.Fees)+SUM(a.Adjustments)-SUM(a.Writeoffs)), 2) AS NetProduction
FROM(
SELECT COUNT(DISTINCT p.PatNum) AS PatientName,
CONCAT(DATEDIFF(@ToDate,@FromDate)+1,' Days') AS FirstVisit,
r.ReferralNum AS Referror,
SUM(CASE WHEN RawPatTrans.TranType='Fee' THEN RawPatTrans.TranAmount ELSE 0 END) AS Fees,
SUM(CASE WHEN RawPatTrans.TranType='Adj' THEN RawPatTrans.TranAmount ELSE 0 END) AS Adjustments,
SUM(CASE WHEN RawPatTrans.TranType='Writeoff' THEN RawPatTrans.TranAmount ELSE 0 END) AS WriteOffs
FROM
/*transaction table*/
(
/*Get the completed procedure dates and charges for the entire office history*/
SELECT 'Fee' AS TranType, pl.PatNum PatNum,pl.ProcDate TranDate,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
UNION ALL
/*Get the adjustment dates and amounts for the entire office history*/
SELECT 'Adj' AS TranType, a.PatNum PatNum,a.AdjDate TranDate,a.AdjAmt TranAmount
FROM adjustment a
WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
UNION ALL
/*Claim writeoffs for the entire office history*/
SELECT 'Writeoff' AS TranType,cp.PatNum PatNum,cp.DateCp TranDate,cp.Writeoff TranAmount
FROM claimproc cp
WHERE cp.Status IN (0,1,4) /*Not Received yet, Received or Supplemental*/
AND cp.ProcDate BETWEEN @FromDate AND @ToDate
) AS RawPatTrans
INNER JOIN patient p ON p.PatNum=RawPatTrans.PatNum
INNER JOIN refattach ra ON p.PatNum=ra.PatNum
AND ra.RefType=1 /*RefFrom*/
AND ra.ItemOrder=(
SELECT MIN(ra.ItemOrder)
FROM refattach ra
WHERE ra.RefType=1 /*RefFrom*/
AND ra.PatNum=p.PatNum
GROUP BY p.PatNum
)
INNER JOIN referral r ON r.ReferralNum=ra.ReferralNum
GROUP BY p.PatNum
) a
WHERE (a.Fees!=0 OR a.Adjustments!=0 OR a.Writeoffs!=0)
)
ORDER BY PatientName;
Return list of statements sent by mail in last 30 days, or other interval - Change value in interval statement to a number other than -30 for a different interval, the 30 interval is for the due date and should not be changed, assumes monthly billing
/*52*/
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count',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";
Production for date range with pay splits and tooth and surface -
/*53 Production for date range with pay splits and tooth and surface*/
SET @FromDate='2016-01-01',@ToDate='2016-01-31';
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
AND pl.ProcStatus=2 #Complete
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN paysplit ps ON pl.ProcNum=ps.ProcNum
ORDER BY patient.LName, patient.FName ASC;
Patient payments by procedure for date range - Patient Payments by procedure only works if you split the payments by procedure when entering payments, uncommon but there are several offices that do this, especially if paying providers by income
/*54 Patient 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
AND pl.ProcStatus != 6
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;
All insurance claimed procedures with UCR fee for date range, even if a different fee was used or sent , for a specified fee schedule. - Useful for comparisons, you can also add the column pl.ProcFee to see what was actually charged.
/*55 All insurance claimed procedures with UCR fee for date range, even if a different fee was used or sent*/
SET @FromDate='2015-01-01' , @ToDate='2015-01-15';
SET @FeeSchedName='Standard';
SELECT c.PatNum, c.DateService, c.ProvTreat, pc.ProcCode, cp.InsPayEst, f.Amount AS '$UCR FEE'
FROM claim c
INNER JOIN claimproc cp ON c.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 feesched fs ON f.FeeSched=fs.FeeSchedNum
AND fs.Description LIKE @FeeSchedName
WHERE DateService >= @FromDate
AND DateService <=@ToDate
ORDER BY c.DateService,c.PatNum,pc.ProcCode
Returns all treatment planned procedures (summed by patient) for active patients without a scheduled OR planned apt, with phone nums - usefull for those transitioning to planned appointments
/*56*/ 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;
Insurance payments received after a certain date for the specified carrier, sorted by carrier - Chart Number used, can use patnum if needed.
/*57 Insurance payments received after a certain date for the specified carrier, sorted by carrier. Chart Number used, can use patnum if needed.*/
SET @CheckDate = '2021-04-01';
SET @CarrierName = '%%'; -- Enter a part of, or entire carrier name. Leave just %'s to run for all carriers
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 05/05/2021:Jeffery, 05/06/2021:MattG*/
SELECT
patient.ChartNumber
,CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS 'Name'
,claimpayment.CheckDate
,claimproc.ProcDate AS 'Date of Service'
,carrier.CarrierName
,claimpayment.CheckNum
,claimproc.ClaimNum
,SUM(claimproc.FeeBilled) AS '$Billed'
,SUM(claimproc.InsPayAmt ) AS '$Amt'
FROM claimpayment
INNER JOIN claimproc
ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum
AND (claimproc.Status = '1' OR claimproc.Status = '4')
AND DATE(claimpayment.CheckDate) > @CheckDate
INNER JOIN insplan
ON claimproc.PlanNum = insplan.PlanNum
INNER JOIN patient
ON claimproc.PatNum = patient.PatNum
INNER JOIN carrier
ON carrier.CarrierNum = insplan.CarrierNum
AND carrier.CarrierName LIKE @CarrierName
GROUP BY claimproc.ClaimNum
ORDER BY CarrierName;
Received Lab Cases with sent and received date with patient number -
/*58*/ SELECT PatNum AS PatID,PatNum,Description AS Laboratory,DateTimeSent,DateTimeRecd
FROM labcase lc
INNER JOIN laboratory l ON lc.LaboratoryNum=l.LaboratoryNum
WHERE DateTimeRecd>DateTimeSent;
Patient lifetime revenue and TP'd procedure totals - also includes PatNum and patient age, does NOT include insurance revenue
/*59*/ SELECT PatNum, (CASE WHEN (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5))<120
THEN (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) ELSE 'NONE' END)
AS 'Age', (SELECT SUM(SplitAmt)
FROM paysplit WHERE paysplit.PatNum=patient.PatNum) AS '$Lifetime Income',
(SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=1 AND
procedurelog.PatNum=patient.PatNum) AS '$Treatment Planned' FROM
patient ORDER BY LName;
Treatment Planned Procedures with Referror and Insurance -
/*60*/ 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;
Query scheduled procedures for code with date range -
/*61 Procedures in a scheduled or ASAP appointment in the date range. Option to filter by specified code(s)*/
/*Query code written/modified 04/11/2018*/
SET @Codes =''; /*Enter exact procedure codes here, separated by a | symbol. Must not end in a | . Will search all if left blank.*/
SET @StartDate='2018-04-01' , @EndDate='2018-04-30'; /*Enter dates here in YYYY-MM-DD format*/
/*---DO NOT MODIFY BELOW THIS LINE---*/
SET @Codes=(CASE WHEN @Codes='' THEN '^' ELSE CONCAT('^',REPLACE(@Codes,'|','$|^'),'$') END);
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) AS Patient,
GROUP_CONCAT(pc.ProcCode) AS 'Codes',
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 IN (1,4) /*Scheduled or ASAP*/
AND ProcCode REGEXP @Codes
AND DATE(ap.AptDateTime) BETWEEN @StartDate AND @EndDate
GROUP BY patient.PatNum, ap.AptNum
ORDER BY aptstatus, patient.LName, patient.FName ASC;
Patients payments by type with patient name (PatNum) for a day - Was useful for a few versions from about 5.4 to 5.6 where insurance checks were not being returned by patient name, this report is included now in standard reports, (daily payments with insurance checks by patient option selected)
/*62*/ SET @StartDate='2010-01-01', @EndDate='2010-12-31';
SELECT paysplit.PatNum,definition.ItemName AS PaymentType, paysplit.DatePay AS 'DatePay',
SUM(paysplit.SplitAmt) AS $PaymentAmt
FROM payment,definition,paysplit
WHERE paysplit.DatePay BETWEEN @StartDate AND @EndDate
AND payment.PayNum=paysplit.PayNum
AND definition.DefNum=payment.PayType
GROUP BY payment.PayType,paysplit.PatNum, paysplit.DatePay
UNION
SELECT PatNum, 'Ins Checks',claimproc.DateCP AS 'DatePay',SUM(claimproc.InsPayAmt) AS $PaymentAmt
FROM claimproc
WHERE claimproc.DateCP BETWEEN @StartDate AND @EndDate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY PatNum, DatePay
ORDER BY DatePay, PaymentType;
Number of active patients of each age - Doesnot imply that a procedure has been completed on patient
/*64 Active Patient count by age*/
SELECT (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) AS 'Age', COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
WHERE p.PatStatus=0
GROUP BY Age;
Shows all records with duplicate first and last name - To see PatNum, choose 'raw' option button in query window
/*65 - Possible duplicate accounts based on exact match first and last name. */
/* Lists all possible duplicates with preferred name, date created, and most recent procedure and appointment date. Excludes deleted patients */
/* Query code written/modified on: 11/05/2019 AlexG*/
/* Change the cut off date to only show duplicates if one of them has been created after this date*/
SET @CutOffDate = '2018-01-01';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SELECT
p1.PatNum AS 'AcctNum',
p1.LName,
p1.Fname,
p1.MiddleI,
p1.Birthdate,
p1.Preferred,
p1.AddrNote,
p1.PatStatus,
COALESCE(NULLIF(p1.SecDateEntry,DATE('0001-01-01')),'Conv.') AS 'DateCreated',
COALESCE((
SELECT
MAX(pl.ProcDate)
FROM procedurelog pl
WHERE pl.ProcStatus = 2
AND pl.PatNum = p1.PatNum
),'None') AS 'Recent Proc',
COALESCE((
SELECT
MIN(ap.AptDateTime)
FROM appointment ap
WHERE ap.AptStatus = 1 -- scheduled
AND ap.AptDateTime > CURDATE()
AND ap.PatNum = p1.PatNum
),'None') AS 'Next Visit'
FROM patient p1
INNER JOIN patient p2
ON p1.LName = p2.LName -- Same last name
AND p1.FName = p2.FName -- Same first name
AND p1.PatNum != p2.PatNum -- Not same patient
AND p2.patstatus != 4 -- Not deleted
WHERE p1.patstatus != 4 -- Not deleted
AND ((p1.SecDateEntry >= DATE(@CutOffDate)) OR (p2.SecDateEntry >= DATE(@CutOffDate))) -- One of the accounts was made since the cutoff date
GROUP BY p1.PatNum
ORDER BY p1.LName, p1.FName, p1.MiddleI, p1.Birthdate, p1.PatNum;
Active patients list who have seen a hygienist within a date range -
/*66 Active patients list who have seen a hygienist in date range*/
/*Query code written/modified: 02/17/2017*/
SET @pos=0, @FromDate='2017-01-01' , @ToDate='2017-01-15';
SELECT @pos:=@pos+1 AS numberofpatients, A.*
FROM (
SELECT pa.PatNum, MAX(ProcDate) AS 'Last Seen'
FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum = pl.PatNum
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus = 2 /*Completed*/
INNER JOIN provider pv ON pv.ProvNum = pl.ProvNum
INNER JOIN definition d ON d.DefNum = pv.Specialty
AND d.ItemName LIKE '%hyg%'
WHERE pa.patstatus = '0' /*Patient*/
GROUP BY pa.PatNum
ORDER BY pa.Lname
)A;
Subscribers grouped by employer, gives # of active patients who are subscribers with insurance through each employer - This just gives count, you can get names in query 188
/*67*/ SELECT EmpName AS 'Employer', COUNT(EmpName) AS '# Emp Patients' FROM insplan i
INNER JOIN employer e ON i.EmployerNum=e.EmployerNum
INNER JOIN inssub iss ON iss.PlanNum=i.PlanNum
INNER JOIN patient p on p.PatNum=iss.Subscriber
WHERE p.PatStatus=0 GROUP BY EmpName;
New patients for date range with ref source and sum of first visit fees - For Versions 17.1 and greater. Please update your version accordingly. Will not return patients with no referral source listed (designed for lending bank reporting req)
/*68 New patients for date range with ref source and sum of first visit fees*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2020-10-01' , @ToDate='2020-10-31';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
/*Query code written/modified: 04/04/2018,10/19/2020:SalinaK*/
SELECT
GROUP_CONCAT(pc.CodeNum) INTO @Broken
FROM procedurecode pc
WHERE pc.ProcCode IN ('D9986','D9987');
SELECT
p.PatNum AS 'Pat ID'
,CONCAT( -- Build a name string like OD's default
p.LName,
',',
(CASE
WHEN LENGTH(p.Preferred) > 0
THEN CONCAT(' \'', p.Preferred,'\'')
ELSE ''
END), -- Display preferred if we have one
' ',
p.FName,
(CASE
WHEN LENGTH(p.MiddleI) > 0
THEN CONCAT(' ', p.MiddleI)
ELSE ''
END) -- Display middle initial if we have one
) AS 'Patient'
,np.First AS 'FirstVisit'
,FORMAT(SUM(pl.ProcFee * (pl.UnitQty + pl.BaseUnits)),2) AS 'VisitFee'
,SUBSTRING_INDEX(ref.Refs,', ',1) AS 'Ref Last'
,SUBSTRING_INDEX(ref.Refs,', ',-1) AS 'Ref First'
FROM patient p
INNER JOIN procedurelog pl
ON p.PatNum = pl.PatNum
INNER JOIN (
/*First procedure date that's not a broken/cancelled apt code*/
SELECT
pl1.PatNum
,MIN(pl1.ProcDate) AS 'First'
FROM procedurelog pl1
WHERE pl1.ProcStatus = 2 -- Completed
AND NOT FIND_IN_SET(pl1.CodeNum,@Broken)
GROUP BY pl1.PatNum
) np
ON np.PatNum = p.PatNum
AND np.First = pl.ProcDate
AND np.First BETWEEN @FromDate AND @ToDate
INNER JOIN (
/*First entered referred from source per patient*/
SELECT
ra.PatNum
,SUBSTRING_INDEX(
GROUP_CONCAT(DISTINCT r.LName,', ',r.FName
ORDER BY ra.ItemOrder
SEPARATOR '|')
,'|',1) AS 'Refs'
FROM referral r
INNER JOIN refattach ra
ON r.ReferralNum = ra.ReferralNum
AND ra.RefType = 1 -- Ref From
GROUP BY ra.PatNum
) ref
ON ref.PatNum = p.PatNum
WHERE pl.ProcStatus = 2 -- Complete
GROUP BY p.PatNum
ORDER BY p.LName
Count of patients by Carrier with procedures completed in date range -
/*69 Count of patients by Carrier with procedures
completed in date range*/
SET @FromDate='2010-01-01' , @ToDate='2010-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 BETWEEN @FromDate AND @ToDate)
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
Income by insurance company for date range -
/*70 Income by insurance carrier for date range*/
SET @FromDate='2019-01-01' , @ToDate='2019-12-31'; /*Adjust dates as needed*/
/*------------ DO NOT MODIFY BELOW THIS LINE ------------*/
/*Query code written/modified on: 11/22/2019:RobG*/
SELECT
CarrierName,
SUM(CheckAmt) AS $Income
FROM claimpayment
WHERE CheckDate BETWEEN @FromDate AND @ToDate
GROUP BY CarrierName;
Carrier and patient list seen in date range - to include only active patients add WHERE...AND PatStatus=0
/*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, A.*
FROM (
SELECT 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 ProcDate >= @Start
AND ProcDate < @Before
/*this is in case we need additional patient information*/
/*you can add any patient or insurance fields to the SELECT statement*/
GROUP BY patient.PatNum, CarrierName
ORDER BY CarrierName
) A
Count of active patients seen in a date range grouped by billing type -
/*72 Count of active patients seen in a date range grouped by billing type*/
/*Query code written/modified: 02/20/2019*/
SET @FromDate='2008-01-01', @ToDate='2008-12-31';
SELECT
p.BillingType,
COUNT(DISTINCT p.PatNum) AS 'COUNT'
FROM patient p
INNER JOIN procedurelog pl
ON pl.PatNum = p.PatNum
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus = 2 -- Complete
WHERE p.PatStatus = 0 -- Patient
GROUP BY p.BillingType
Received Preauths over last X months -
/*73*/ 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;
Patient payments in date range -
/*75*/ 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;
New patients in date range with address and ref source - Patients will be listed mulitple times if they have mulitple referrals. For Versions 17.1 and greater. Please update your version accordingly.
/*76 New patients in date range with address and ref source. Patients will have multiple rows if they have mulitple referrals*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2021-10-01' , @ToDate='2021-10-31';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
/*Query code written/modified: 11/10/2021:ChrisD*/
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,
COALESCE(r.LName, '') AS 'RefLName',
COALESCE(r.FName, '') AS 'RefFName'
FROM patient p
LEFT JOIN refattach ra
ON p.PatNum = ra.PatNum
AND ra.RefType = 1 /*RefFrom*/
LEFT JOIN referral r
ON r.ReferralNum = ra.ReferralNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
ORDER BY p.LName, ra.ItemOrder
Returns guarantors of patients who have not received 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.
/*77*/ 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);
Patients with no scheduled apt who have not been in for a time period with the date of their last scheduled apt. - (From @daysIntervalStart days ago to @daysIntervalEnd days ago, change the interval currently 365 days: 1 year) This is useful for making a patient list before archiving patients, to call and try one last time.
/*78 Active (status) patients with no scheduled apt who have not been in for a time period with the date of their last completed apt. - (From @daysIntervalStart days ago to @daysIntervalEnd days ago, change the interval currently 365 days: 1 year) This is useful for making a patient list before archiving patients, to call and try one last time.*/
SET @daysIntervalStart=365/*<<<Just normally change this one*/, @daysIntervalEnd=0;
/*------------ DO NOT MODIFY BELOW THIS LINE ------------*/
/*Query code written/modified on: 03/28/2019:RobG*/
#
SET @pos=0;
SELECT
@pos:=@pos+1 AS 'NumberOfPatients',
a.PatNum,
a.HmPhone,
a.Address,
a.City,
a.State,
a.Zip,
a.LastApt,
a.DaysSince
FROM (
SELECT
patient.PatNum,
patient.HmPhone,
patient.Address,
patient.City,
patient.State,
patient.Zip,
tmp2.AptDateTime AS LastApt,
(TO_DAYS(CURDATE()) - TO_DAYS(tmp2.AptDateTime)) AS 'DaysSince'
FROM patient
INNER JOIN (
SELECT
PatNum,
MAX(AptDateTime) AS 'AptDateTime'
FROM appointment
WHERE AptStatus = 2 -- Complete
GROUP BY PatNum
) tmp2
ON patient.PatNum=tmp2.PatNum
LEFT JOIN (
SELECT DISTINCT
PatNum
FROM appointment
WHERE AptStatus = 1 -- Scheduled
)/*patients with scheduled appointments*/ tmp1
ON patient.PatNum=tmp1.PatNum
WHERE tmp1.PatNum IS NULL
AND (TO_DAYS(CURDATE()) - TO_DAYS(tmp2.AptDateTime)) NOT BETWEEN @daysIntervalEnd AND @daysIntervalStart
AND patient.PatStatus = 0 -- Patient
GROUP BY tmp2.PatNum
ORDER BY patient.LName, patient.FName ASC
)a;
Insurance claim procedures with UCR fee, InsEst and InsAmtPaid - Change date range as needed, does not distinguish between received claims, sent claims etc
/*80 Insurance claim procedures with UCR fee, InsEst and InsAmtPaid - Change date range as needed, does not distinguish between received claims, sent claims etc*/
SET @StartDate = '2009-08-01' , @EndDate = '2009-08-15';
SET @FeeSched = 'Standard'; -- Enter the exact fee schedule name here
/*------------ DO NOT MODIFY BELOW THIS LINE ------------*/
/*Query code written/modified on: 5/6/2009, 05/16/2019:RobG*/
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 feesched fs
ON f.FeeSched = fs.FeeSchedNum
AND fs.Description = @FeeSched
WHERE DateService BETWEEN @StartDate AND @EndDate;
All treatment planned and scheduled, all treatment planned total, returns these two dollar amounts, active patients - SEE REPLICATION WARNING for versions before 14.3.1. The second number includes the first, so the relationship beween the two amounts is the percentage of treatment planned work that is scheduled right now
/*81 This is a point in time query, what do I have scheduled, what do I have TP'd for active patients*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
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;
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.
/*82 Date Range limited: treatment planned and scheduled, treatment planned total, returns these two dollar amounts for all patients. Caution: the results will change depending on how long after the period you run the query. 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.*/
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 5/6/2009, 11/04/2021:MattG*/
SET @FromDate='2016-01-01', @ToDate='2021-01-15';
SELECT
'TPd And Scheduled' AS ProcCondition
,FORMAT(SUM(pl.ProcFee * (pl.BaseUnits + pl.UnitQty)), 2) 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 pl.DateTP BETWEEN DATE(@FromDate) AND DATE(@ToDate)
AND AptStatus=1 -- Scheduled
UNION ALL
SELECT
'Treatment Planned' AS ProcCondition
,FORMAT(SUM(ProcFee * (pl.BaseUnits + pl.UnitQty)), 2) 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 pl.DateTP BETWEEN DATE(@FromDate) AND DATE(@ToDate)
AND (
ISNULL(ap.aptnum)
OR AptStatus=6 -- Planned
OR AptStatus=3 -- UnschedList
);
Patients from given city seen in date range - theoretically helpful for splitting practice
/*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 @FromDate='2011-10-01', @ToDate='2011-10-31' ;
SELECT
DISTINCTROW patient.PatNum,
patient.city
FROM procedurelog
INNER JOIN patient
ON procedurelog.PatNum=patient.PatNum
WHERE ProcStatus=2
AND ProcDate BETWEEN @FromDate AND @ToDate
AND patient.City LIKE('%Port%')
List of patients with the specified code attached to a scheduled appointment with appointment date in the specified date range -
/*84 List of patients with the specified code attached to a scheduled
appointment with appointment date in the specified date range*/
SET @StartDate = '2021-03-01', @EndDate = '2021-03-31';
SET @Code ='%D0120%';
/*------------------- DO NOT MODIFY BELOW THIS LINE -------------------*/
/*Query code written/modified: 03/02/2021:SalinaK*/
SELECT
pa.PatNum,
pc.ProcCode,
pl.ProcFee * (pl.UnitQty + pl.BaseUnits) 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
AND pc.ProcCode LIKE @Code
INNER JOIN appointment ap
ON pl.AptNum = ap.AptNum
AND ap.AptStatus = 1 -- Scheduled
AND ap.AptDateTime BETWEEN DATE(@StartDate) AND DATE(@EndDate) + INTERVAL 1 DAY
ORDER BY pa.LName;
Patient and insurance Payments for date range for given provider - Patient and insurance Payments for date range for given provider
/*85 Sum of patient and insurance payments for date range for given provider*/
SET @StartDate='2021-10-01',@EndDate='2021-10-31';
SET @ProviderAbbreviation='DOC'; -- Enter provider abbreviation here. Uses partial matching. Leave blank for all.
/*--------------------DO NOT MODIFY BELOW THIS LINE--------------------*/
/* Query code written/modified 03/11/2022:ChrisD */
SELECT
p.PatNum,
main.PaymentType,
pv.Abbr,
main.PaymentAmt AS $PaymentAmt_
FROM patient p
INNER JOIN (
SELECT
ps.PatNum,
COALESCE(d.ItemName, "Income Transfer") AS 'PaymentType',
ps.ProvNum,
SUM(ps.SplitAmt) AS 'PaymentAmt'
FROM payment pm
INNER JOIN paysplit ps
ON pm.PayNum = ps.PayNum
AND ps.DatePay BETWEEN @StartDate AND @EndDate
LEFT JOIN definition d
ON d.DefNum = pm.PayType
GROUP BY ps.PatNum, d.ItemName
UNION ALL
SELECT
claimproc.PatNum,
'Ins Checks' AS 'PaymentType',
claimproc.ProvNum,
SUM(claimproc.InsPayAmt) AS 'PaymentAmt'
FROM claimproc
WHERE claimproc.DateCP BETWEEN @StartDate AND @EndDate
AND claimproc.Status IN (1, 4) -- received, supplemental
GROUP BY claimproc.PatNum
) main
ON main.PatNum = p.PatNum
INNER JOIN provider pv
ON pv.ProvNum = main.ProvNum
AND pv.Abbr LIKE CONCAT('%', @ProviderAbbreviation, '%')
ORDER BY p.LName, p.FName;
Adjustments summed by type -
/*86*/ SET @Start='2009-01-01' , @End='2009-12-31';
/*Adjust above Dates as needed*/
SELECT AdjType, SUM(AdjAmt), Count(AdjNum) AS AdjCount FROM adjustment
WHERE AdjDate >=@Start
AND AdjDate <=@End
GROUP BY AdjType
ORDER BY SUM(AdjAmt);
Sum of payments made by carrier for procedures in a date range -
/*88*/ SET @FromDate='2008-01-01', @Todate='2008-01-31' ;
/*Adjust above Dates as needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients', SUM(claimproc.InsPayAmt) as 'InsPaidTotal'
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 >= @FromDate
AND procedurelog.ProcDate < @ToDate
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
Insurance income for time period summed by guarantor and carrier -
/*90 Sum of payments made by carrier for procedures in a date range*/
/*Query code written/modified: 10/12/2015*/
SET @FromDate='2011-01-01', @ToDate='2015-12-31' ;
SELECT carrier.CarrierName, patient.Guarantor,SUM(claimproc.InsPayAmt) AS '$InsPayAmt_'
FROM patient
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum
INNER JOIN insplan ON insplan.PlanNum=claimproc.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
WHERE patient.PatStatus=0
AND claimproc.ProcDate BETWEEN @FromDate AND @ToDate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY patient.Guarantor, carrier.CarrierName
ORDER BY carrier.CarrierName;
Aging Report for Balance over 90 with no payment in last 30 - SEE REPLICATION WARNING for versions before 14.3.1.
/*91*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT CONCAT(g.LName,', ',g.FName,' ',g.MiddleI) AS Guarantor
,g.Bal_0_30,g.Bal_31_60,g.Bal_61_90,g.BalOver90
,g.BalTotal,g.InsEst,g.BalTotal-g.InsEst AS $PatPor,
MAX(paysplit.DatePay) AS LastPayment
FROM patient
INNER JOIN patient g ON patient.Guarantor=g.PatNum
LEFT JOIN paysplit
ON paysplit.PatNum=patient.PatNum
WHERE (patient.patstatus IN (0,1))
AND (g.BalOver90 > '.005')
GROUP BY patient.Guarantor
ORDER BY g.LName,g.FName;
SELECT Guarantor
,Bal_0_30,Bal_31_60,Bal_61_90,BalOver90 AS $BalOver90
,BalTotal AS $BalanceTotal,InsEst AS $InsEstimate, $PatPor AS $PatientPor,
DATE_FORMAT(LastPayment,'%m/%d/%Y') AS LastPayment FROM tmp WHERE DATE(LastPayment)<(CURDATE()- INTERVAL 30 DAY);
DROP TABLE IF EXISTS tmp;
Find an Insurance check by Check Number - Change check number between the '%%' symbols
/*92 Locate insurance checks by check number*/
SET @Check='%123%'; /*Change check number between '%%' symbols.*/
/*------------------- DO NOT MODIFY BELOW THIS LINE -------------------*/
/*Query code written/modified: 09/29/2020:SalinaK*/
SELECT
p.PatNum AS 'PatID',
CONCAT(p.LName,', ',p.FName,' ',p.MiddleI) AS NAME,
cpay.CheckDate,
ca.CarrierName,
cpay.CheckNum,
cp.ClaimNum,
FORMAT(SUM(cp.InsPayAmt),2) AS '$Amt_'
FROM claimpayment cpay
INNER JOIN claimproc cp
ON cpay.ClaimPaymentNum = cp.ClaimPaymentNum
AND cp.Status IN (1,4) -- Received, supplemental
INNER JOIN patient p
ON cp.PatNum = p.PatNum
INNER JOIN insplan ip
ON cp.PlanNum = ip.PlanNum
INNER JOIN carrier ca
ON ca.CarrierNum = ip.CarrierNum
WHERE cpay.CheckNum LIKE @Check
GROUP BY cp.ClaimNum
ORDER BY ca.CarrierName;
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.
/*95*/ SELECT LName,FName,WkPhone,HmPhone,WirelessPhone
FROM patient WHERE PatStatus=0 INTO OUTFILE "c:\\TEMP\\patients.csv" FIELDS TERMINATED BY ',';
Find an Insurance check by Amount and Date -
/*96*/ 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;
Get Patient info based on subscriber id (or part of it) - Add more fields from patient table as needed
/*97*/ SET @SubscriberID=('%123%');
SELECT p.PatNum, iss.SubscriberID FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub iss ON iss.InsSubNum=pp.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=iss.PlanNum
WHERE iss.SubscriberID LIKE @SubscriberID
ORDER BY p.LName, p.FName;
Procedures with notes for date range (completed procs) - Note that since we do not delete notes, there may be multiple entries if notes were altered To see last note only, try query 136 below
/*98*/ 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;
Find insurance plans without assigned benefits - Returns subscribers of plans (vs patients using plan)
/*99 Find insurance plans without assigned benefits*/
SELECT PlanNum, SubScriber
FROM inssub
WHERE AssignBen=0;
Patients with saved Treatment Plans and date of plan -
/*100*/ 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;
Patient, provider, balance, like shown in patient payment window - SEE REPLICATION WARNING for versions before 14.3.1.
/*101*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
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
AND paysplit.PayPlanNum = 0
GROUP BY patient.PatNum,ProvNum;
/*payplan princ reduction*/
/*Version 1: Payment plan principal for the entire office history for those using the original payplans accounting*/
INSERT INTO tempfambal (PatNum,Guarantor,ProvNum,AmtBal)
SELECT patient.PatNum,patient.Guarantor,payplancharge.ProvNum,-(pp.CompletedAmt)
FROM payplancharge, patient, payplan pp
WHERE patient.PatNum=payplancharge.PatNum
AND pp.PatNum = patient.PatNum
AND pp.CompletedAmt!=0
GROUP BY patient.PatNum,payplancharge.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;
CommLog notes by Type -
/*102*/ 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
New patients with Clinic and Referral source for date range - For Versions 17.1 and greater. Please update your version accordingly. Excludes patients who have never actually had a procedure completed.
/*103 New patients with Clinic and Referral source for date range*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 04/04/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2018-01-01' , @ToDate='2018-01-05';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SET @pos=0;
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
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 /*Completed*/
AND pl.ProcFee > 0 /*Remove procs with no charge*/
LEFT JOIN refattach ra ON p.PatNum=ra.PatNum
LEFT JOIN referral r ON r.ReferralNum=ra.ReferralNum
AND ra.RefType = 1 /*RefFrom*/
LEFT JOIN clinic c ON p.ClinicNum=c.ClinicNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND p.PatStatus=0 /*Patient - active*/
GROUP BY p.PatNum
ORDER BY c.Description, p.LName;
Count of patients seen in the date range by patient's assigned clinic with total for date range. - Exludes patients that don't have a completed procedure in the date range and those that have completed procedures with no fee.
/*104 Count of patients seen in the date range by patient's assigned clinic. Exludes patients that don't have a completed procedure in the date range and those that have completed procedures with no fee.*/
SET @FromDate='2008-01-01' , @ToDate='2008-01-31';
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 07/24/2020:MattG*/
SELECT
IFNULL(c.Description,'None') AS 'Clinic',
IFNULL(COUNT(DISTINCT p.PatNum),0) 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 -- Patient
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 -- Completed
AND pl.ProcFee > 0
GROUP BY c.Description
UNION ALL
SELECT
CONCAT('Total: ', @FromDate, ' to ', @ToDate) AS 'Clinic',
(
SELECT
COUNT(DISTINCT p.PatNum)
FROM patient p
INNER JOIN procedurelog pl
ON pl.PatNum=p.PatNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND p.PatStatus=0 -- Patient
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 -- Completed
AND pl.ProcFee>0
) AS 'Patients';
Patients names, addresses & phone(s) seen today -
/*105*/ 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 pl.ProcDate=DATE(curdate())
GROUP BY PatName
ORDER BY PatName
New Patient count, insured and not insured over date range - Also see #545 for breakdown by month over a long period
/*106*/ SET @FromDate='2008-06-01' , @ToDate='2008-06-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;
Referred procedures by date range -
/*107*/ 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;
Patients with NO images - (nonindexed table, so will take a VERY long time to run, upward of one minute) , also not to useful post version 5.6 since images also include statement PDFs, and in any version, scanned images et, generally used for determining missing files
/*108*/ SELECT p.PatNum FROM patient p LEFT JOIN document a ON p.PatNum=a.PatNum
WHERE a.PatNum IS NULL;
Patients' image count - (only returns patients with images, runs fast), note that images include PDFs stored by Open Dental
/*109*/ 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;
Procedures with date and description where the sum fee paid is greater than the fee - helpful for incorrect medicaid estimates
/*110*/ 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;
Procs with date and description w/ sum fee paid > fee and patient acct credit - Same query as 110, but adds condition: Patient balances < 0 (account credits) helpful for incorrect medicaid estimates, uses last balance from updated aging claimproc status is received or supplemental
/*111*/ 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;
Returns procedures with estimated insurance greater than charged fee claim proc status is not received AND the proc is complete AND the status of the claim is sent -
/*112*/ 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
Outstanding insurance claims by carrier -
/*113*/ 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;
Outstanding insurance claims for a particular carrier -
/*114*/ SET @Carrier='%Blue Cross%';
SELECT cl.PatNum,cl.DateSent,cl.DateService, 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;
Guarantors of families where no payment has been made in 1 Month and they are over 90 days past due -
/*115 Guarantors of families where no payment has been made in 1 Month and they are over 90 days past due,
period can be changed, you can use terms like 1 MONTH or 45 DAY for the interval*/
SELECT g.PatNum AS 'Number',CONCAT(g.LName, ", ", g.FName) AS 'Name', g.BalOver90 AS '$FamBalOver90',
DATE_FORMAT(MAX(ps.DatePay), '%m-%d-%Y') AS 'DateLastPay' , 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 g.BalOver90>1
GROUP BY g.PatNum
HAVING MAX(ps.DatePay)<(CURDATE()-INTERVAL 1 MONTH)
ORDER BY g.LName, g.FName;
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 (New report available in 5.7.0+ gives these PPO numbers without this query)
/*116*/ 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, received and supplemental claims*/
ORDER BY ProcDate;
Total Anticipated and incurred writeoffs for procedures completed in range - Like 116 but sum only
/*117*/ 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, received and supplemental claims*/
ORDER BY ProcDate
Mailing list of guarantors of patients with a particular carrier - Unlike some others, returns the guarantors of all patients with plan, not the subscriber, also if you drop the plan it goes off the list
/*118 Mailing list of guarantors of patients with a particular carrier, unlike some others, returns the guarantors of all patients with plan, not the subscriber, also if you drop the plan it goes off the list*/
/*Query code written/modified: 09/28/2015*/
SET @CarrierName='%PLAN%';
SELECT DISTINCTROW gu.salutation, gu.LName, gu.FName, gu.Address,
gu.Address2, gu.City, gu.State, gu.zip, gu.Birthdate, ib.DateEffective, ca.CarrierName
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=ib.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
INNER JOIN patient gu ON p.Guarantor=gu.PatNum
WHERE ca.CarrierName LIKE @CarrierName
ORDER BY ca.CarrierName, gu.LName;
Completed procedures for date range showing tooth surface - Change dates to change range
/*119 Completed procedures in the date range showing tooth surface*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate = '2016-04-19', @ToDate = '2016-04-21';
/*-------------------- Do not modify under this line --------------------*/
SELECT
CONCAT(patient.LName, ', ',patient.FName, ' ', patient.MiddleI) AS Patient,
pl.ProcDate,
pv.Abbr,
pc.ProcCode,
pc.AbbrDesc,
toothnum,
surf,
pl.ProcFee
FROM patient
INNER JOIN procedurelog pl
ON patient.PatNum = pl.PatNum
INNER JOIN procedurecode pc
ON pl.CodeNum = pc.CodeNum
INNER JOIN provider pv
ON pl.ProvNum = pv.ProvNum
WHERE pl.ProcStatus = 2 -- Complete
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
ORDER BY ProcDate,patient.LName, patient.FName ASC;
List of Guarantor's Fname and LName, Guarantor's Email address and the FName of those patient under that guarantor's account - excludes inactive patients
/*120*/ SELECT g.LName, g.FName, p.FName AS 'Patient', g.eMail
FROM patient p
INNER JOIN patient g ON p.Guarantor=g.PatNum
WHERE p.PatStatus=0 AND g.EMail LIKE ('%@%')
ORDER BY g.LName, g.FName;
List of active patients referred to you or from you in date range - For Versions 17.1 and greater. Please update your version accordingly.
/*121 List of active patients referred to you or from you in date range.*/
/*For Versions 17.1 and greater. Please update your version accordingly.*/
/*Query code written/modified: 04/04/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2018-01-01' , @ToDate='2018-01-05';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', -- Count returned rows
A.* -- Display all fields from FROM subquery
FROM ( -- Begin FROM subquery
SELECT p.PatNum, -- Get patient numbers/names
CONCAT(rf.LName , ", ", rf.FName) AS Referral, -- Get referral last and first name
ra.RefType, -- Returns True if their referral is referred from the referral, otherwise False
DATE_FORMAT(ra.RefDate,'%m/%d/%Y') AS RefDate -- Get the date referral was made
FROM patient p -- Start with patients
INNER JOIN refattach ra ON p.PatNum=ra.PatNum -- Get attached referrals for the patient
AND ra.RefDate BETWEEN @FromDate AND @ToDate -- For referrals created in your date range
INNER JOIN referral rf ON ra.ReferralNum=rf.ReferralNum -- Get the referrals information
AND p.PatStatus = '0' -- Only patients with a "Patient" status
) A -- End FROM subquery
ORDER BY A.Referral -- Sort in order of referral name
Count of active patients with each insurance plan (not subscribers) - NOTE:will not sum to total patients as some patients may have no insurance some may have more than one
/*122*/ SELECT carrier.CarrierName, COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM carrier
INNER JOIN insplan ip ON carrier.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.PlanNum=ip.PlanNum
INNER JOIN patplan pp ON pp.InsSubNum=ib.InsSubNum
INNER JOIN patient p ON pp.PatNum=p.PatNum
WHERE p.PatStatus=0
GROUP BY CarrierName
ORDER BY CarrierName;
Patients seen in time period with column indicating recall and non recall procs and if the apt was recall only - Useful for dentists trying to sell non recall procs
/*123 Counts procedures as recall where they match list*/
SET @FromDate='2019-01-01' , @ToDate='2019-11-26';/*Change Dates here */
SET @RecallCodes='D0120|D1120|D1110|D1203|D1204|D0270|D0272|D0273|D0274|D0330'; /*Enter procedure codes separated by a pipe ('|' without the quotes). For all leave blank as '' */
/*------------ DO NOT MODIFY BELOW THIS LINE ------------*/
/*Query code written/modified on: 11/26/2019:RobG*/
#
SET @RecallCodes=(CASE WHEN LENGTH(@RecallCodes)=0 THEN '^' ELSE CONCAT('^',REPLACE(@RecallCodes,'|','$|^'),'$') END);
#
SET @pos=0;
SELECT
@pos:=@pos+1 AS 'Count',
rep.PatNum,
rep.LastProcDate,
rep.ProcCount,
rep.RecallProcs,
rep.RecallOnly
FROM (
SELECT
PatNum,
DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastProcDate',
(COUNT(SetRecall)) AS 'ProcCount',
SUM(SetRecall) AS 'RecallProcs',
(CASE WHEN ((COUNT(SetRecall) -SUM(SetRecall))=0) THEN 'Yes' ELSE 'No' END) AS 'RecallOnly'
FROM (
SELECT
PatNum,
ProcDate,
ProcCode,
ProcNum,
(CASE WHEN ProcCode REGEXP @RecallCodes THEN 1 ELSE 0 END) AS 'SetRecall'
FROM procedurelog pl
INNER JOIN procedurecode pc
ON pl.CodeNum = pc.CodeNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate -- Date range
AND ProcStatus = 2 -- Complete
) tmp
GROUP BY PatNum
ORDER BY RecallOnly, LastProcDate
) rep
Commlog entries in the specified date range, for the specified commlog type. -
/*124 Commlog entries in the specified date range, for the specified commlog type.*/
SET @FromDate = '2008-06-01' , @ToDate = '2008-06-30',
@CommlogType = ''; -- Enter commlog types between the apostrophes separated by a comma
/*-------------------- Do not modify under this line --------------------*/
/*Query code written/modified: 08/23/2019 MattG*/
#
SET @pos=0,
@CommlogType=(CASE WHEN LENGTH(@CommlogType) = 0 THEN '^' ELSE CONCAT('^',REPLACE(REPLACE(@CommlogType,', ','$|^'), ',','$|^'),'$') END);
SELECT
@pos:=@pos+1 AS 'Count',
display.PatNum,
display.CommDateTime,
display.Note,
display.ItemName
FROM (-- Gets the data, outside layer for count
SELECT
commlog.PatNum,
CommDateTime,
Note,
ItemName
FROM commlog
INNER JOIN definition
ON commlog.CommType=definition.DefNum
AND definition.ItemName REGEXP @CommlogType
INNER JOIN patient
ON commlog.PatNum=patient.PatNum
WHERE CommDateTime BETWEEN @FromDate AND @ToDate + INTERVAL 1 DAY
ORDER BY patient.LName, patient.FName
) display;
Hygiene Production For Time Period -
/*125*/ SET @FromDate='2008-06-01' , @ToDate='2008-06-30';
SELECT CONCAT(patient.LName, ', ',patient.FName, ' ', patient.MiddleI) As Patient, pl.ProcDate, pv.Abbr,pc.ProcCode, pc.AbbrDesc, pl.ProcFee FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pv ON pl.ProvNum=pv.ProvNum
WHERE pl.ProcStatus = '2' AND (pl.ProcDate >=@FromDate
AND pl.ProcDate <= @ToDate) AND pc.IsHygiene=1
ORDER BY ProcDate,patient.LName, patient.FName ASC;
Hygiene Summed by Procedure for Date range -
/*126 Hygiene Summed by Procedure for Date range*/
SET @FromDate='2016-01-01' , @ToDate='2016-01-31'; -- Allows for a date range
SELECT COUNT(*), -- Counts all returned rows
pv.Abbr, -- Shows the provider abbreviation
pc.ProcCode, -- Shows the procedure code
pc.AbbrDesc, -- Shows the procedure code abbreviation
AVG(pl.ProcFee) AS '$Ave. Fee' , -- Averages all the fees
SUM(pl.ProcFee) AS '$Tot. Prod' -- Sums up all the fees for a gross total
FROM patient -- Start with patients
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum -- Includes procedures for the patient
AND pl.ProcStatus = 2 -- Only for completed procedures
AND (pl.ProcDate >=@FromDate AND pl.ProcDate <= @ToDate) -- Only for procedures completed in the date range
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum -- Includes procedure code information for the procedures
AND pc.IsHygiene=1 -- Only for hygiene procedure codes
INNER JOIN provider pv ON pl.ProvNum=pv.ProvNum -- Includes provider info on the procedure
GROUP BY pc.ProcCode, pv.ProvNum -- One row per procedure code, per provider
ORDER BY ProcCode; -- In order by procedure code
List of active patients who have never had one of a user-defined list of procedures. Includes last visit date and total procs completed on patient. -
/*127*/ SELECT patient.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', COUNT(procedurelog.ProcNum) AS '# Procs Total'
FROM patient,procedurelog
WHERE patient.PatNum NOT IN
(SELECT DISTINCT p.PatNum FROM patient p
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
AND pc.ProcCode IN('D0120','D1110','D1204') #edit procedures here
WHERE pl.ProcStatus='2')
AND procedurelog.PatNum=patient.PatNum AND procedurelog.ProcStatus=2 AND patient.PatStatus=0
GROUP BY procedurelog.PatNum
ORDER BY patient.LName, patient.FName
Count of Patients seen by each provider in date range - A patient will count once for each provider seen in the date range.
/*128 Count of Patients seen by each provider in date range*/
/*Query code written/modified: 02/21/2018*/
/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @StartDate='2018-01-01', @EndDate='2018-01-31';
/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SELECT Abbr AS 'Provider',
COUNT(DISTINCT patient.PatNum) AS 'Patients Seen'
FROM patient
INNER JOIN procedurelog
ON procedurelog.PatNum = patient.PatNum
AND procedurelog.ProcStatus = 2 /*Completed Procedures*/
AND procedurelog.ProcDate BETWEEN @StartDate AND @EndDate
INNER JOIN provider
ON procedurelog.ProvNum = provider.ProvNum
WHERE patient.patstatus = 0 /*Active Patients*/
GROUP BY provider.Abbr
ORDER BY provider.Abbr
Guarantor (Mailing) info for New Patients with last seen Date of any dependent - SEE REPLICATION WARNING for versions before 14.3.1. for Pedo practice mailing letter to parents of new patients
/*129*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT DISTINCT PatNum, Guarantor FROM patient WHERE PatStatus=0;
/*this creates a tmp table with all patnums and the patnum of the guarantor*/
SET @pos=0, @FromDate='2008-07-01' , @ToDate='2008-07-31';
SELECT @pos:=@pos+1 as 'Count', LName, FName,DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', Address, Address2, City, State, Zip FROM patient
INNER JOIN tmp ON patient.PatNum=tmp.PatNum
INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
WHERE procedurelog.ProcStatus=2
AND DateFirstVisit BETWEEN @FromDate AND @ToDate
GROUP BY tmp.Guarantor
ORDER BY LName;
DROP TABLE IF EXISTS tmp;
Patients not seen since a date plus proc count -
/*130*/ SELECT patient.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', COUNT(procedurelog.ProcNum) AS '# Procs Total'
FROM patient,procedurelog
WHERE procedurelog.PatNum=patient.PatNum AND procedurelog.ProcStatus=2 AND patient.PatStatus=0
GROUP BY procedurelog.PatNum
HAVING MAX(ProcDate)<'2008-01-15'
ORDER BY patient.LName, patient.FName
Subscribers with insurance of specific employer(s) -If you only want one employer, duplicate name in both fields - Replace the text between the % signs, you can use any part of employer name, not case specific
/*131 Subscribers with insurance of specific employers, uses insurance employer(s) you should repeat the employer name if you only want one, many times employers are listed more than one way, like HP and Hewlett Packard*/
SELECT EmpName AS 'Employer', PatNum, p.address, p.address2,p.city,p.state,p.zip
FROM patient p
INNER JOIN inssub iss ON iss.Subscriber=p.PatNum
INNER JOIN insplan i ON i.PlanNum=iss.PlanNum
INNER JOIN employer e ON i.EmployerNum=e.EmployerNum
WHERE p.PatStatus=0 AND
((empname like ('%IBM%')) OR (empname like ('%Frijole Taco Stand%')))
ORDER By EmpName, p.lname;
Active patients who have never had any procedure(s)completed - note that active means that this is the status of those patients. The idea being that you may wish to review these accounts to follow up and try and schedule work or deactivate them.
/*132*/ SELECT p.PatNum FROM patient p
LEFT JOIN procedurelog pl ON p.PatNum=pl.PatNum AND ProcStatus=2
WHERE (pl.PatNum IS NULL) AND p.PatStatus=0
ORDER BY LName;
Outstanding insurance claims by Date of Service not including Preauths -
/*133*/ SELECT cl.PatNum,cl.DateService,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
DateService<(CURDATE()-INTERVAL 30 DAY) AND
ClaimType<>'PreAuth'
ORDER BY ca.CarrierName,p.LName;
Preauths received in last year but procedures not scheduled -
/*134*/ SELECT pl.PatNum, pl.ProcFee,pc.ProcCode, c.DateReceived, ca.CarrierName,
(SELECT SUM(InsPayEst) FROM claimproc WHERE claimproc.ProcNum=pl.ProcNum AND claimproc.Status=2) AS 'InsPayEst'
FROM claim c
INNER JOIN claimproc cp ON c.ClaimNum=cp.ClaimNum AND c.ClaimType='PreAuth' AND c.ClaimStatus='R'
INNER JOIN insplan i ON cp.PlanNum=i.PlanNum
INNER JOIN carrier ca ON i.CarrierNum=ca.CarrierNum
INNER JOIN procedurelog pl ON cp.ProcNum=pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
WHERE AptNum=0 AND DateReceived>(CURDATE()-INTERVAL 12 MONTH)
AND pl.ProcStatus=1;
Completed procedures for date range, limited to specified procedures - Enter procedure codes separated by a comma. If left blank this will search for all ProcCodes
/*135 Completed procedures for a date range, limited to specific procedures*/
SET @FromDate='2017-01-01' , @ToDate='2018-01-31';
SET @Codes = 'D0220,D0110,D1234' /*Enter procedure codes here, separated by a comma. If left blank this will search for all ProcCodes*/;
/*---DO NOT MODIFY BELOW THIS LINE---*/
/*Query code written/modified: 02/20/2018, 03/15/2021:SalinaK*/
#
SET @Codes=(CASE WHEN LENGTH(@Codes)=0 THEN '^' ELSE CONCAT('^',REPLACE(@Codes,',','$|^'),'$') END);
SELECT CONCAT(patient.LName, ', ',patient.FName, ' ', patient.MiddleI) AS Patient,
pl.ProcDate,
pv.Abbr,
pc.ProcCode,
pc.AbbrDesc,
ToothNum,
FORMAT(pl.ProcFee*(pl.UnitQty+pl.BaseUnits),2) AS ProcFee_
FROM patient
INNER JOIN procedurelog pl
ON patient.PatNum=pl.PatNum
AND pl.ProcStatus = 2 /*Completed procedures*/
AND pl.ProcDate BETWEEN @FromDate AND @ToDate
INNER JOIN procedurecode pc
ON pl.CodeNum= pc.CodeNum
AND pc.ProcCode REGEXP @Codes
INNER JOIN provider pv
ON pl.ProvNum=pv.ProvNum
ORDER BY ProcDate, patient.LName, patient.FName ASC;
Returns all notes for procedures in given range (check 98) - SEE REPLICATION WARNING for versions before 14.3.1. Great for reviewing the days notes, export to view full, longer notes
/*136*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
SET @pos=0, @FromDate='2013-01-17' , @ToDate='2013-01-17';
DROP TABLE IF EXISTS tmp1;
CREATE TABLE tmp1 SELECT pl.PatNum, ProcDate,Max(pn.EntryDateTime) AS 'NoteEntered',
pc.ProcCode,pl.ProcNum, p.LName FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
INNER JOIN procnote pn ON pl.ProcNum=pn.ProcNum
INNER JOIN patient p ON pl.PatNum=p.PatNum
WHERE DATE(pn.EntryDateTime) BETWEEN @FromDate AND @ToDate
AND LENGTH(Note)>0 GROUP BY pn.ProcNum;
SELECT @pos:=@pos+1 as 'Count', tmp1.PatNum, tmp1.ProcDate, tmp1.ProcCode, tmp1.NoteEntered, pn.Note FROM tmp1
INNER JOIN procnote pn ON tmp1.ProcNum=pn.ProcNum AND (pn.EntryDateTime)=(tmp1.NoteEntered)
ORDER BY tmp1.LName;
DROP TABLE IF EXISTS tmp1;
New Patients in Date Range with Date of last visit and Procedure Count -
/*137*/ SET @pos=0, @FromDate='2008-06-01' , @ToDate='2008-06-31';
SELECT @pos:=@pos+1 as 'Count', patient.PatNum, LName, FName, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit',
COUNT(procedurelog.ProcNum) AS '# Procs Total'
FROM patient
INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0
AND DateFirstVisit BETWEEN @FromDate AND @ToDate
GROUP BY procedurelog.PatNum
ORDER BY LName;
Patients with a given insurance fee schedule -
/*138 Active patients with given insurance fee schedule*/
SET @FeeSched = ''; /*Enter exact fee schedule names between the '', separated by a comma. Must not end in a comma. Will search all if left blank.*/
/*---DO NOT MODIFY BELOW THIS LINE---*/
/*Query code written/modified: 01/22/2019 by SalinaK, 05/14/2020 AlexG*/
#
SET @FeeSched = (CASE
WHEN LENGTH(@FeeSched) = 0
THEN '^'
ELSE CONCAT('^',REPLACE(@FeeSched,',','$|^'),'$')
END);
SELECT
carrier.CarrierName,
p.PatNum,
feesched.Description AS 'FeeSchedule'
FROM carrier
INNER JOIN insplan ip
ON carrier.CarrierNum = ip.CarrierNum
INNER JOIN inssub iss
ON ip.PlanNum = iss.PlanNum
INNER JOIN patplan pp
ON iss.InsSubNum = pp.InsSubNum
INNER JOIN patient p
ON pp.PatNum = p.PatNum
INNER JOIN feesched
ON ip.FeeSched = feesched.FeeSchedNum
WHERE p.PatStatus = 0 -- Active patients
AND feesched.Description REGEXP @FeeSched
ORDER BY CarrierName;
Check secondary claims received in date range for writeoff amounts -
/*139*/ SET @pos=0, @FromDate='2008-06-01' , @ToDate='2008-06-31';
SELECT @pos:=@pos+1 as 'Count',cl.PatNum,WriteOff,cl.DateReceived,
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='R' AND
DateReceived Between @FromDate and @ToDate AND
ClaimType='S'
ORDER BY cl.DateReceived;
Mailing List for patients with TP procs without a scheduled apt -
/*140*/ SELECT LName, FName, Address, Address2, City, State, Zip, SUM(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) AND ProcStatus=1 AND Length(Zip)>1 AND PatStatus=0
GROUP BY patient.PatNum
ORDER BY patient.LName, patient.FName ASC;
Recall information with e-mail -
/*141*/ SELECT p.LName, p.FName, p.BirthDate, p.BillingType, p.Email, r.DateDue, d.ItemName AS 'RecallStatus'
FROM patient p
INNER JOIN recall r ON p.PatNum=r.PatNum
LEFT JOIN definition d ON r.RecallStatus=d.DefNum
Count of complete appointments in date range -
/*142*/ SET @Start ='2008-08-01', @End='2008-08-31';
SELECT Count(*) FROM appointment WHERE aptstatus=2 AND AptDateTime BETWEEN @Start AND @End+INTERVAL 1 DAY;
Treatment planned procedures for patients with no scheduled apt with specified carrier - (see also #50 & #56)
/*143*/ SET @Carrier='%delta%';
SELECT CONCAT(LName, ', ',FName, ' ', MiddleI) As Patient,
pc.ProcCode as 'Code', abbrdesc as 'Description', ToothNum,
DATE_FORMAT(pl.ProcDate,'%m-%d-%Y') AS 'Date',
c.CarrierName, ProcFee
FROM carrier c
INNER JOIN insplan ip ON c.CarrierNum=ip.CarrierNum
INNER JOIN inssub ib ON ib.PlanNum=ip.PlanNum
INNER JOIN patplan pp ON ib.InsSubNum=pp.InsSubNum
INNER JOIN patient p ON pp.PatNum=p.PatNum
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum