Query for TP Procedures Completed Same Day

[valeries]

Does anyone have a query written for treatment planned procedures by provider that are set complete the same day they were originally treatment planned? Thanks!

[Tom Zaccaria]

Try this, modified #326

/*326 Work treatment planned Today, and status (is it complete or TP) and whether accepted (scheduled or complete) or just TP and not accepted (not scheduled)*/
SET @pos=0, @FromDate=CurDate() , @ToDate=CurDate();
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 pl.ProcStatus=2
ORDER BY DateTP,pa.LName, pa.FName ASC;

Hope this helps,
drtmz