Morning Huddle Report

For users or potential users.
Post Reply
KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Morning Huddle Report

Post by KevinRossen » Tue Feb 25, 2014 8:43 am

After fine-tuning and tweaking various reports, I've finally put together a report that I'm very happy with for our morning huddles. It lists the current day's scheduled appointments with the following info: Op, Apt Time, shows "NP" if they're a new patient, the patient's name, Age, the family's balance (after estimated insurance), the procedures that are part of that day's appointments, and any unscheduled treatment planned procedures in the patient's chart.

The only thing that would make this report better, in my opinion, would be if I could figure out how to set it for a future date (the next day, for example), but I can only get it to work for the current date. The nice thing about how it's setup is that you never have to change the date. Just pull it up in the morning, click print, and you're ready to go.

Here's the query:

Code: Select all

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(Op VARCHAR(4) NOT NULL,Time VARCHAR(8), NP VARCHAR(3), PatNum VARCHAR(25) NOT NULL,Age VARCHAR(3) NOT NULL,$FamBal double NOT NULL DEFAULT 0,TodaysProcs VARCHAR(40),UnschedProcs VARCHAR(40));
INSERT INTO t1(Op,Time,NP,PatNum,Age,TodaysProcs)
SELECT op.Abbrev AS 'Op', TIME_FORMAT(a.AptDateTime, '%l:%i %p') AS 'Time',  IF(DATE(p.DateFirstVisit)=CURDATE(),'NP','') AS 'NP', p.PatNum AS 'PatNum', (CASE WHEN (YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5))<200 THEN(YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5)) ELSE 0 END) AS 'Age', GROUP_CONCAT(pc.AbbrDesc) AS 'TodaysProcs'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
LEFT JOIN operatory op ON op.OperatoryNum=a.Op
LEFT JOIN procedurelog pl ON pl.AptNum=a.AptNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE DATE(a.AptDateTime) = CURDATE() AND AptStatus=1
GROUP BY a.AptNum ORDER BY op.OpName,a.AptDateTime;
CREATE TABLE t2
SELECT pt.PatNum AS 'PatNum', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN appointment a ON A.PatNum=pl.PatNum
WHERE (pl.ProcStatus = '1' AND pl.AptNum = '0' AND ProcFee > 100) AND (DATE(a.AptDateTime) = CURDATE() AND a.AptStatus=1) GROUP BY pl.PatNum;
UPDATE t1,t2 SET t1.UnschedProcs=t2.Procs WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2
SELECT a.PatNum AS 'PatNum', SUM(p2.BalTotal-p2.InsEst) AS '$FamBal'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient p2 ON p.Guarantor=p2.PatNum
WHERE DATE(a.AptDateTime) = CURDATE() AND a.AptStatus=1 GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.$FamBal=t2.$FamBal WHERE t1.PatNum=t2.PatNum;
SELECT * FROM t1;
DROP TABLE IF EXISTS t1,t2;
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: Morning Huddle Report

Post by jsalmon » Tue Feb 25, 2014 8:48 am

KevinRossen wrote:...would be if I could figure out how to set it for a future date (the next day, for example)
MySQL's ADDDATE method is pretty sweet. I bet you could use it:
http://dev.mysql.com/doc/refman/5.5/en/ ... on_adddate
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: Morning Huddle Report

Post by KevinRossen » Tue Feb 25, 2014 9:19 am

jsalmon wrote:
KevinRossen wrote:...would be if I could figure out how to set it for a future date (the next day, for example)
MySQL's ADDDATE method is pretty sweet. I bet you could use it:
http://dev.mysql.com/doc/refman/5.5/en/ ... on_adddate
I started trying out using the SET @FromDate=2014-02-26 formula, but I kept getting empty results. I'm sure I can get it working for the next day, but I figured I'd share what I have now.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

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

Re: Morning Huddle Report

Post by Tom Zaccaria » Tue Feb 25, 2014 10:17 am

I changed this line,

WHERE DATE(a.AptDateTime) = CURDATE()+1 AND AptStatus=1

and it worked for tomorrow.

drtmz

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

