NEED URGENT HELP FOR FINDING A FEW SPECIFIC REPORTS!!!

[canyonfalls1]

Hello,

I need a query for each of these types of reports and I'm having trouble finding examples of them:

Patient analysis - I know there's a New Patient option, but was hoping I could do it by year with a total # for each month of each year rather than printing out 30 pages of new patients

Revenue by Source - I need a year to date break down of collections by cash, CC, insurance, etc.

Production by Service code - also need a year to date break down of the production our procedure codes generate.

Thank you all SOOOO Much, this was just dropped in my lap and I need it by tomorrow!!!

[bpcomp]

You need custom queries. Look at this page for examples http://opendentalsoft.com:1942/ODQueryL ... yList.aspx.

This page describes how to run the query http://www.opendental.com/manual/queryrun.html.

Good luck.

[Arna]

1. Patient analysis - I know there's a New Patient option, but was hoping I could do it by year with a total # for each month of each year rather than printing out 30 pages of new patients

This one will require a custom query. We don't have this specific report available.

2. Revenue by Source - I need a year to date break down of collections by cash, CC, insurance, etc.

The Daily Payments Report can be run to break list all the payments, however 6 months in to the year, you may not want all that data.

This query may be more what you are after

Code: Select all

/* 483 Summary of payments by payment type for date range.*/
SET @FromDate='2016-01-01' , @ToDate='2016-12-31';
SELECT definition.ItemName AS PaymentType, 
SUM(paysplit.SplitAmt) AS $PaymentAmt 
FROM payment,definition,paysplit 
WHERE paysplit.DatePay BETWEEN @FromDate AND @ToDate 
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 BETWEEN @FromDate AND @ToDate

3. Production by Service code - also need a year to date break down of the production our procedure codes generate.

You should run the Daily Procedures Report, set the date range and Group by Procedure Code.

This query is also handy at a higher level:

Code: Select all

/*45*/ SET @FromDate= '2016-01-01', @ToDate='2016-12-01'; /*change dates here*/ 
/*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 @TotNum=0, @TotFee=0; 
DROP TABLE IF EXISTS tmp1; DROP TABLE IF EXISTS tmp2;
/*Create temp table containing summary info by procedure*/
 CREATE TABLE tmp1 SELECT MID(procedurecode.ProcCode,2,4) AS 'cat', Count(*) as 'num',
SUM(procedurelog.ProcFee) as 'fee'
FROM procedurelog,procedurecode,definition
WHERE procedurelog.ProcStatus =2
AND procedurelog.CodeNum=procedurecode.CodeNum
AND definition.DefNum=procedurecode.ProcCat
AND procedurelog.ProcDate >= @FromDate
AND procedurelog.ProcDate <= @ToDate
GROUP BY cat
ORDER BY cat;
/*Create a temp table grouping the procedures and info by range*/ 
CREATE TABLE tmp2 (Category CHAR(40), Fees FLOAT NOT NULL, Number FLOAT NOT NULL, PercDollar FLOAT NOT NULL, PercQuant FLOAT NOT NULL);
/*Create Prepared insert Statements and execute*/
/*A do loop could be used here but would not neccesarily work on all DB types*/ 
PREPARE ins FROM 'INSERT INTO tmp2 (Category, Fees, Number) SELECT ?, SUM(Fee), SUM(num) FROM tmp1 WHERE cat>? AND cat<?'; SET @cat='Diagnostic', @StCode=99, @EndCode=1000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Preventative', @StCode=999, @EndCode=2000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Restorative', @StCode=1999, @EndCode=3000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Endodontics', @StCode=2999, @EndCode=4000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Periodontics', @StCode=3999, @EndCode=5000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Prosthodontics (Removable)', @StCode=4999, @EndCode=5900; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Maxillofacial Prosthestics', @StCode=5899, @EndCode=6000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Implant Services', @StCode=5999, @EndCode=6200; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Prosthodontics (Fixed)', @StCode=6199, @EndCode=7000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Oral and Maxillofacial Surgery', @StCode=6999, @EndCode=8000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Orthodontics', @StCode=7999, @EndCode=9000; EXECUTE ins USING @cat, @StCode, @EndCode; SET @cat='Adjunctive General Services', @StCode=8999, @EndCode=10000; EXECUTE ins USING @cat, @StCode, @EndCode; /*Insert totals, can't use procedure when changing @variable values*/ INSERT INTO tmp2 (Category, Fees, Number) SELECT 'Total', @TotFee:=SUM(fee), @TotNum:=SUM(num) FROM tmp1 WHERE cat>99 AND cat<10000; /*Cacluate the percentages*/ UPDATE tmp2 SET PercDollar= CASE WHEN @TotFee = 0 THEN 0 ELSE
FORMAT(100*Fees/@TotFee,2) END;
UPDATE tmp2 SET PercQuant= CASE WHEN @TotNum = 0 THEN 0 ELSE
FORMAT(100*Number/@TotNum,2) END;
/*Display Results*/
SELECT Category,Number, Fees as '$Fees', PercDollar, PercQuant FROM tmp2;

[rhaber123]

Very helpful reports. Thank you