Question on Building an advanced metrics query

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Question on Building an advanced metrics query

Post by KevinRossen » Wed Dec 18, 2013 11:23 am

Hi Everyone,

We are recent converts to Open Dental (as of Nov 1, 2013). I had a report that I made in our old software PracticeWorks that pulled together a number of different types of data to create one nice report with a bunch of different metrics that I like to track each year.

Here's what the finished report looked like from PW
And here's what the data page looks like.

I've started with query example #237 to start with. I am pretty sure I'll need to build temporary table using MySQL in the query, which I'm learning how to do. I just want to make sure I'm on the right track before I invest much more time building this report.

Any tips to help me get over the initial learning curve?
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Question on Building an advanced metrics query

Post by jsalmon » Fri Dec 20, 2013 11:16 pm

Good luck. Typically when people try to make "one query to rule them all" they get so complicated that the data results never really seem to be quite right. If you ever need help trying to figure out where to grab data from or what tables to join together, I'll be able to help you in that aspect. Otherwise, I hope you get the end results that you're looking for!
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Question on Building an advanced metrics query

Post by KevinRossen » Mon Dec 23, 2013 7:43 am

jsalmon wrote:Good luck. Typically when people try to make "one query to rule them all" they get so complicated that the data results never really seem to be quite right. If you ever need help trying to figure out where to grab data from or what tables to join together, I'll be able to help you in that aspect. Otherwise, I hope you get the end results that you're looking for!
I'm getting the feeling that it would be easier to create multiple queries to do what I want and then pull them together into a spreadsheet separately. I'm starting to think that I need to separate the queries into the type of data I'm compiling: 1) production numbers; 2) procedure counts; and 3) patient/appointment counts.

Does that seem simpler?
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Question on Building an advanced metrics query

Post by jsalmon » Mon Dec 23, 2013 9:01 am

That always seems to be the simple route when wanting seemingly unrelated data to show all at once.

We offer a service here at HQ for writing all kinds of complex queries for $90 an hour. You can always give us a call and put in a query request and get a quote on how hard our query department deems your request. Lots of our users utilize this service we provide, hence why our query examples page is so vast.
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Question on Building an advanced metrics query

Post by KevinRossen » Mon Dec 23, 2013 10:02 am

jsalmon wrote:That always seems to be the simple route when wanting seemingly unrelated data to show all at once.

We offer a service here at HQ for writing all kinds of complex queries for $90 an hour. You can always give us a call and put in a query request and get a quote on how hard our query department deems your request. Lots of our users utilize this service we provide, hence why our query examples page is so vast.
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
I will probably end up doing that, but I'm enjoying the learning process of figuring out how MySQL queries work. Opening up lots of ideas.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: Question on Building an advanced metrics query

Post by Hersheydmd » Fri Dec 27, 2013 6:37 am

Kevin, that is a really awesome report. Tremendous amount of information all in one place.
What do you do with all that information?
BTW, have you looked at the graphical dashboard in OD? It has everything I'm looking for. I find it easier to look at than a spreadsheet of numbers. It shows you trends.
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Question on Building an advanced metrics query

Post by KevinRossen » Fri Jan 03, 2014 7:47 am

Hersheydmd wrote:Kevin, that is a really awesome report. Tremendous amount of information all in one place.
What do you do with all that information?
BTW, have you looked at the graphical dashboard in OD? It has everything I'm looking for. I find it easier to look at than a spreadsheet of numbers. It shows you trends.
One of the things I look for in the data is trends. If we are doing less of the elective hygiene procedures (sealants, fl, etc) it might be that the hygiene team is not talking about them with the patients. I also like to know what makes one month's production higher than another.

As far as the dashboard goes, I do like it as a simple quick view. Just looking for more comprehensive info.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Question on Building an advanced metrics query

Post by KevinRossen » Mon Feb 03, 2014 10:41 pm

Here's an update. I was able to pour through a bunch of different resources over the past couple months and figure out a lot of how MySQL queries work. I was able to build a query that gives me exactly what I'm looking for. The report for my practice is very specific to us, but I figured the main idea would be helpful to others out there. So, here is the User Query that I came up with. It's designed for a single dentist practice with a hygiene department. Let me know if you have any questions.