Re: Morning Huddle Report

Post by KevinRossen » Tue Feb 25, 2014 11:08 am

Tom Zaccaria wrote:I changed this line,

WHERE DATE(a.AptDateTime) = CURDATE()+1 AND AptStatus=1

and it worked for tomorrow.

drtmz
Ah, thanks. That actually helped me reevaluate my code. I left out the quotations around the date when I was using the SET function. I've got the updated code below still using the current date as the default, but with instructions on setting a specific date.

Code: Select all

SET @ScheduledDate=CURDATE(); /* Or set specific date using: SET @ScheduledDate='2014-02-26'; */
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(Op VARCHAR(4) NOT NULL,Time VARCHAR(8), NP VARCHAR(3), PatNum VARCHAR(25) NOT NULL,Age VARCHAR(3) NOT NULL,$FamBal double NOT NULL DEFAULT 0,TodaysProcs VARCHAR(40),UnschedProcs VARCHAR(40));
INSERT INTO t1(Op,Time,NP,PatNum,Age,TodaysProcs)
SELECT op.Abbrev AS 'Op', TIME_FORMAT(a.AptDateTime, '%l:%i %p') AS 'Time',  IF(DATE(p.DateFirstVisit)=@ScheduledDate,'NP','') AS 'NP', p.PatNum AS 'PatNum', (CASE WHEN (YEAR(@ScheduledDate)-YEAR(p.Birthdate)) - (RIGHT(@ScheduledDate,5)<RIGHT(p.Birthdate,5))<200 THEN(YEAR(@ScheduledDate)-YEAR(p.Birthdate)) - (RIGHT(@ScheduledDate,5)<RIGHT(p.Birthdate,5)) ELSE 0 END) AS 'Age', GROUP_CONCAT(pc.AbbrDesc) AS 'TodaysProcs'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
LEFT JOIN operatory op ON op.OperatoryNum=a.Op
LEFT JOIN procedurelog pl ON pl.AptNum=a.AptNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE DATE(a.AptDateTime) = @ScheduledDate AND AptStatus=1
GROUP BY a.AptNum ORDER BY op.OpName,a.AptDateTime;
CREATE TABLE t2
SELECT pt.PatNum AS 'PatNum', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN appointment a ON A.PatNum=pl.PatNum
WHERE (pl.ProcStatus = '1' AND pl.AptNum = '0' AND ProcFee > 100) AND (DATE(a.AptDateTime) = @ScheduledDate AND a.AptStatus=1) GROUP BY pl.PatNum;
UPDATE t1,t2 SET t1.UnschedProcs=t2.Procs WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2
SELECT a.PatNum AS 'PatNum', SUM(p2.BalTotal-p2.InsEst) AS '$FamBal'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient p2 ON p.Guarantor=p2.PatNum
WHERE DATE(a.AptDateTime) = @ScheduledDate AND a.AptStatus=1 GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.$FamBal=t2.$FamBal WHERE t1.PatNum=t2.PatNum;
SELECT * FROM t1;
DROP TABLE IF EXISTS t1,t2;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

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

Re: Morning Huddle Report

Post by Tom Zaccaria » Tue Feb 25, 2014 4:24 pm

You may be over-thinking the situation here. The only time the morning huddle information is useful is on the day of the huddle.
No need for a change of dates capacity as the information in the appointments may change between the time you print the report and the time of the actual huddle.

It is a very concise report.

drtmz

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

Re: Morning Huddle Report

Post by KevinRossen » Tue Feb 25, 2014 8:46 pm

Tom Zaccaria wrote:You may be over-thinking the situation here. The only time the morning huddle information is useful is on the day of the huddle.
No need for a change of dates capacity as the information in the appointments may change between the time you print the report and the time of the actual huddle.

It is a very concise report.

drtmz
I agree. I was mainly thinking of printing the next day's report. Or Monday morning on the previous Thursday/Friday.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

mikelb420
Posts: 7
Joined: Sun Aug 14, 2011 6:29 am

Re: Morning Huddle Report

Post by mikelb420 » Thu Jun 26, 2014 8:56 am

