Query for other recall setups
Query for other recall setups
I have a few procedures set up in the recall system which is awesome! I want to do a query where I can see who hasn't had some of these set up, but don't know how to call for this in the query since it is a custom recall? For example, one is a recall for health history updates, a non-ADA code I created to help us get health history updates once a year. I want to query to see who hasn't been put on this recall system, but don't know what that custom created recall code is written as. Any advice
Re: Query for other recall setups
This is the best work around I came up with, but it also shows people that are scheduled for pano recall, but just haven't had it done yet.
/* List of patients with exam but without pano*/
SELECT p.LName, p.FName
FROM patient p
INNER JOIN patient g ON p.guarantor=g.PatNum
INNER JOIN procedurelog pcomp ON pcomp.PatNum=p.PatNum
INNER JOIN procedurecode pccomp ON pccomp.CodeNum=pcomp.CodeNum AND ProcStatus=2
WHERE (
SELECT COUNT(*) FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=2
WHERE pl.PatNum=p.PatNum AND
(pc.ProcCode LIKE('D0330'))
AND pl.ToothNum=pcomp.ToothNum
)=0 AND pccomp.ProcCode IN('D0150')
/* List of patients with exam but without pano*/
SELECT p.LName, p.FName
FROM patient p
INNER JOIN patient g ON p.guarantor=g.PatNum
INNER JOIN procedurelog pcomp ON pcomp.PatNum=p.PatNum
INNER JOIN procedurecode pccomp ON pccomp.CodeNum=pcomp.CodeNum AND ProcStatus=2
WHERE (
SELECT COUNT(*) FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum AND ProcStatus=2
WHERE pl.PatNum=p.PatNum AND
(pc.ProcCode LIKE('D0330'))
AND pl.ToothNum=pcomp.ToothNum
)=0 AND pccomp.ProcCode IN('D0150')