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;