Open Dental Forum

I am trying to add the patient's current insurance plan to query #164 (below).
Any help would be appreciated. Thanks
drtmz

/*164 Returns all treatment planned procedures for active patients without a scheduled OR planned apt, who were last seen in a given date range
with phone numbers, useful for those transitioning to planned appointments (this differs FROM #56 largely in that it is date limited and lists out the treatment)*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
DROP TABLE IF EXISTS tmp1;
DROP TABLE IF EXISTS tmp2;
SET @FROMDate='2018-01-1' , @ToDate='2018-2-1';
CREATE TABLE tmp1 AS SELECT patient.PatNum, CONCAT(LName, ', ',FName, ' ', MiddleI) AS PatName, HmPhone, WkPhone,
WirelessPhone AS Wireless, ProcFee, pc.abbrdesc, pc.ProcCode FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1
LEFT JOIN appointment ap ON patient.PatNum=ap.PatNum AND (ap.AptStatus=1 OR ap.AptStatus=6 )
WHERE ap.AptNum IS NULL AND patient.PatStatus=0;
CREATE TABLE tmp2 AS SELECT p.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', MAX(ProcDate) AS 'DateLast'
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE pl.ProcStatus=2 AND p.PatStatus=0
GROUP BY pl.PatNum;
SELECT PatName,Left(HmPhone,15) AS HmPhone,Left(WkPhone,21) As WKPhone,Left(Wireless,15) AS Wireless, ProcCode, ProcFee AS '$Fee', LastVisit
FROM tmp1 INNER JOIN tmp2 ON tmp1.PatNum=tmp2.PatNum
WHERE DateLast BETWEEN @FROMDate AND @ToDate
ORDER BY PatName;
DROP TABLE IF EXISTS tmp1;

Here it is with the patient's primary insurance showing as the last column.


/*164 Returns all treatment planned procedures for active patients without a scheduled OR planned apt, who were last seen in a given date range
with phone numbers, useful for those transitioning to planned appointments (this differs FROM #56 largely in that it is date limited and lists out the treatment)*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
DROP TABLE IF EXISTS tmp1;
DROP TABLE IF EXISTS tmp2;
SET @FROMDate='2018-01-1' , @ToDate='2018-2-1';
CREATE TABLE tmp1 AS SELECT patient.PatNum, CONCAT(LName, ', ',FName, ' ', MiddleI) AS PatName, HmPhone, WkPhone,
WirelessPhone AS Wireless, ProcFee, pc.abbrdesc, pc.ProcCode FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=1
LEFT JOIN appointment ap ON patient.PatNum=ap.PatNum AND (ap.AptStatus=1 OR ap.AptStatus=6 )
WHERE ap.AptNum IS NULL AND patient.PatStatus=0;
CREATE TABLE tmp2 AS SELECT p.PatNum, DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') AS 'LastVisit', MAX(ProcDate) AS 'DateLast'
FROM patient p
INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
WHERE pl.ProcStatus=2 AND p.PatStatus=0
GROUP BY pl.PatNum;
SELECT PatName,LEFT(HmPhone,15) AS HmPhone,LEFT(WkPhone,21) AS WKPhone,LEFT(Wireless,15) AS Wireless, ProcCode, ProcFee AS '$Fee', LastVisit ,
ca.CarrierName AS 'Primary Insurance'
FROM tmp1 INNER JOIN tmp2 ON tmp1.PatNum=tmp2.PatNum
LEFT JOIN patplan pp ON tmp1.PatNum=pp.PatNum
AND pp.Ordinal=1 /*Primary only*/
LEFT JOIN inssub ins ON pp.InsSubNum=ins.InsSubNum
LEFT JOIN insplan inp ON ins.PlanNum=inp.PlanNum
LEFT JOIN carrier ca ON inp.CarrierNum=ca.CarrierNum
WHERE DateLast BETWEEN @FROMDate AND @ToDate
ORDER BY PatName;
DROP TABLE IF EXISTS tmp1;

Perfecto. OD support to the rescue again.
Great Work.
drtmz

It looks like you missed copying one line "DROP TABLE IF EXISTS tmp2;" at the very bottom of this query when you grabbed it off the example page. I'd recommend adding it back if you are saving it to your favorites or somewhere, otherwise a table will stick around in your database forever after running that query (Though when you ran it next time it would delete the table and re-add it).

OK Thanks
drtmz