This is great! Is it possible to add a couple fields for email and wireless? Not the the actual but just a boolean or check mark that they have one on file. Helps to remind us to get those so our recall and reminders are automated. Also, it would be nice to show their birthday and if someone is overdue for hygiene (also could just be a check mark) Is it possible to add non-table data to this as well? Such as the days scheduled production totals and monthly totals for goal tracking. And since the goal is to get it all in one report, if adding received lab cases w/o appt and lab cases not received is doable? I have a feature request for this report but if someone figured it out already, that would be awesome!

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

Re: Morning Huddle Report

Post by KevinRossen » Thu Jun 26, 2014 9:11 am

mikelb420 wrote:This is great! Is it possible to add a couple fields for email and wireless? Not the the actual but just a boolean or check mark that they have one on file. Helps to remind us to get those so our recall and reminders are automated. Also, it would be nice to show their birthday and if someone is overdue for hygiene (also could just be a check mark) Is it possible to add non-table data to this as well? Such as the days scheduled production totals and monthly totals for goal tracking. And since the goal is to get it all in one report, if adding received lab cases w/o appt and lab cases not received is doable? I have a feature request for this report but if someone figured it out already, that would be awesome!
Well, I take the position that [almost] anything is possible with Open Dental / MySQL. Take a look at the link in my signature as I'm developing a service for automated reports. Right now I don't have a morning huddle report, but it's on my roadmap for my practice. I like getting my reports automatically emailed, which is what I've setup for myself and a few others. I also make sure I'm HIPAA compliant, so any file that has PHI is encrypted (requires password to open).

To answer your specific questions, everything you want could be pulled together in one report...but it'll take some MySQL-ninja skills. I'll see what I can do.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

mikelb420
Posts: 7
Joined: Sun Aug 14, 2011 6:29 am

Re: Morning Huddle Report

Post by mikelb420 » Thu Jun 26, 2014 11:37 am

That would be AWESOME! I have a feature request, but anything close to this will be a huge help until it gets incorporated.

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

Re: Morning Huddle Report

Post by KevinRossen » Thu Jun 26, 2014 2:24 pm

mikelb420 wrote:That would be AWESOME! I have a feature request, but anything close to this will be a huge help until it gets incorporated.
I haven't had a chance to make it work with lab cases or production details, but here's the morning huddle report with added fields of birthday, email check, wireless check, and if non-hygiene patients are due for propy or perio. Email and Cell output "No" when that field is missing from the patient.

Code: Select all

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(
Op VARCHAR(4) NOT NULL,
TIME VARCHAR(8), 
PatNum VARCHAR(25) NOT NULL,
NP VARCHAR(3), 
Recall VARCHAR(15),
Age VARCHAR(3) NOT NULL,
DOB VARCHAR(10),
Email VARCHAR(3),
Cell VARCHAR(3),
FamBal DOUBLE NOT NULL DEFAULT 0,
TodaysProcs VARCHAR(40),
UnschedProcs VARCHAR(40));
INSERT INTO t1(Op,TIME,NP,PatNum,Age,DOB,Email,Cell,TodaysProcs)
SELECT op.Abbrev AS 'Op', TIME_FORMAT(a.AptDateTime, '%l:%i %p') AS 'Time',  IF(DATE(p.DateFirstVisit)=CURDATE(),'NP','') AS 'NP', p.PatNum AS 'PatNum', (CASE WHEN (YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5))<200 THEN(YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5)) ELSE '' END) AS 'Age', p.Birthdate AS 'DOB', IF(p.Email='','No','') AS 'Email', IF(p.WirelessPhone='','No','') AS 'Cell', GROUP_CONCAT(pc.ProcCode) AS 'TodaysProcs'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
LEFT JOIN operatory op ON op.OperatoryNum=a.Op
LEFT JOIN procedurelog pl ON pl.AptNum=a.AptNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE DATE(a.AptDateTime) = CURDATE() AND AptStatus=1
GROUP BY a.AptNum ORDER BY op.OpName,a.AptDateTime;
/* Unscheduled Procs */
CREATE TABLE t2
SELECT pt.PatNum AS 'PatNum', GROUP_CONCAT(pc.ProcCode) AS 'Procs'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN appointment a ON A.PatNum=pl.PatNum
WHERE (pl.ProcStatus = '1' AND pl.AptNum = '0' AND ProcFee > 100) AND (DATE(a.AptDateTime) = CURDATE() AND a.AptStatus=1) GROUP BY pl.PatNum;
UPDATE t1,t2 SET t1.UnschedProcs=t2.Procs WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
/* Family Balance */
CREATE TABLE t2
SELECT a.PatNum AS 'PatNum', ROUND(SUM(p2.BalTotal-p2.InsEst),0) AS 'FamBal'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient p2 ON p.Guarantor=p2.PatNum
WHERE DATE(a.AptDateTime) = CURDATE() AND a.AptStatus=1 GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.FamBal=t2.FamBal WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
/* Recall Due? */
CREATE TABLE t2
SELECT a.PatNum, CONCAT(rt.Description, ' overdue') AS 'RecallDue'
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON r.RecallTypeNum=rt.RecallTypeNum
WHERE DATE(a.AptDateTime)=CURDATE() AND a.AptStatus=1 AND a.IsHygiene=0 AND DATE(r.DateDue)<=CURDATE() AND rt.Description IN ('Prophy','Perio')
GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.Recall=t2.RecallDue WHERE t1.PatNum=t2.PatNum;
/* Output Data */
SELECT * FROM t1;
DROP TABLE IF EXISTS t1,t2;
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: Morning Huddle Report