Below are all 164 lines of code:

Code: Select all

/* 12 month practice metrics for a one dentist practice. Set date range and Dentist's ProvNum. */
SET @FromDate='2013-01-01' , @ToDate='2013-12-31';
SET @ProvNum='4';
DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1( 
Month CHAR(10) NOT NULL,
Days int NOT NULL,
Hours int NOT NULL,
$Production double NOT NULL,
$Collection double NOT NULL,
CPct CHAR(7) NOT NULL,
$DrProd double NOT NULL,
$HygProd double NOT NULL,
HygPct CHAR(7) NOT NULL,
NPs double NOT NULL,
$ProdDay double NOT NULL,
$DRDay double NOT NULL,
$HygDay double NOT NULL,
NPDay double NOT NULL,
$ProdHr double NOT NULL,
$DRHr double NOT NULL,
$HygHr double NOT NULL);
/*Load Months & Days*/
INSERT INTO t1(Month,Days)
SELECT MONTH(pl.ProcDate) AS 'Month', COUNT(DISTINCT pl.ProcDate) AS 'Days'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(pl.ProcDate);
/*Scheduled Hours*/
CREATE TABLE t2
SELECT MONTH(s.SchedDate) AS 'Month', ROUND(SUM(TIME_TO_SEC(TIMEDIFF(s.StopTime, s.StartTime)))/(60*60),1) AS 'Hours'
FROM schedule s
WHERE s.SchedDate BETWEEN @FromDate AND @ToDate AND SchedType = 1 AND s.SchedDate < CURDATE()
GROUP BY MONTH(s.SchedDate);
UPDATE t1,t2 SET t1.Hours=t2.Hours WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/*Net Production*/
CREATE TABLE t2( 
Month int NOT NULL,
$Production double NOT NULL DEFAULT 0,
$Adjustments double NOT NULL DEFAULT 0,
$WriteOff double NOT NULL DEFAULT 0,
$TotProd double NOT NULL DEFAULT 0);
INSERT INTO t2(Month,$Production)
SELECT MONTH(pl.ProcDate) AS 'Month', SUM(pl.procfee) AS '$Production'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(pl.ProcDate);
/*Adj*/
CREATE TABLE t3
SELECT MONTH(a.AdjDate) AS 'Month',
SUM(a.AdjAmt) AS 'Adjustments' FROM adjustment a 
WHERE a.AdjDate BETWEEN @FromDate AND @ToDate 
GROUP BY MONTH(a.AdjDate);
UPDATE t2,t3 SET t2.$Adjustments=t3.Adjustments WHERE t2.Month=t3.Month;
DROP TABLE IF EXISTS t3;
/*WriteOffs*/
CREATE TABLE t3
SELECT MONTH(cp.ProcDate) AS 'Month', SUM(cp.WriteOff) AS '$WriteOff'
FROM claimproc cp 
WHERE (cp.Status=1 OR cp.Status=@ProvNum OR cp.Status=0)  AND cp.ProcDate BETWEEN @FromDate AND @ToDate /*vs DateCP if not counting writeoffs by procdate*/
GROUP BY MONTH(cp.ProcDate);
UPDATE t2 LEFT JOIN t3 ON t2.Month=t3.Month
SET t2.$WriteOff=-t3.$WriteOff, t2.$TotProd=ROUND(t2.$Production+t2.$Adjustments-IFNULL(t3.$WriteOff,0),2);
DROP TABLE IF EXISTS t3;
UPDATE t1,t2 SET t1.$Production=t2.$TotProd WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/*Collection*/
CREATE TABLE t2( 
Month int NOT NULL,
$PatIncome double NOT NULL DEFAULT 0,
$InsIncome double NOT NULL DEFAULT 0);
/*PatInc*/
INSERT INTO t2(Month,$PatIncome)
SELECT MONTH(pp.DatePay) AS 'Month', ROUND(SUM(pp.SplitAmt),2) AS '$PatIncome' 
FROM paysplit pp WHERE pp.DatePay BETWEEN @FromDate AND @ToDate 
GROUP BY MONTH(pp.DatePay);
/*InsIncome*/
CREATE TABLE t3
SELECT MONTH(cpay.CheckDate) AS 'Month', ROUND(SUM(cp.InsPayAmt),2) AS '$InsIncome'
FROM claimproc cp INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE cpay.CheckDate BETWEEN @FromDate AND @ToDate AND cp.Status IN(1,4) 
GROUP BY MONTH(cpay.CheckDate);
UPDATE t2,t3 SET t2.$InsIncome=t3.$InsIncome WHERE t2.Month=t3.Month;
DROP TABLE IF EXISTS t3;
UPDATE t1,t2 SET t1.$Collection=(t2.$InsIncome+t2.$PatIncome) WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET CPct=CONCAT(ROUND(($Collection/$Production*100),1),'%');
/*DR Production*/
CREATE TABLE t2( 
Month int NOT NULL,
$Production double NOT NULL DEFAULT 0,
$Adjustments double NOT NULL DEFAULT 0,
$WriteOff double NOT NULL DEFAULT 0,
$TotProd double NOT NULL DEFAULT 0);
INSERT INTO t2(Month,$Production)
SELECT MONTH(pl.ProcDate) AS 'Month', SUM(pl.procfee) AS '$Production'
FROM procedurelog pl WHERE pl.ProvNum=@ProvNum AND pl.ProcStatus=2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(pl.ProcDate);
/*DR Adj*/
CREATE TABLE t3
SELECT MONTH(a.AdjDate) AS 'Month', SUM(a.AdjAmt) AS 'Adjustments' 
FROM adjustment a WHERE a.ProvNum=@ProvNum AND a.AdjDate BETWEEN @FromDate AND @ToDate 
GROUP BY MONTH(a.AdjDate);
UPDATE t2,t3 SET t2.$Adjustments=t3.Adjustments WHERE t2.Month=t3.Month;
DROP TABLE IF EXISTS t3;
/*DR WriteOffs*/
CREATE TABLE t3
SELECT MONTH(cp.ProcDate) AS 'Month', SUM(cp.WriteOff) AS '$WriteOff'
FROM claimproc cp 
WHERE cp.ProvNum=@ProvNum AND (cp.Status=1 OR cp.Status=@ProvNum OR cp.Status=0)  AND cp.ProcDate BETWEEN @FromDate AND @ToDate /*vs DateCP if not counting writeoffs by procdate*/
GROUP BY MONTH(cp.ProcDate);
UPDATE t2 LEFT JOIN t3 ON t2.Month=t3.Month
SET t2.$WriteOff=-t3.$WriteOff, t2.$TotProd=ROUND(t2.$Production+t2.$Adjustments-IFNULL(t3.$WriteOff,0),2);
DROP TABLE IF EXISTS t3;
UPDATE t1,t2 SET t1.$DRProd=t2.$TotProd WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/*Hyg Production*/
CREATE TABLE t2( 
Month int NOT NULL,
$Production double NOT NULL DEFAULT 0,
$Adjustments double NOT NULL DEFAULT 0,
$WriteOff double NOT NULL DEFAULT 0,
$TotProd double NOT NULL DEFAULT 0);
INSERT INTO t2(Month,$Production)
SELECT MONTH(pl.ProcDate) AS 'Month', SUM(pl.procfee) AS '$Production'
FROM procedurelog pl WHERE pl.ProvNum<>@ProvNum AND pl.ProcStatus=2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(pl.ProcDate);
/*Hyg Adj*/
CREATE TABLE t3
SELECT MONTH(a.AdjDate) AS 'Month', SUM(a.AdjAmt) AS 'Adjustments' 
FROM adjustment a WHERE a.ProvNum<>@ProvNum AND a.AdjDate BETWEEN @FromDate AND @ToDate 
GROUP BY MONTH(a.AdjDate);
UPDATE t2,t3 SET t2.$Adjustments=t3.Adjustments WHERE t2.Month=t3.Month;
DROP TABLE IF EXISTS t3;
/*Hyg WriteOffs*/
CREATE TABLE t3
SELECT MONTH(cp.ProcDate) AS 'Month', SUM(cp.WriteOff) AS '$WriteOff'
FROM claimproc cp 
WHERE cp.ProvNum<>@ProvNum AND (cp.Status=1 OR cp.Status=@ProvNum OR cp.Status=0)  AND cp.ProcDate BETWEEN @FromDate AND @ToDate /*vs DateCP if not counting writeoffs by procdate*/
GROUP BY MONTH(cp.ProcDate);
UPDATE t2 LEFT JOIN t3 ON t2.Month=t3.Month
SET t2.$WriteOff=-t3.$WriteOff, t2.$TotProd=ROUND(t2.$Production+t2.$Adjustments-IFNULL(t3.$WriteOff,0),2);
DROP TABLE IF EXISTS t3;
UPDATE t1,t2 SET t1.$HygProd=t2.$TotProd WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET HygPct=CONCAT(ROUND(($HygProd/$DRProd*100),1),'%');
/*NP Count*/
CREATE TABLE t2
SELECT MONTH(p.DateFirstVisit) AS 'Month', COUNT(DISTINCT p.PatNum) AS 'NPs'
FROM patient p 
INNER JOIN procedurelog pl on pl.PatNum=p.PatNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND p.PatStatus=0 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pl.ProcFee > 0
GROUP BY MONTH(p.DateFirstVisit);
UPDATE t1,t2 SET t1.NPs=t2.NPs WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET $ProdDay=ROUND($Production/Days,2);
UPDATE t1 SET $DRDay=ROUND($DRProd/Days,2);
UPDATE t1 SET $HygDay=ROUND($HygProd/Days,2);
UPDATE t1 SET NPDay=ROUND(NPs/Days,1);
UPDATE t1 SET $ProdHr=ROUND($Production/Hours,2);
UPDATE t1 SET $DRHr=ROUND($DRProd/Hours,2);
UPDATE t1 SET $HygHr=ROUND($HygProd/Hours,2);
SELECT * FROM t1;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Question on Building an advanced metrics query

