Query -Financial Urgent Note

For users or potential users.
Post Reply
Tabakdds
Posts: 4
Joined: Thu Mar 26, 2015 5:41 pm

Query -Financial Urgent Note

Post by Tabakdds » Tue Nov 26, 2019 2:36 pm

I was wondering if someone could help me?
I would like to run a query for specific appointment date (usually next day) that will provide me with patients name, appointment time and Family Urgent Family Note.
Currently, I found this query but it only displays the Family Urgent Financial Note if guarantor is coming for the appointment and totally ignores any other family members.
I would like to have ability to see the family urgent financial note if any of the family members are coming for their appointment on specific date, and it should not depend whether or not they are insured.

Current query "987"- that does not work for me
/*987 List of insured patients with a scheduled appointment in date range, with Name, PatNum, and FamFinUrgNote listed.*/
/*Query code written/modified: 07/09/2015*/
SET @StartDate='2019-11-27', @EndDate='2019-11-27';
SELECT CONCAT(patient.LName, ', ', patient.FName) AS Patient,
patient.PatNum,
patient.FamFinUrgNote
FROM patient
INNER JOIN appointment ON patient.PatNum = appointment.PatNum
AND DATE(appointment.AptDateTime) BETWEEN @StartDate AND @EndDate
AND appointment.AptStatus=1
INNER JOIN patplan pp ON pp.PatNum=patient.PatNum AND pp.Ordinal=1


Thank you in advance,
Respectfully,
Natalya

DougR
Posts: 3
Joined: Wed Feb 05, 2020 5:31 pm

Re: Query -Financial Urgent Note

Post by DougR » Wed Feb 05, 2020 6:42 pm

I know this is old, but where is the "Where" clause?

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Query -Financial Urgent Note

Post by cmcgehee » Thu Feb 06, 2020 8:11 am

The "where" clause is optional, so this particular query doesn't have one. If you wanted to add it, it would go after the "join" clauses:

Code: Select all

/*987 List of insured patients with a scheduled appointment in date range, with Name, PatNum, and FamFinUrgNote listed.*/
/*Query code written/modified: 07/09/2015*/
SET @StartDate='2019-11-27', @EndDate='2019-11-27';
SELECT CONCAT(patient.LName, ', ', patient.FName) AS Patient,
patient.PatNum,
patient.FamFinUrgNote
FROM patient
INNER JOIN appointment ON patient.PatNum = appointment.PatNum
AND DATE(appointment.AptDateTime) BETWEEN @StartDate AND @EndDate
AND appointment.AptStatus=1
INNER JOIN patplan pp ON pp.PatNum=patient.PatNum AND pp.Ordinal=1
WHERE patient.PatStatus=0 #active
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply