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
Changing deceased account Guantor
Changing deceased account Guantor
________________________
Brad Johnson, D.D.S.
Corridor Family Dentistry, PLLC
2120 Westdale DR SW
Cedar Rapids, IA 52404
(319)396-7263
Brad Johnson, D.D.S.
Corridor Family Dentistry, PLLC
2120 Westdale DR SW
Cedar Rapids, IA 52404
(319)396-7263
Re: Changing deceased account Guantor
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.ki0ak wrote:...looking for a script/SQL to create a list of deceased patients set as account Guarantors.
https://opendentalsoft.com:1943/ODQuery ... tForm.aspx
https://opendentalsoft.com:1943/ODQuery ... yList.aspx
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).ki0ak wrote:Would be even better if it would place the next oldest family member as Guarantor.
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
Jason Salmon
Open Dental Software
http://www.opendental.com
-
- Posts: 355
- Joined: Mon Feb 25, 2008 3:09 am
Re: Changing deceased account Guantor
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
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
Re: Changing deceased account Guantor
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;
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;
James Zemencik, DMD
http://www.bridgeville-dentist.com/
http://www.bridgeville-dentist.com/
Re: Changing deceased account Guantor
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
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
David Graffeo
Open Dental Software
http://www.opendental.com
Re: Changing deceased account Guantor
Thanks - that's what I needed.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
Brad
________________________
Brad Johnson, D.D.S.
Corridor Family Dentistry, PLLC
2120 Westdale DR SW
Cedar Rapids, IA 52404
(319)396-7263
Brad Johnson, D.D.S.
Corridor Family Dentistry, PLLC
2120 Westdale DR SW
Cedar Rapids, IA 52404
(319)396-7263