Code: Select all
/*525 All recall appointments in date range with status.*/
SET @FromDate='2015-01-01' , @ToDate='2016-12-21'; /*set dates here, includes these and all between*/
SELECT p.FName,
p.LName,
p.WirelessPhone,
p.HmPhone,
p.WkPhone,
a.AptDateTime,
a.AptStatus,
MIN(r.DateDue) AS DateDue,
rty.Description
FROM appointment a
INNER JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN recall r ON r.PatNum=p.PatNum
INNER JOIN recalltype rty ON rty.RecallTypeNum=r.RecallTypeNum
INNER JOIN recalltrigger rtr ON rtr.RecallTypeNum=r.RecallTypeNum
INNER JOIN procedurelog pl ON pl.CodeNum=rtr.CodeNum
AND pl.ProcStatus IN(1,2) /*TP or Completed proc*/
AND pl.AptNum=a.AptNum
WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate+INTERVAL 1 DAY
GROUP BY a.AptNum
ORDER BY a.AptDateTime