Post
by Tom Zaccaria » Fri Nov 05, 2021 5:14 am
Been using this forever. Change the procedure codes to search for whatever you want.
Time interval is in days to search.
drtmz
/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/
/*Just change the interval values below to get the proper month*/
SET @FromDate=Date_Sub(CurDate(),Interval 177 day),@ToDate=Date_Sub(CurDate(),Interval 173 day);
SELECT p.LName,p.FName, p.email, p.wirelessphone, PreferRecallMethod as 'Rcl', pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND PatStatus = '0'
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110','D1120')
AND CarrierName not like '%Dominion%'
AND CarrierName not like '%Rayant%'
AND p.PatNum NOT IN(SELECT DISTINCT(pl.PatNum) FROM procedurelog pl
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum AND pc.ProcCode IN('D1110','D1120')
LEFT JOIN appointment a ON a.AptNum=pl.AptNum
WHERE ((pl.ProcDate>@ToDate AND pl.ProcStatus=2)
OR (a.AptStatus=1 AND a.AptDateTime>=CURDATE())))
GROUP BY pl.ProcNum
ORDER BY p.email, pl.ProcDate, p.LName,p.FName;