Income Based on Catagory of Dental Services

[Nate]

I am still new to creating querys and could not locate an example one close enough to modify for what I need. I would like to find out which services my income is coming from. Can a query be made that will provide the percentage of my total fees received from the following categories of services:

Hygiene Services
Operative
Crown & Bridge
Endo
Perio
Ortho
TMJ
Implant
Surgery
Pedo
Removable
Miscellaneous

Thanks for any help with this.

[Jorgebon]

This is a very complicated query, but fortunately there is a query in the examples page (query number 45) that could work. Set the desired date range in the first line:

SET @FromDate= '2007-08-21', @ToDate='2007-08-22'; /*change dates here*/
SET @TotNum=0, @TotFee=0; DROP TABLE IF EXISTS tmp1; DROP TABLE IF EXISTS tmp2;
/*Create 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;
DROP TABLE IF EXISTS tmp1; DROP TABLE IF EXISTS tmp2

[jclaydds]

If you do not find a query that will give you the desired information, here are a couple of other options. If you are familiar with Microsoft Excel, you could likely get the data you are looking for. Go to Reports, then Daily Reports, then Procedures and enter the date range. Under, type, be sure and ask that the report be "grouped by procedure code". After you run the report, use the export tab to save it in a file using the .xls (Excel) format. The file will be located in the "Open Dental Exports" directory on you hard drive. Open it using Excel and then, depending on your familiarity with Excel, it should be fairly easy for you to get the information you desire.

A second option would also work but may take a bit more effort. In the same report (Daily Procedures), there is a box that gives you the ability to filter your results. The box is labeled " only procedures similar to". If you want to run the report for specific categories of treatment and only get endodontic procedures for example, enter D3 in the box which is the first two characters of all the endodontic codes. If you enter D5, you will get all the removable prosthetic codes, etc. Remember the data is case sensitive so enter "D" not "d".

Hopefully this will help if the suggested query doesn't do the trick.

[nathansparks]

A little more on this topic:

There is no income based on category of dental services unless you split patient payments to procedures, which is difficult. The query shown is great (if I do say so myself) but only applies to production (expected revenue, whether collected or not). Income (revenue) is not always tied to a procedure. If we had a set of assumptions (First IN First out is a start) then we could apply income to procedures, but when two are done the same day, with dif provider and the patient pays half of what they owe, what do we do? To which procedures should an adjustment apply? I mean these questions can all be answered, but there then would be a set of assumptions upon which the report was based.