Changing deceased account Guantor

For users or potential users.
Post Reply
ki0ak
Posts: 29
Joined: Tue Feb 09, 2010 6:38 am

Changing deceased account Guantor

Post by ki0ak » Mon Jan 07, 2019 1:58 pm

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
________________________
Brad Johnson, D.D.S.
Corridor Family Dentistry, PLLC
2120 Westdale DR SW
Cedar Rapids, IA 52404
(319)396-7263

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Changing deceased account Guantor

Post by jsalmon » Tue Jan 08, 2019 9:26 am

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
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Changing deceased account Guantor

Post by Tom Zaccaria » Wed Jan 09, 2019 8:56 am

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

JimZ
Posts: 113
Joined: Thu Nov 01, 2007 9:16 pm
Location: Pittsburgh, PA
Contact:

Re: Changing deceased account Guantor

Post by JimZ » Wed Jan 09, 2019 6:57 pm

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;

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Changing deceased account Guantor

Post by dgraffeo » Thu Jan 10, 2019 10:31 am

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
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

ki0ak
Posts: 29
Joined: Tue Feb 09, 2010 6:38 am

Re: Changing deceased account Guantor

Post by ki0ak » Wed Jan 16, 2019 12:34 pm

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
________________________
Brad Johnson, D.D.S.
Corridor Family Dentistry, PLLC
2120 Westdale DR SW
Cedar Rapids, IA 52404
(319)396-7263

Post Reply