Adding priority to Tx Plan Query

For users or potential users.

Adding priority to Tx Plan Query

Postby spolevoy » Thu Jun 20, 2019 6:53 am

I have a custom query that returns tx planned procedures for given range.
I'd like to add a column for Tx plan priority. I looked through query examples but couldn't find a good field to select.


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;
spolevoy
 
Posts: 69
Joined: Wed Oct 17, 2012 4:45 am

Return to Main Forum

Who is online

Users browsing this forum: No registered users and 8 guests

cron