I'll see what I can whip up. I think I can come up with something for you.mikebarrdds wrote:At the end of this month, for example, I want to compare my metrics for the same month in the previous year. Compare this July's numbers to last July's numbers. And, then I also compare YTD for this year compared to the same period last year.
I believe (but not sure) that it's based on cancelled / no show appts. I will say that metric wasn't important to me.KevinRossen wrote:In your previous software, how was the missed appointment production calculated? Was it based on empty time on the schedule? Or did the software track the production for cancelled/no show appointments?
Ok, I assumed that was the case. I've got a query for you. I really like the report, so I'll use it for our office, too. Make a lot of sense to look at things this way.mikebarrdds wrote: I believe (but not sure) that it's based on cancelled / no show appts. I will say that metric wasn't important to me.
The main things I like to compare are (obviously) production and collections. Also new patients, # of patients seen.
It helps me see how I'm doing this year compared to last year. What is the trend?
Code: Select all
/* Period 1 */
SET @FromDate1='2013-06-01',
@ToDate1='2013-06-30',
@ProdGoal1=200000;
/* Period 2 */
SET @FromDate2='2014-06-01',
@ToDate2='2014-06-30',
@ProdGoal2=200000;
/* Table for Period 1 */
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
Period VARCHAR(35),
PTsSeen double NOT NULL DEFAULT 0,
NPsSeen double NOT NULL DEFAULT 0,
NPPct VARCHAR(6),
GrossProd double NOT NULL DEFAULT 0,
ProdGoal double NOT NULL DEFAULT 0,
ProdGoalPct VARCHAR(6),
EstHours double NOT NULL DEFAULT 0,
GrossPerHour double NOT NULL DEFAULT 0,
GrossPerPt double NOT NULL DEFAULT 0,
NetProd double NOT NULL DEFAULT 0,
NetPerPt double NOT NULL DEFAULT 0,
NetPerHour double NOT NULL DEFAULT 0,
Collection double NOT NULL DEFAULT 0,
CollectRatio VARCHAR(8),
PatCollect double NOT NULL DEFAULT 0,
InsCollect double NOT NULL DEFAULT 0);
/* Select Period 1 Dates */
INSERT INTO t1(Period)
SELECT CONCAT(@FromDate1,' through ',@ToDate1) AS 'Period';
/* Count Patients Seen */
DROP TABLE IF EXISTS t2;
CREATE TABLE t2
SELECT COUNT(DISTINCT PatNum) AS 'PTsSeen'
FROM procedurelog
WHERE ProcDate BETWEEN @FromDate1 AND @ToDate1 AND ProcStatus=2;
UPDATE t1,t2 SET t1.PTsSeen=t2.PTsSeen;
DROP TABLE IF EXISTS t2;
/* Count New Patients Seen */
CREATE TABLE t2
SELECT COUNT(DISTINCT p.PatNum) AS 'NPsSeen'
FROM patient p LEFT JOIN procedurelog pl ON p.PatNum=pl.PatNum
WHERE (pl.ProcDate BETWEEN @FromDate1 AND @ToDate1 AND pl.ProcStatus=2) AND p.DateFirstVisit BETWEEN @FromDate1 AND @ToDate1;
UPDATE t1,t2 SET t1.NPsSeen=t2.NPsSeen;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET NPPct=CONCAT(ROUND((NPsSeen/PTsSeen*100),1),'%');
/*Gross Prod*/
CREATE TABLE t2
SELECT SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) AS 'GrossProd'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND DATE(pl.ProcDate) BETWEEN @FromDate1 AND @ToDate1;
UPDATE t1,t2 SET t1.GrossProd=t2.GrossProd;
UPDATE t1 SET ProdGoal=@ProdGoal1;
UPDATE t1 SET ProdGoalPct=CONCAT(ROUND((GrossProd/ProdGoal*100),1),'%');
UPDATE t1 SET GrossPerPt=ROUND(GrossProd/PTsSeen,2);
DROP TABLE IF EXISTS t2;
/*Scheduled Hours*/
CREATE TABLE t2
SELECT ROUND(SUM(TIME_TO_SEC(TIMEDIFF(s.StopTime, s.StartTime)))/(60*60),1) AS 'EstHours'
FROM schedule s
WHERE s.SchedDate BETWEEN @FromDate1 AND @ToDate1 AND SchedType = 1 AND s.SchedDate < CURDATE();
UPDATE t1,t2 SET t1.EstHours=t2.EstHours;
UPDATE t1 SET GrossPerHour=ROUND(GrossProd/EstHours,2);
DROP TABLE IF EXISTS t2;
/*Net Production*/
CREATE TABLE t2(
Adjustments double NOT NULL DEFAULT 0,
WriteOff double NOT NULL DEFAULT 0);
INSERT INTO t2(Adjustments)
SELECT SUM(AdjAmt) AS 'Adjustments'
FROM adjustment WHERE AdjDate BETWEEN @FromDate1 AND @ToDate1;
DROP TABLE IF EXISTS t3;
CREATE TABLE t3
SELECT SUM(WriteOff) AS 'WriteOff'
FROM claimproc WHERE (Status=1 OR Status=4 OR Status=0) AND ProcDate BETWEEN @FromDate1 AND @ToDate1;
UPDATE t2,t3 SET t2.WriteOff=t3.WriteOff;
DROP TABLE IF EXISTS t3;
UPDATE t1,t2 SET t1.NetProd=ROUND(t1.GrossProd+t2.Adjustments-IFNULL(t2.WriteOff,0),2);
UPDATE t1 SET NetPerHour=ROUND(NetProd/EstHours,2);
UPDATE t1 SET NetPerPt=ROUND(NetProd/PTsSeen,2);
DROP TABLE IF EXISTS t2;
/*Collection*/
CREATE TABLE t2(
Month int NOT NULL,
PatCollect double NOT NULL DEFAULT 0,
InsCollect double NOT NULL DEFAULT 0);
INSERT INTO t2(PatCollect)
SELECT ROUND(SUM(SplitAmt),2) AS 'PatCollect'
FROM paysplit WHERE DatePay BETWEEN @FromDate1 AND @ToDate1;
DROP TABLE IF EXISTS t3;
CREATE TABLE t3
SELECT ROUND(SUM(cp.InsPayAmt),2) AS 'InsCollect'
FROM claimproc cp INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE cpay.CheckDate BETWEEN @FromDate1 AND @ToDate1 AND cp.Status IN(1,4);
UPDATE t2,t3 SET t2.InsCollect=t3.InsCollect;
DROP TABLE IF EXISTS t3;
UPDATE t1,t2 SET t1.Collection=(t2.InsCollect+t2.PatCollect);
UPDATE t1,t2 SET t1.PatCollect=t2.PatCollect;
UPDATE t1,t2 SET t1.InsCollect=t2.InsCollect;
DROP TABLE IF EXISTS t2;
UPDATE t1 SET CollectRatio=CONCAT(ROUND((Collection/NetProd*100),1),'%');
/* Table for Period 2 */
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(
Period VARCHAR(35),
PTsSeen double NOT NULL DEFAULT 0,
NPsSeen double NOT NULL DEFAULT 0,
NPPct VARCHAR(6),
GrossProd double NOT NULL DEFAULT 0,
ProdGoal double NOT NULL DEFAULT 0,
ProdGoalPct VARCHAR(6),
EstHours double NOT NULL DEFAULT 0,
GrossPerHour double NOT NULL DEFAULT 0,
GrossPerPt double NOT NULL DEFAULT 0,
NetProd double NOT NULL DEFAULT 0,
NetPerPt double NOT NULL DEFAULT 0,
NetPerHour double NOT NULL DEFAULT 0,
Collection double NOT NULL DEFAULT 0,
CollectRatio VARCHAR(8),
PatCollect double NOT NULL DEFAULT 0,
InsCollect double NOT NULL DEFAULT 0);
/* Select Period 1 Dates */
INSERT INTO t2(Period)
SELECT CONCAT(@FromDate2,' through ',@ToDate2) AS 'Period';
/* Count Patients Seen */
DROP TABLE IF EXISTS t3;
CREATE TABLE t3
SELECT COUNT(DISTINCT PatNum) AS 'PTsSeen'
FROM procedurelog
WHERE ProcDate BETWEEN @FromDate2 AND @ToDate2 AND ProcStatus=2;
UPDATE t2,t3 SET t2.PTsSeen=t3.PTsSeen;
DROP TABLE IF EXISTS t3;
/* Count New Patients Seen */
CREATE TABLE t3
SELECT COUNT(DISTINCT p.PatNum) AS 'NPsSeen'
FROM patient p LEFT JOIN procedurelog pl ON p.PatNum=pl.PatNum
WHERE (pl.ProcDate BETWEEN @FromDate2 AND @ToDate2 AND pl.ProcStatus=2) AND p.DateFirstVisit BETWEEN @FromDate2 AND @ToDate2;
UPDATE t2,t3 SET t2.NPsSeen=t3.NPsSeen;
DROP TABLE IF EXISTS t3;
UPDATE t2 SET NPPct=CONCAT(ROUND((NPsSeen/PTsSeen*100),1),'%');
/*Gross Prod*/
CREATE TABLE t3
SELECT SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) AS 'GrossProd'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND DATE(pl.ProcDate) BETWEEN @FromDate2 AND @ToDate2;
UPDATE t2,t3 SET t2.GrossProd=t3.GrossProd;
UPDATE t2 SET ProdGoal=@ProdGoal1;
UPDATE t2 SET ProdGoalPct=CONCAT(ROUND((GrossProd/ProdGoal*100),1),'%');
UPDATE t2 SET GrossPerPt=ROUND(GrossProd/PTsSeen,2);
DROP TABLE IF EXISTS t3;
/*Scheduled Hours*/
CREATE TABLE t3
SELECT ROUND(SUM(TIME_TO_SEC(TIMEDIFF(s.StopTime, s.StartTime)))/(60*60),1) AS 'EstHours'
FROM schedule s
WHERE s.SchedDate BETWEEN @FromDate2 AND @ToDate2 AND SchedType = 1 AND s.SchedDate < CURDATE();
UPDATE t2,t3 SET t2.EstHours=t3.EstHours;
UPDATE t2 SET GrossPerHour=ROUND(GrossProd/EstHours,2);
DROP TABLE IF EXISTS t3;
/*Net Production*/
CREATE TABLE t3(
Adjustments double NOT NULL DEFAULT 0,
WriteOff double NOT NULL DEFAULT 0);
INSERT INTO t3(Adjustments)
SELECT SUM(AdjAmt) AS 'Adjustments'
FROM adjustment WHERE AdjDate BETWEEN @FromDate2 AND @ToDate2;
DROP TABLE IF EXISTS t4;
CREATE TABLE t4
SELECT SUM(WriteOff) AS 'WriteOff'
FROM claimproc WHERE (Status=1 OR Status=4 OR Status=0) AND ProcDate BETWEEN @FromDate2 AND @ToDate2;
UPDATE t3,t4 SET t3.WriteOff=t4.WriteOff;
DROP TABLE IF EXISTS t4;
UPDATE t2,t3 SET t2.NetProd=ROUND(t2.GrossProd+t3.Adjustments-IFNULL(t3.WriteOff,0),2);
UPDATE t2 SET NetPerHour=ROUND(NetProd/EstHours,2);
UPDATE t2 SET NetPerPt=ROUND(NetProd/PTsSeen,2);
DROP TABLE IF EXISTS t3;
/*Collection*/
CREATE TABLE t3(
Month int NOT NULL,
PatCollect double NOT NULL DEFAULT 0,
InsCollect double NOT NULL DEFAULT 0);
INSERT INTO t3(PatCollect)
SELECT ROUND(SUM(SplitAmt),2) AS 'PatCollect'
FROM paysplit WHERE DatePay BETWEEN @FromDate2 AND @ToDate2;
DROP TABLE IF EXISTS t4;
CREATE TABLE t4
SELECT ROUND(SUM(cp.InsPayAmt),2) AS 'InsCollect'
FROM claimproc cp INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE cpay.CheckDate BETWEEN @FromDate2 AND @ToDate2 AND cp.Status IN(1,4);
UPDATE t3,t4 SET t3.InsCollect=t4.InsCollect;
DROP TABLE IF EXISTS t4;
UPDATE t2,t3 SET t2.Collection=(t3.InsCollect+t3.PatCollect);
UPDATE t2,t3 SET t2.PatCollect=t3.PatCollect;
UPDATE t2,t3 SET t2.InsCollect=t3.InsCollect;
DROP TABLE IF EXISTS t3;
UPDATE t2 SET CollectRatio=CONCAT(ROUND((Collection/NetProd*100),1),'%');
/* Build Report */
SELECT * FROM t1
UNION
SELECT * FROM t2
UNION
SELECT 'Change',
CONCAT(ROUND(((t2.PTsSeen-t1.PTsSeen)/t1.PTsSeen*100),1),'%'),
CONCAT(ROUND(((t2.NPsSeen-t1.NPsSeen)/t1.NPsSeen*100),1),'%'),
'',
CONCAT(ROUND(((t2.GrossProd-t1.GrossProd)/t1.GrossProd*100),1),'%'),
'',
'',
'',
CONCAT(ROUND(((t2.GrossPerHour-t1.GrossPerHour)/t1.GrossPerHour*100),1),'%'),
CONCAT(ROUND(((t2.GrossPerPt-t1.GrossPerPt)/t1.GrossPerPt*100),1),'%'),
CONCAT(ROUND(((t2.NetProd-t1.NetProd)/t1.NetProd*100),1),'%'),
CONCAT(ROUND(((t2.NetPerPt-t1.NetPerPt)/t1.NetPerPt*100),1),'%'),
CONCAT(ROUND(((t2.NetPerHour-t1.NetPerHour)/t1.NetPerHour*100),1),'%'),
CONCAT(ROUND(((t2.Collection-t1.Collection)/t1.Collection*100),1),'%'),
'',
CONCAT(ROUND(((t2.PatCollect-t1.PatCollect)/t1.PatCollect*100),1),'%'),
CONCAT(ROUND(((t2.InsCollect-t1.InsCollect)/t1.InsCollect*100),1),'%')
FROM t1,t2;
DROP TABLE IF EXISTS t1,t2;
KevinRossen wrote:Like this?
I have it setup for our office and my beta testers to be automatically emailed at the end of every day. I've got quite a bit going on in the backend outside of Open Dental.
Thanks, Steve. I've heard of YAPI. But, I'm really not interested in adding more software (that costs more than my PMS). I don't need or use intra-office messaging (OD does that anyway). My practice simply isn't that big. As for patient communications, I might be interested in that in the foreseeable future. But, from what I've seen, YAPI doesn't do that nearly as well as systems like Lighthouse, for example.spolevoy wrote:Hey Mike,
Not to divert the conversation, but have you looked at addding mopsy's YAPI?
It's an intra-office messaging, appointment reminder, recall management, patient communication system that also does metrics
At the very top change each of these:mikebarrdds wrote:How do I change the date ranges? Is that done in the query code?
It's a combination of running MySQL queries from batch scripts and HIGHLY customized excel spreadsheets/charts. I don't have to manually interact with it. My setup emails it to me at 6:45 every day. Check the link in my signature for a few more details.Jay wrote:How do you generate this kind of report?
KevinRossen wrote:Like this?
I have it setup for our office and my beta testers to be automatically emailed at the end of every day. I've got quite a bit going on in the backend outside of Open Dental.