Query Help!!

For users or potential users.
Post Reply
Mal
Posts: 2
Joined: Tue May 14, 2019 1:04 pm

Query Help!!

Post by Mal » Tue May 14, 2019 1:09 pm

I have been trying for way too long to edit this query to what I'm looking for. The current query I am using is:

SET @pos=0, @FromDate='2019-01-01' , @ToDate='2019-01-31';
SELECT @pos:=@pos+1 as 'Count', pa.PatNum,
pc.ProcCode as 'Code', abbrdesc as 'Description', ToothNum, DATE_FORMAT(pl.DateTP,'%m-%d-%Y') AS 'DateTP',
pr.Abbr, ProcFee, IF(pl.AptNum, IF(pl.ProcStatus=2, 'Complete', IF(pl.Procstatus=1, 'Scheduled', 'Other')), IF(pl.ProcStatus=2, 'Complete','TP')) AS 'Status'
FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pr ON pl.ProvNum=pr.ProvNum
WHERE pl.ProcStatus IN(1,2) AND
(DateTP BETWEEN @FromDate AND @ToDate)
AND pc.ProcCode NOT LIKE ('D1%') AND pc.ProcCode NOT LIKE('D0%') AND pc.ProcCode NOT LIKE('N4%') AND pc.ProcCode NOT LIKE('D999%') AND pc.ProcCode NOT LIKE('N99%') AND pc.ProcCode NOT LIKE('99%')
ORDER BY DateTP,pa.LName, pa.FName ASC;

This query allows me to see all treatment that was treatment planned in a specific date range and the status of each code. I would really like to break this into three separate reports: 1. will show only treatment completed 2. will show only treatment scheduled and 3. will show treatment left with a status of "TP"
Any ideas of how to proceed or what I'm missing that this query won't work without all status displayed.
Thanks

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

Re: Query Help!!

Post by Tom Zaccaria » Tue May 14, 2019 1:47 pm

try changing the last line to

ORDER by status;

drtmz

Mal
Posts: 2
Joined: Tue May 14, 2019 1:04 pm

Re: Query Help!!

Post by Mal » Wed May 15, 2019 8:20 am

So that does help ordering this one report by status, but I am really hoping to actually break the report to only showing me one status. My goal is to be able to use this query to break into three separate reports (1 for completed, 1 for treatment planned, and 1 for scheduled)

User avatar
Ardavan
Posts: 106
Joined: Sat May 15, 2010 9:10 am

Re: Query Help!!

Post by Ardavan » Wed May 15, 2019 10:18 am

The database documentation is your best friend: https://www.opendental.com/OpenDentalDo ... on18-2.xml

ProcStatus column:
TP: 1- Treatment Plan.
C: 2- Complete.
EC: 3- Existing Current Provider.
EO: 4- Existing Other Provider.
R: 5- Referred Out.
D: 6- Deleted.
Cn: 7- Condition.
TPi: 8- Treatment Plan inactive.

The procedurelog table does not differentiate between treatment planned and scheduled procedures, you will have to join the appointment table for that. Otherwise I have added the @Status variable below so you can choose for the report to return treatment planned (1) or completed procedures (2). OpenDental support will likely write your query for $90 (last I checked it was $90/hour with a minimum of 1 hour and if this takes them more than that they need to hire new DBAs), I'll do it for a reasonable offer.

SET @pos=0, @FromDate='2019-01-01' , @ToDate='2019-01-31', @Status=1; -- 1 for TP, 2 for completed
SELECT @pos:=@pos+1 as 'Count', pa.PatNum,
pc.ProcCode as 'Code', abbrdesc as 'Description', ToothNum, DATE_FORMAT(pl.DateTP,'%m-%d-%Y') AS 'DateTP',
pr.Abbr, ProcFee, IF(pl.AptNum, IF(pl.ProcStatus=2, 'Complete', IF(pl.Procstatus=1, 'Scheduled', 'Other')), IF(pl.ProcStatus=2, 'Complete','TP')) AS 'Status'
FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pr ON pl.ProvNum=pr.ProvNum
WHERE pl.ProcStatus=@Status AND
(DateTP BETWEEN @FromDate AND @ToDate)
AND pc.ProcCode NOT LIKE ('D1%') AND pc.ProcCode NOT LIKE('D0%') AND pc.ProcCode NOT LIKE('N4%') AND pc.ProcCode NOT LIKE('D999%') AND pc.ProcCode NOT LIKE('N99%') AND pc.ProcCode NOT LIKE('99%')
ORDER BY DateTP,pa.LName, pa.FName ASC;
There are 10 types of people in this world, those who will laugh at this joke, and those who won't. ~Annonymous Bug Writer

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Query Help!!

Post by dgraffeo » Wed May 15, 2019 10:24 am

You'd have to add an AND clause that looks at the procedurelog.ProcStatus - You can see Here: https://opendental.com/OpenDentalDocume ... ocedurelog that a ProcStatus of 2 is complete, for instance.
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

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

Re: Query Help!!

Post by Tom Zaccaria » Thu May 16, 2019 8:41 am

The simplest solution may be to export the query to an excel spreadsheet and then just highlight and print the rows you want.

drtmz

Post Reply