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
Query Help!!
-
- Posts: 358
- Joined: Mon Feb 25, 2008 3:09 am
Re: Query Help!!
try changing the last line to
ORDER by status;
drtmz
ORDER by status;
drtmz
Re: Query Help!!
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)
Re: Query Help!!
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;
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
Re: Query Help!!
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
David Graffeo
Open Dental Software
http://www.opendental.com
-
- Posts: 358
- Joined: Mon Feb 25, 2008 3:09 am
Re: Query Help!!
The simplest solution may be to export the query to an excel spreadsheet and then just highlight and print the rows you want.
drtmz
drtmz