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