Post by KevinRossen » Wed Nov 12, 2014 11:29 am

I finally got back to updating this report. It now lists all patients for the day, their DOB, age, if they're due for recall (for non-hygiene appts), if email or cell are missing from their info (the EC column), their family's total balance, the scheulded procs for the day (with abbreviation instead of code), and any unscheduled procedures they have treatement planned (with a fee over $150).

Below the list of patients is the scheduled production for the day, followed by any appointments in the next five days that have not received a lab case yet. Lastly, there is a list of lab cases that need to be scheduled (or possibly attached to already completed appointments).

Let me know if you have any questions or have any feedback. Copy and paste the code below into Reports > User Query:

Code: Select all

DROP TABLE IF EXISTS t1,t2,t3,t4;
CREATE TABLE t1(
OpTime VARCHAR(12) NOT NULL,
PatNum VARCHAR(25),
Patient VARCHAR(25) NOT NULL,
DOB VARCHAR(10) NOT NULL,
Age VARCHAR(3) NOT NULL,
RecallDue VARCHAR(15) NOT NULL,
EC VARCHAR(3) NOT NULL,
FamBal VARCHAR(10) NOT NULL,
TodaysProcs VARCHAR(30) NOT NULL,
UnschedProcs VARCHAR(30) NOT NULL);
INSERT INTO t1(OpTime,PatNum,Patient,DOB,Age,EC,TodaysProcs)
SELECT CONCAT(op.Abbrev,'-',TIME_FORMAT(a.AptDateTime, '%l:%i')) AS OpTime, p.PatNum, CONCAT(IF(DATE(p.DateFirstVisit)=CURDATE(),'NP-',''),p.LName,', ',p.FName) AS Patient, DATE_FORMAT(p.Birthdate,'%m-%d-%Y') AS DOB, (CASE WHEN (YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5))<200 THEN(YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5)) ELSE '' END) AS Age, CONCAT(IF(p.Email='','E',''),IF(p.WirelessPhone='','C','')) AS EC, GROUP_CONCAT(pc.AbbrDesc) AS TodaysProcs
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
LEFT JOIN operatory op ON op.OperatoryNum=a.Op
LEFT JOIN procedurelog pl ON pl.AptNum=a.AptNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE DATE(a.AptDateTime) = CURDATE() AND AptStatus IN (1,4)
GROUP BY a.AptNum ORDER BY op.OpName,a.AptDateTime;
/* Recall Due? */
CREATE TABLE t2
SELECT a.PatNum, rt.Description AS RecallDue
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON r.RecallTypeNum=rt.RecallTypeNum
WHERE DATE(a.AptDateTime)=CURDATE() AND a.AptStatus IN (1,4) AND a.IsHygiene=0 AND DATE(r.DateDue)<=CURDATE() AND (rt.Description LIKE '%Proph%' OR rt.Description LIKE '%Perio%')
GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.RecallDue=t2.RecallDue WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
/* Unscheduled Procs */
CREATE TABLE t2
SELECT pt.PatNum, GROUP_CONCAT(pc.AbbrDesc) AS Procs
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN appointment a ON A.PatNum=pl.PatNum
WHERE (pl.ProcStatus = '1' AND pl.AptNum = '0' AND ProcFee > 150) AND (DATE(a.AptDateTime) = CURDATE() AND a.AptStatus IN (1,4)) GROUP BY pl.PatNum;
UPDATE t1,t2 SET t1.UnschedProcs=t2.Procs WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
/* Family Balance */
CREATE TABLE t2
SELECT a.PatNum, ROUND(SUM(p2.BalTotal-p2.InsEst),0) AS FamBal
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient p2 ON p.Guarantor=p2.PatNum
WHERE DATE(a.AptDateTime) = CURDATE() AND a.AptStatus IN (1,4) GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.FamBal=t2.FamBal WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
ALTER TABLE t1 DROP PatNum;
INSERT INTO t1(Patient,DOB) SELECT '-----' AS Patient,'-----' AS DOB;
/* Lab Cases Not Received for Appointments in next 5 days */
CREATE TABLE t2(
One VARCHAR(12) NOT NULL,
Patient VARCHAR(25) NOT NULL,
AptDate VARCHAR(10) NOT NULL,
Four VARCHAR(3) NOT NULL,
Five VARCHAR(15) NOT NULL,
Six VARCHAR(3) NOT NULL,
Seven VARCHAR(10) NOT NULL,
Lab VARCHAR(30) NOT NULL,
Nine VARCHAR(30) NOT NULL);
INSERT INTO t2(Patient,AptDate,Lab)
SELECT CONCAT(p.LName,', ',p.FName) AS Patient, DATE(AptDateTime) AS AptDate, l.Description AS Lab
FROM labcase lc
LEFT JOIN laboratory l ON lc.LaboratoryNum=l.LaboratoryNum
LEFT JOIN appointment a ON lc.AptNum=a.AptNum
LEFT JOIN patient p ON lc.PatNum=p.PatNum
WHERE (DATE(a.AptDateTime) BETWEEN CURDATE() AND CURDATE() + INTERVAL 5 DAY) AND DATE(lc.DateTimeRecd)='0001-01-01';
INSERT INTO t2(Patient) SELECT '-----' AS Patient;
/* Lab Cases Received, Need to be scheduled/attached */
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(
One VARCHAR(12) NOT NULL,
Patient VARCHAR(25) NOT NULL,
Received VARCHAR(10) NOT NULL,
Four VARCHAR(3) NOT NULL,
Five VARCHAR(15) NOT NULL,
Six VARCHAR(3) NOT NULL,
Seven VARCHAR(10) NOT NULL,
Lab VARCHAR(30) NOT NULL,
Nine VARCHAR(30) NOT NULL);
INSERT INTO t3(Patient,Received,Lab)
SELECT CONCAT(p.LName,', ',p.FName) AS Patient, DATE(DateTimeRecd) AS Received, l.Description AS Lab
FROM labcase lc
LEFT JOIN laboratory l ON lc.LaboratoryNum=l.LaboratoryNum
LEFT JOIN appointment a ON lc.AptNum=a.AptNum
LEFT JOIN patient p ON lc.PatNum=p.PatNum
WHERE (lc.AptNum=0 OR a.AptStatus IN (3,5)) AND DATE(DateTimeRecd)>'0001-01-01'
ORDER BY DATE(DateTimeRecd) DESC;
INSERT INTO t3(Patient) SELECT '-----' AS Patient;
/* Scheduled Production */
DROP TABLE IF EXISTS t4,t5;
CREATE TABLE t4(
One VARCHAR(12) NOT NULL,
Two VARCHAR(25) NOT NULL,
NetSched VARCHAR(10) NOT NULL,
Four VARCHAR(3) NOT NULL,
Five VARCHAR(15) NOT NULL,
Six VARCHAR(3) NOT NULL,
Seven VARCHAR(10) NOT NULL,
Lab VARCHAR(30) NOT NULL,
Nine VARCHAR(30) NOT NULL,
SchedProd double NOT NULL DEFAULT 0,
SchdWrtOff double NOT NULL DEFAULT 0);
/*Sched Prod*/
INSERT INTO t4(Two, SchedProd)
SELECT 'Scheduled Prod' AS Two, SUM(pl.procfee) AS 'SchedProd'
FROM appointment ap INNER JOIN procedurelog pl ON pl.AptNum=ap.AptNum 
WHERE pl.ProcStatus=1 AND ap.AptStatus IN (1,4) AND DATE(ap.AptDateTime)=CURDATE();
/*Sched Writeoffs*/
CREATE TABLE t5
SELECT SUM(IF((cp.WriteOffEstOverride=-1),IF((cp.WriteOffEst=-1),0,cp.WriteOffEst), cp.WriteOffEstOverride)) AS 'SchdWrtOff'
FROM appointment ap INNER JOIN procedurelog pl ON pl.AptNum=ap.AptNum LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=1 AND ap.AptStatus IN (1,4) AND DATE(ap.AptDateTime)=CURDATE() AND (ISNULL(cp.Status) OR cp.Status=6 /*estimate*/);
UPDATE t4,t5 SET t4.SchdWrtOff=-t5.SchdWrtOff;
DROP TABLE IF EXISTS t5;
UPDATE t4 SET NetSched=ROUND(SchedProd+SchdWrtOff,0);
ALTER TABLE t4 DROP SchedProd, DROP SchdWrtOff;
INSERT INTO t4(NetSched) SELECT '-----' AS NetSched;
/* Output Data */
SELECT * FROM t1
UNION
SELECT * FROM t4
UNION
SELECT '','Pending Lab Cases','AptDate','','','','','Lab',''
UNION
SELECT * FROM t2
UNION
SELECT '','Lab Cases to Schedule','DateRecd','','','','','Lab',''
UNION
SELECT * FROM t3;
DROP TABLE IF EXISTS t1,t2,t3,t4;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

