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.
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;