Post by Tom Zaccaria » Tue Feb 04, 2014 4:31 pm

Did run this query on our results for 2013.
Problem is $DrProd, $DRDay and $DRHr columns were all '0' .
It basically gives all the production and collections to hygiene.

Is it something in the query or the way we have things coded internally?

I am able to differentiate dr and hyg production in other queries.

drtmz

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Question on Building an advanced metrics query

Post by KevinRossen » Tue Feb 04, 2014 8:00 pm

Tom Zaccaria wrote:Did run this query on our results for 2013.
Problem is $DrProd, $DRDay and $DRHr columns were all '0' .
It basically gives all the production and collections to hygiene.

Is it something in the query or the way we have things coded internally?

I am able to differentiate dr and hyg production in other queries.

drtmz
The way I have it setup you'll have to know the ProvNum for the dr. You can get it by running SELECT * FROM provider

That said, I know I need to tweak a few things. I'd like to make it not dependent on the ProvNum. Also, there's something wrong with how I'm calculating hours. I guess that's what happens when I work on MySQL after midnight.

I'll post updates as I find them. I'd appreciate any feedback or suggestions.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Question on Building an advanced metrics query

Post by Tom Zaccaria » Wed Feb 05, 2014 2:55 am

Don't feel too bad. After running the query I was amazed to find out how close 'your' numbers were to ours for 2013.
Took me at least ten minutes to realize that the numbers were from our results.
Color me 'stupid'.

drtmz

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Question on Building an advanced metrics query

Post by tgriswold » Wed Feb 05, 2014 5:00 pm

This query is a little weird, but it lets you set the ProvNum into a stored variable if you know the provider abbreviation. You could then use it just as if you had set it manually at the top. Just saves you some time from either doing a quick select statement to find out the provider's ProvNum before you run the query, or doing some joins inside the query to use the abbreviation instead of the provider number. I haven't personally used this technique a lot but it seems to work just fine.

Code: Select all

SET @ProvAbbr='Travis';
SET @ProvNum=(SELECT ProvNum FROM provider WHERE provider.Abbr=@ProvAbbr LIMIT 1);
~~~~~Insert normal query here~~~~~~~
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply