#Open dental queries update#

* After saving the resulting datafile, you would use the letter merge feature of Word to actually print the cards or labels. *35 List of families seen in the last three years - Useful for generating a list of patients for Christmas cards.*/ List of families seen in the last three years - Useful for generating a list of patients for Christmas cards. ORDER BY referral.Specialty, referral.LName, referral.FName INNER JOIN refattach ON patient.PatNum=refattach.PatNumĪND refattach.RefDate BETWEEN AND JOIN referral ON referral.ReferralNum=refattach.ReferralNum *27 A list of referrals during a specific date range.*/ Best for exporting to a text file for use in a letter merge. INNER JOIN procedurelog pl ON pl.PatNum=ra.PatNumĪND pl.ProcDate BETWEEN AND BY r.ReferralNumĪ list of referrals during a specific date range. INNER JOIN refattach ra ON r.ReferralNum=ra.ReferralNum SET NOT MODIFY BELOW THIS LINE-*/įORMAT(SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)),2) AS $HowMuch

#Open dental queries update#

Please update your version accordingly.*/ *20 List of referral sources, how many unique patients referred, and how much production from each source*/ Referral sources with how many unique patients referred and gross production for the specified date range - Similar to internal Referral Analysis Report. INSERT INTO tempbroken SELECT PatNum,COUNT(*) *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.*/ This query will not work on versions 7.8.1 or higher of Open Dental as the school table was removed.*/ This query will not work on versions 7.8.1 or higher of Open Dental as the school table was removed. SEE REPLICATION WARNING for versions before 14.3.1. WHERE appointment.AptDateTime LIKE AptStatus != 6 /*Planned*/ INNER JOIN patient p ON p.PatNum=B.PatNum LEFT JOIN payplan pplan ON pplan.PayPlanNum=ppc.PayPlanNum WHEN pplan.PlanNum=0 THEN ppc.Principal+ppc.Interest ELSE 0 END) #if debit and a patient plan, add principal and interest (CASE WHEN ppc.ChargeType != 0 THEN -ppc.Principal #if credit, subtract the principal SELECT 'PayPlan2' AS TranType, ppc.PatNum AS PatNum, *Version 2: Payment plan charges for those using line-item account for payplans*/

SELECT 'PayPlan1' AS TranType,pp.PatNum AS PatNum,pp.PayPlanDate AS TranDate, *Version 1: Payment plan principal for the entire office history for those using the original payplans accounting*/ WHERE cp.Status IN (1,4,5,7)/*received,supplemental,CapClaim, or CapComplete*/ (CASE WHEN cp.payplannum = 0 THEN -cp.InsPayAmt ELSE 0 END)-cp.Writeoff AS TranAmount SELECT 'InsPay' AS TranType,cp.PatNum AS PatNum,cp.DateCp AS TranDate, *Claim payments for the entire office history*/ SELECT 'Adj' AS TranType,a.PatNum AS PatNum,a.AdjDate AS TranDate, *Get the adjustment dates and amounts for the entire office history*/ (CASE WHEN IN (1,3) THEN (CASE WHEN ps.PayPlanNum=0 THEN -ps.SplitAmt ELSE 0 END) ELSE -ps.SplitAmt END) AS TranAmount SELECT 'Pay' AS TranType,ps.PatNum AS PatNum,ps.DatePay AS TranDate, *Paysplits for the entire office history*/ Pl.ProcFee*(pl.UnitQty+pl.BaseUnits) AS TranAmount SELECT 'Fee' AS TranType,pl.PatNum AS PatNum,pl.ProcDate AS TranDate, *Get the completed procedure dates and charges for the entire office history*/ *Get Patient level charges and credits*/ *Get the family level charges, credits, and ins estimates*/ INNER JOIN patient ON appointment.PatNum=patient.PatNum SET ValueString FROM preference WHERE prefName LIKE 'PayPlansVersion'),1) įORMAT(IFNULL(aging.PatBal,0),2) AS PatEstBalance *Change dates between the ' ' in format 'YYYY-mm-dd'*/ *9 Estimated balances for patients with an appointment on a specific day*/ The account balances for all patients with an appointment on a specific day. WHERE pl.ProcStatus=1 - Treatment Planned ,SUBSTRING(p.MiddleI, 2, LENGTH(p.MiddleI)-1) ,IF( - Display middle initial if we have one ,SUBSTRING(p.FName, 2, LENGTH(p.FName)-1) ,SUBSTRING(p.Preferred, 2, LENGTH(p.Preferred)-1)

,SUBSTRING(p.LName, 2, LENGTH(p.LName)-1) *- Do not modify under this line -*/ĬONCAT( - Build a name string like OD's default *2 All treatment planned procedures, ordered by patient*/ All treatment planned procedures, ordered by patient - See also #50 & #56