Query Help!!

[Mal]

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]

try changing the last line to

ORDER by status;

drtmz

[Mal]

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)

[Ardavan]

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;

[dgraffeo]

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.

[Tom Zaccaria]

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

drtmz