Add Insurance Plan to Query #164

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Add Insurance Plan to Query #164

Post by Tom Zaccaria » Wed Feb 28, 2018 3:41 am

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;

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Add Insurance Plan to Query #164

Post by cmcgehee » Wed Feb 28, 2018 7:51 am

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;
Chris McGehee
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Add Insurance Plan to Query #164

Post by Tom Zaccaria » Wed Feb 28, 2018 11:41 am

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

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Add Insurance Plan to Query #164

Post by tgriswold » Wed Feb 28, 2018 2:27 pm

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).
Travis Griswold
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Add Insurance Plan to Query #164

Post by Tom Zaccaria » Thu Mar 01, 2018 2:43 am

OK Thanks
drtmz

Post Reply