enamelrod
Posts: 462
Joined: Tue Jul 24, 2007 9:51 am

Re: Morning Huddle Report

Post by enamelrod » Wed Nov 12, 2014 2:37 pm

Brilliant.

User avatar
Rickliftig
Posts: 764
Joined: Thu Jul 10, 2008 4:50 pm
Location: West Hartford, CT
Contact:

Re: Morning Huddle Report

Post by Rickliftig » Thu Nov 13, 2014 5:09 am

This is a gem. Thanks!
Another Happy Open Dental User!

Rick Liftig, DMD FAGD
University of CT 1979
West Hartford, CT 06110
srick@snet.net

User avatar
drtech
Posts: 1647
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: Morning Huddle Report

Post by drtech » Tue Jan 27, 2015 10:30 am

Fixed Capitalization Error for Linux servers... "A.PatNum to a.PatNum"


DROP TABLE IF EXISTS t1,t2,t3,t4;
CREATE TABLE t1(
OpTime VARCHAR(12) NOT NULL,
PatNum VARCHAR(25),
Patient VARCHAR(25) NOT NULL,
DOB VARCHAR(10) NOT NULL,
Age VARCHAR(3) NOT NULL,
RecallDue VARCHAR(15) NOT NULL,
EC VARCHAR(3) NOT NULL,
FamBal VARCHAR(10) NOT NULL,
TodaysProcs VARCHAR(30) NOT NULL,
UnschedProcs VARCHAR(30) NOT NULL);
INSERT INTO t1(OpTime,PatNum,Patient,DOB,Age,EC,TodaysProcs)
SELECT CONCAT(op.Abbrev,'-',TIME_FORMAT(a.AptDateTime, '%l:%i')) AS OpTime, p.PatNum, CONCAT(IF(DATE(p.DateFirstVisit)=CURDATE(),'NP-',''),p.LName,', ',p.FName) AS Patient, DATE_FORMAT(p.Birthdate,'%m-%d-%Y') AS DOB, (CASE WHEN (YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5))<200 THEN(YEAR(CURDATE())-YEAR(p.Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(p.Birthdate,5)) ELSE '' END) AS Age, CONCAT(IF(p.Email='','E',''),IF(p.WirelessPhone='','C','')) AS EC, GROUP_CONCAT(pc.AbbrDesc) AS TodaysProcs
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
LEFT JOIN operatory op ON op.OperatoryNum=a.Op
LEFT JOIN procedurelog pl ON pl.AptNum=a.AptNum
LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE DATE(a.AptDateTime) = CURDATE() AND AptStatus IN (1,4)
GROUP BY a.AptNum ORDER BY op.OpName,a.AptDateTime;
/* Recall Due? */
CREATE TABLE t2
SELECT a.PatNum, rt.Description AS RecallDue
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON r.RecallTypeNum=rt.RecallTypeNum
WHERE DATE(a.AptDateTime)=CURDATE() AND a.AptStatus IN (1,4) AND a.IsHygiene=0 AND DATE(r.DateDue)<=CURDATE() AND (rt.Description LIKE '%Proph%' OR rt.Description LIKE '%Perio%')
GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.RecallDue=t2.RecallDue WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
/* Unscheduled Procs */
CREATE TABLE t2
SELECT pt.PatNum, GROUP_CONCAT(pc.AbbrDesc) AS Procs
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN appointment a ON a.PatNum=pl.PatNum
WHERE (pl.ProcStatus = '1' AND pl.AptNum = '0' AND ProcFee > 150) AND (DATE(a.AptDateTime) = CURDATE() AND a.AptStatus IN (1,4)) GROUP BY pl.PatNum;
UPDATE t1,t2 SET t1.UnschedProcs=t2.Procs WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
/* Family Balance */
CREATE TABLE t2
SELECT a.PatNum, ROUND(SUM(p2.BalTotal-p2.InsEst),0) AS FamBal
FROM appointment a
LEFT JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient p2 ON p.Guarantor=p2.PatNum
WHERE DATE(a.AptDateTime) = CURDATE() AND a.AptStatus IN (1,4) GROUP BY a.PatNum;
UPDATE t1,t2 SET t1.FamBal=t2.FamBal WHERE t1.PatNum=t2.PatNum;
DROP TABLE IF EXISTS t2;
ALTER TABLE t1 DROP PatNum;
INSERT INTO t1(Patient,DOB) SELECT '-----' AS Patient,'-----' AS DOB;
/* Lab Cases Not Received for Appointments in next 5 days */
CREATE TABLE t2(
One VARCHAR(12) NOT NULL,
Patient VARCHAR(25) NOT NULL,
AptDate VARCHAR(10) NOT NULL,
Four VARCHAR(3) NOT NULL,
Five VARCHAR(15) NOT NULL,
Six VARCHAR(3) NOT NULL,
Seven VARCHAR(10) NOT NULL,
Lab VARCHAR(30) NOT NULL,
Nine VARCHAR(30) NOT NULL);
INSERT INTO t2(Patient,AptDate,Lab)
SELECT CONCAT(p.LName,', ',p.FName) AS Patient, DATE(AptDateTime) AS AptDate, l.Description AS Lab
FROM labcase lc
LEFT JOIN laboratory l ON lc.LaboratoryNum=l.LaboratoryNum
LEFT JOIN appointment a ON lc.AptNum=a.AptNum
LEFT JOIN patient p ON lc.PatNum=p.PatNum
WHERE (DATE(a.AptDateTime) BETWEEN CURDATE() AND CURDATE() + INTERVAL 5 DAY) AND DATE(lc.DateTimeRecd)='0001-01-01';
INSERT INTO t2(Patient) SELECT '-----' AS Patient;
/* Lab Cases Received, Need to be scheduled/attached */
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(
One VARCHAR(12) NOT NULL,
Patient VARCHAR(25) NOT NULL,
Received VARCHAR(10) NOT NULL,
Four VARCHAR(3) NOT NULL,
Five VARCHAR(15) NOT NULL,
Six VARCHAR(3) NOT NULL,
Seven VARCHAR(10) NOT NULL,
Lab VARCHAR(30) NOT NULL,
Nine VARCHAR(30) NOT NULL);
INSERT INTO t3(Patient,Received,Lab)
SELECT CONCAT(p.LName,', ',p.FName) AS Patient, DATE(DateTimeRecd) AS Received, l.Description AS Lab
FROM labcase lc
LEFT JOIN laboratory l ON lc.LaboratoryNum=l.LaboratoryNum
LEFT JOIN appointment a ON lc.AptNum=a.AptNum
LEFT JOIN patient p ON lc.PatNum=p.PatNum
WHERE (lc.AptNum=0 OR a.AptStatus IN (3,5)) AND DATE(DateTimeRecd)>'0001-01-01'
ORDER BY DATE(DateTimeRecd) DESC;
INSERT INTO t3(Patient) SELECT '-----' AS Patient;
/* Scheduled Production */
DROP TABLE IF EXISTS t4,t5;
CREATE TABLE t4(
One VARCHAR(12) NOT NULL,
Two VARCHAR(25) NOT NULL,
NetSched VARCHAR(10) NOT NULL,
Four VARCHAR(3) NOT NULL,
Five VARCHAR(15) NOT NULL,
Six VARCHAR(3) NOT NULL,
Seven VARCHAR(10) NOT NULL,
Lab VARCHAR(30) NOT NULL,
Nine VARCHAR(30) NOT NULL,
SchedProd double NOT NULL DEFAULT 0,
SchdWrtOff double NOT NULL DEFAULT 0);
/*Sched Prod*/
INSERT INTO t4(Two, SchedProd)
SELECT 'Scheduled Prod' AS Two, SUM(pl.procfee) AS 'SchedProd'
FROM appointment ap INNER JOIN procedurelog pl ON pl.AptNum=ap.AptNum
WHERE pl.ProcStatus=1 AND ap.AptStatus IN (1,4) AND DATE(ap.AptDateTime)=CURDATE();
/*Sched Writeoffs*/
CREATE TABLE t5
SELECT SUM(IF((cp.WriteOffEstOverride=-1),IF((cp.WriteOffEst=-1),0,cp.WriteOffEst), cp.WriteOffEstOverride)) AS 'SchdWrtOff'
FROM appointment ap INNER JOIN procedurelog pl ON pl.AptNum=ap.AptNum LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=1 AND ap.AptStatus IN (1,4) AND DATE(ap.AptDateTime)=CURDATE() AND (ISNULL(cp.Status) OR cp.Status=6 /*estimate*/);
UPDATE t4,t5 SET t4.SchdWrtOff=-t5.SchdWrtOff;
DROP TABLE IF EXISTS t5;
UPDATE t4 SET NetSched=ROUND(SchedProd+SchdWrtOff,0);
ALTER TABLE t4 DROP SchedProd, DROP SchdWrtOff;
INSERT INTO t4(NetSched) SELECT '-----' AS NetSched;
/* Output Data */
SELECT * FROM t1
UNION
SELECT * FROM t4
UNION
SELECT '','Pending Lab Cases','AptDate','','','','','Lab',''
UNION
SELECT * FROM t2
UNION
SELECT '','Lab Cases to Schedule','DateRecd','','','','','Lab',''
UNION
SELECT * FROM t3;
DROP TABLE IF EXISTS t1,t2,t3,t4;
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

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

Re: Morning Huddle Report

Post by Tom Zaccaria » Wed Jan 28, 2015 3:59 am

This is getting interesting.

Could we change the orientation to Landscape and add a column for date of last BW/FMX/PAN for the hygiene patients (those with a periodic exam, PerEx)?
As in Column Heading of 'X-rays'
BW 6-6-2014
FMX 6-6-2009

This would make it much easier to determine who is due for what as opposed to having to sift through chart or account to see when the last BWs were taken.

drtmz

Post Reply