Adding priority to TP report

For users or potential users.
Post Reply
spolevoy
Posts: 74
Joined: Wed Oct 17, 2012 4:45 am

Adding priority to TP report

Post by spolevoy » Thu Sep 19, 2019 6:20 am

We have a custom report (written by Kevin Rossen a while ago, thanks Kevin!) that returns incomplete tx for the last week/3 weeks/3 months.
I'd like to add a column for TP priority, as we started using custom definitions, like IN PROGRESS or NEXT INS YR or WAIT.
I can't figure out how to add to the report, what the field name is.


This is the query. Any help is appreciated.

DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t1(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND PatStatus = '0' AND AptNum = '0' AND ProcFee >= 170 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
CREATE TABLE t2(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t2(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND PatStatus = '0' AND AptNum = '0' AND ProcFee >=170 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 28 DAY) AND DATE_SUB(NOW(), INTERVAL 21 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
CREATE TABLE t3(Name VARCHAR(30) NOT NULL,Age VARCHAR(3),Wireless VARCHAR(13) NOT NULL,Home VARCHAR(13) NOT NULL DEFAULT '',DateTP date,Procs VARCHAR(35) NOT NULL,Fees double NOT NULL DEFAULT 0,NextAppt date);
INSERT INTO t3(Name,Age,Wireless,Home,DateTP,Procs,Fees,NextAppt)
SELECT CONCAT(LName,', ',IF(Preferred='',FName,CONCAT(FName,' (',Preferred,')'))) AS 'Name', DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(Birthdate, '00-%m-%d')) AS 'Age', pt.WirelessPhone AS 'Wireless', pt.HmPhone AS 'Home', ProcDate AS 'DateTP', GROUP_CONCAT(pc.AbbrDesc) AS 'Procs', SUM(ProcFee) AS 'Fees', DATE(A.DateNextSchedApt) AS 'NextAppt'
FROM procedurelog pl LEFT JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN patient pt ON pt.PatNum=pl.PatNum LEFT JOIN (SELECT appointment.PatNum, MIN(DATE(AptDateTime)) AS DateNextSchedApt FROM appointment WHERE DATE(AptDateTime)>=CURDATE() AND AptStatus=1 GROUP BY appointment.PatNum) A ON A.PatNum=pl.PatNum
WHERE ProcStatus = '1' AND PatStatus = '0' AND AptNum = '0' AND ProcFee >= 170 AND (ProcDate BETWEEN DATE_SUB(NOW(), INTERVAL 120 DAY) AND DATE_SUB(NOW(), INTERVAL 90 DAY))
GROUP BY pl.PatNum ORDER BY Fees DESC;
SELECT * FROM t1
UNION
SELECT * FROM t2
UNION
SELECT * FROM t3;
DROP TABLE IF EXISTS t1,t2,t3;

Post Reply