Open Dental Forum

Hello,

I couldn't find anything on this, but am looking for a script/SQL to create a list of deceased patients set as account Guarantors. Would be even better if it would place the next oldest family member as Guarantor. Looking to identify deceased Guarantors to prevent statements being addressed to them.

Thanks.

Brad

ki0ak wrote:...looking for a script/SQL to create a list of deceased patients set as account Guarantors.

Getting a list of deceased patients that are set as the guarantor is a very simple query that might even be helpful for other users some day in the future. I'd recommend filling out a query request so that it can make our way to our Query Examples page for others to enjoy as well. The query is so simple I'd imagine we could provide it for no charge.
https://opendentalsoft.com:1943/ODQuery ... tForm.aspx
https://opendentalsoft.com:1943/ODQuery ... yList.aspx

ki0ak wrote:Would be even better if it would place the next oldest family member as Guarantor.

This should not be done via a script but should be a programmatic change. There are too many other important things that need to go along with switching the guarantor of the family / super family (e.g. running aging after the new guarantor has been assigned, etc). This would be a feature request for a tool that would comb through the database finding these patients and fixing or prompting for manual attention (e.g. the scenario where the deceased patient is the only patient in the family).
https://www.opendental.com/manual/featurerequests.html

How about this for a start?

select patnum from patient
where patstatus = 5

It will at least give you a list of deceased patients. Then you could edit the individual records.

drtmz

See if this works:

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp SELECT DISTINCT Guarantor FROM patient WHERE PatStatus =0;
SELECT LName, FName, Address, Address2, City, State, Zip, HmPhone, Birthdate, MedUrgNote FROM patient, tmp
WHERE patient.PatNum=tmp.Guarantor AND PatStatus =5 ORDER BY LName;
DROP TABLE IF EXISTS tmp;

That will work in most cases, but it will only fetch deceased guarantors from patients who are currently an active patient. If the patient is archived or are also deceased, it won't fetch the guarantor. I think the simplest way to fetch deceased guarantors is to find all patient rows whose PatNum=Guarantor (aka. the patient we're looking at is the guarantor) and the PatStatus=5.

SELECT * FROM patient WHERE patient.PatNum=patient.Guarantor AND patient.PatStatus=5

dgraffeo wrote:That will work in most cases, but it will only fetch deceased guarantors from patients who are currently an active patient. If the patient is archived or are also deceased, it won't fetch the guarantor. I think the simplest way to fetch deceased guarantors is to find all patient rows whose PatNum=Guarantor (aka. the patient we're looking at is the guarantor) and the PatStatus=5.

SELECT * FROM patient WHERE patient.PatNum=patient.Guarantor AND patient.PatStatus=5

Thanks - that's what I needed.

Brad