How about this report?

[fishdrzig]

I have someone interested in looking at my practice and he needs just one report from Open Dental. How embarrassing when I can't produce it. Went through all the queries and even called tech support. Can anyone recommend a solution. He is coming in on Friday. I thought this would be so easy, but it doesn't look good!!! Frustrating.

Annual production reports for 3 years and year-to-date for the current year showing production, adjustments collections by provider by procedure (or by procedure by provider), this should be a report by ADA codes.

Can this be done with Open Dental??

[rhaber123]

Click on Reports / Production and Income / More Options
Here you can print REPORTS showing productions, adjustments, and collections , Annual production reports and year-to-date reports by provider if needed too

[rhaber123]

top 100 procedures yearly
change the dates accordingly

SET @FromDate='2015-01-01' , @ToDate='2015-12-31';
SELECT Count(*),pv.Abbr, pc.ProcCode, pc.AbbrDesc, AVG(pl.ProcFee) as '$Ave. Fee' , SUM(pl.ProcFee) AS '$Tot. Prod' 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)
GROUP BY pc.ProcCode
ORDER BY Count(*)DESC Limit 100

[Jandrew]

If you haven't found anything that suits your needs, please feel free to submit a query request online outlining exactly what you need from your report. If you mention that you need the report by Friday, we will do our best to accommodate your needs (though expedited reports tend to be more costly). If you find a report that is similar to what you want and needs only minor changes, our team may even be able to do it free of charge. As always, please don't hesitate to call us if you have any questions, or even if you would like someone to step through the query request process with you.

[Jandrew]

I was just looking through some of the query examples and found #1022. Is this close to what you want?
You can edit the dates at the top to what you want them to be.


/*1022 Procedure code totals by ProcCode and provider for completed procedures with completion date in date range.*/
/*Showing production, writeoffs, adjustments, and net production*/
/*Query code written/modified: 10/30/2014*/
SET @FromDate='2014-01-10', @ToDate='2014-01-10';
SELECT A.Provider,A.ProcCode,
COUNT(A.ProcCode) AS 'Quantity',
SUM(A.ProcFee) AS $ProcFee_,
SUM(A.WriteOffs) AS $WriteOffs_,
SUM(A.Adjustments) AS $Adjustments_,
SUM(A.NetProd) AS $NetProd_
FROM (
SELECT provider.Abbr AS 'Provider',
pc.ProcCode,
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0) AS 'ProcFee',
COALESCE(cp.WriteOffs,0) AS 'WriteOffs',
COALESCE(SUM(adj.AdjAmt),0) AS 'Adjustments',
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0)
-COALESCE(cp.WriteOffs,0)+COALESCE(SUM(adj.AdjAmt),0) AS 'NetProd',
provider.Abbr
FROM procedurelog pl
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status IN (1,4)
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp ON pl.ProcNum=cp.ProcNum
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status=7
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
)cp7 ON pl.ProcNum=cp7.ProcNum
INNER JOIN patient ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN provider ON provider.ProvNum=pl.ProvNum
LEFT JOIN adjustment adj ON adj.ProcNum=pl.ProcNum
WHERE pl.ProcStatus='2'
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
) A
GROUP BY A.Abbr,A.ProcCode
ORDER BY A.Abbr,A.ProcCode

[Tom Zaccaria]

I wasn't looking for it but I like this query very much.
Would be even better if we could pull AbbrDesc from Procedurecode. This way you wouldn't have to remember what the ADA codes represent.
Nice find!

drtmz

[tgriswold]

Added ProcDesc column for you.

Code: Select all

/*1022 Procedure code totals by ProcCode and provider for completed procedures with completion date in date range.*/
/*Showing production, writeoffs, adjustments, and net production*/
/*Query code written/modified: 12/18/2015*/
SET @FromDate='2014-01-10', @ToDate='2014-01-10';
SELECT A.Provider,A.ProcCode,A.AbbrDesc AS ProcDesc,
COUNT(A.ProcCode) AS 'Quantity',
SUM(A.ProcFee) AS $ProcFee_,
SUM(A.WriteOffs) AS $WriteOffs_,
SUM(A.Adjustments) AS $Adjustments_,
SUM(A.NetProd) AS $NetProd_
FROM (
SELECT provider.Abbr AS 'Provider',
pc.ProcCode,
pc.AbbrDesc,
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0) AS 'ProcFee',
COALESCE(cp.WriteOffs,0) AS 'WriteOffs',
COALESCE(SUM(adj.AdjAmt),0) AS 'Adjustments',
pl.ProcFee*(CASE pl.UnitQty+pl.BaseUnits WHEN 0 THEN 1 ELSE pl.UnitQty+pl.BaseUnits END)-IFNULL(COALESCE(cp7.WriteOffs,0),0)
-COALESCE(cp.WriteOffs,0)+COALESCE(SUM(adj.AdjAmt),0) AS 'NetProd',
provider.Abbr
FROM procedurelog pl
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status IN (1,4)
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum	
)cp ON pl.ProcNum=cp.ProcNum
LEFT JOIN (
SELECT SUM(cp.WriteOff) AS WriteOffs,pl.ProcNum
FROM procedurelog pl
INNER JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=2
AND cp.Status=7
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum	
)cp7 ON pl.ProcNum=cp7.ProcNum
INNER JOIN patient ON patient.PatNum=pl.PatNum 
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
INNER JOIN provider ON provider.ProvNum=pl.ProvNum
LEFT JOIN adjustment adj ON adj.ProcNum=pl.ProcNum
WHERE pl.ProcStatus='2'
AND pl.DateEntryC BETWEEN @FromDate AND @ToDate
GROUP BY pl.ProcNum
) A
GROUP BY A.Abbr,A.ProcCode
ORDER BY A.Abbr,A.ProcCode

[fishdrzig]

Travis

Yes, this does look like something that I can use. Can you show me where I need to enter info (Date, provider etc....) to make it work for me?

[Tom Zaccaria]

Great. Thank you,
drtmz

[tgriswold]

Travis

Yes, this does look like something that I can use. Can you show me where I need to enter info (Date, provider etc....) to make it work for me?

To adjust the date range, change the @FromDate='YYYY-MM-DD' and @ToDate='YYYY-MM-DD' at the top of the query on the fourth line. For example:

Code: Select all

SET @FromDate='2015-12-01', @ToDate='2015-12-31'; 

Currently this query does not have a provider filter.