is there a daily report for patients with appointments today

[lethstang]

but are also due for recalls? We are having a problem in that we will have patients on the schedule who could also be using their say, crown seat appointment for their six month recall. Right now my front is going over the next days schedule to catch them, but im hoping theres a report or query they can print so they dont have to go through each patients chart.

[cmcgehee]

Query 1005 sounds like it could be close. It will give a yes or no simply on whether the patient has a an appointment scheduled with recall procedures on it.

Code: Select all

/*1005 Patients with an appointment today, showing if that appointment has any recall triggers.  Also shows years since last pano, months since last BW's, and months since lastFMX.*/
/*Pano based on D0330, 2BW=D0272, 4BW=D0274, FMX=D0210.  Recall months estimated by 30 days in a month*/
SELECT p.PatNum AS 'Pat#', CONCAT(p.LName,', ',p.FName) AS 'Patient', /* a.AptNum, */
IFNULL(FORMAT(DATEDIFF(CURDATE(),(SELECT MAX(pano.ProcDate)
	FROM procedurelog pano
	INNER JOIN procedurecode panocode ON pano.CodeNum=panocode.CodeNum
	WHERE panocode.ProcCode='D0330' 
	AND pano.ProcStatus=2 
	AND pano.PatNum=p.PatNum))/365,1)
,'None') AS 'YrsPano',
(CASE WHEN ((SELECT COUNT(*) FROM procedurelog pl2
	INNER JOIN recall r2 ON r2.PatNum=pl2.PatNum
	INNER JOIN recalltrigger rt2 ON rt2.RecallTypeNum=r2.RecallTypeNum 
		AND pl2.CodeNum=rt2.CodeNum
	WHERE pl2.AptNum=a.AptNum)>0) THEN 'Yes' ELSE 'No' END
) AS 'RecallApt', 
(CASE WHEN (IFNULL(FORMAT(DATEDIFF(CURDATE(),
	(SELECT MAX(Mo2BW.ProcDate)
	FROM procedurelog Mo2BW
	INNER JOIN procedurecode Mo2BWcode ON Mo2BW.CodeNum=Mo2BWcode.CodeNum
	WHERE Mo2BWcode.ProcCode='D0272' 
	AND Mo2BW.ProcStatus=2 
	AND Mo2BW.PatNum=p.PatNum))/30,1),'None')) >= 6.0 THEN 'Due' 
	ELSE IFNULL(FORMAT(DATEDIFF(CURDATE(),(SELECT MAX(Mo2BW.ProcDate)
	FROM procedurelog Mo2BW
	INNER JOIN procedurecode Mo2BWcode ON Mo2BW.CodeNum=Mo2BWcode.CodeNum
	WHERE Mo2BWcode.ProcCode='D0272' 
	AND Mo2BW.ProcStatus=2 
	AND Mo2BW.PatNum=p.PatNum))/30,1),'None') END
) AS 'Mo2BW', 
(CASE WHEN (IFNULL(FORMAT(DATEDIFF(CURDATE(),
	(SELECT MAX(Mo4BW.ProcDate)
	FROM procedurelog Mo4BW
	INNER JOIN procedurecode Mo4BWcode ON Mo4BW.CodeNum=Mo4BWcode.CodeNum
	WHERE Mo4BWcode.ProcCode='D0274' 
	AND Mo4BW.ProcStatus=2 
	AND Mo4BW.PatNum=p.PatNum))/30,1),'None')) >= 6.0 THEN 'Due' ELSE 
	IFNULL(FORMAT(DATEDIFF(CURDATE(),(SELECT MAX(Mo4BW.ProcDate)
	FROM procedurelog Mo4BW
	INNER JOIN procedurecode Mo4BWcode ON Mo4BW.CodeNum=Mo4BWcode.CodeNum
	WHERE Mo4BWcode.ProcCode='D0274' 
	AND Mo4BW.ProcStatus=2 
	AND Mo4BW.PatNum=p.PatNum))/30,1),'None') END
) AS 'Mo4BW',
(CASE WHEN (IFNULL(FORMAT(DATEDIFF(CURDATE(),
	(SELECT MAX(MoFMX.ProcDate)
	FROM procedurelog MoFMX
	INNER JOIN procedurecode MoFMXcode ON MoFMX.CodeNum=MoFMXcode.CodeNum
	WHERE MoFMXcode.ProcCode='D0210'
	AND MoFMX.ProcStatus=2 
	AND MoFMX.PatNum=p.PatNum))/30,1),'None')) >= 60.0 THEN 'Due' ELSE 
	IFNULL(FORMAT(DATEDIFF(CURDATE(),(SELECT MAX(MoFMX.ProcDate)
	FROM procedurelog MoFMX
	INNER JOIN procedurecode MoFMXcode ON MoFMX.CodeNum=MoFMXcode.CodeNum
	WHERE MoFMXcode.ProcCode='D0210'
	AND MoFMX.ProcStatus=2 
	AND MoFMX.PatNum=p.PatNum))/30,1),'None') END
) AS 'MoFMX'
FROM patient p
INNER JOIN appointment a ON a.PatNum=p.PatNum
	AND DATE(a.AptDateTime)=CURDATE()
	AND a.AptStatus IN (1,2,4,5)
ORDER BY p.LName, p.FName, 'RecallApt' DESC