Need to change this query a little bit....help please!

[Janet Century]

This is #505 from the Examples page

/*505 Balances of patients with appointments on a given date*/
SET @AptDate='2010-08-03';
SELECT appointment.AptDateTime,patient.LName,patient.FName,patient.EstBalance AS PatBal, g.BalTotal
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
INNER JOIN patient g ON patient.Guarantor=g.PatNum
AND DATE(appointment.AptDateTime)=@AptDate
AND AptStatus != 6
AND AptStatus != 3;

Few things I want to change if possible (or let me know if there's already another report that has these features):
- Can I eliminate the appointment time? I don't need it and it's taking up valuable column space when I export this to a spreadsheet.
- Can I put in a range of dates?
- Can I add a column that notes whether or not the patient has insurance (whether it's single, double, whatever- just need to know if they have any), or can I exclude patients who do NOT have any insurance (that might be the easier way to go for what I need it for.)


Thanks in advance!

[Tom Zaccaria]

This should remove the appointment column

/*505 Balances of patients with appointments on a given date*/
SET @AptDate='2010-08-03';
SELECT patient.LName,patient.FName,patient.EstBalance AS PatBal, g.BalTotal
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
INNER JOIN patient g ON patient.Guarantor=g.PatNum
AND DATE(appointment.AptDateTime)=@AptDate
AND AptStatus != 6
AND AptStatus != 3;

drtmz

[Tom Zaccaria]

This is one way of doing the date range

/*505 Balances of patients with appointments on a given date*/
SELECT patient.LName,patient.FName,patient.EstBalance AS PatBal, g.BalTotal
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
INNER JOIN patient g ON patient.Guarantor=g.PatNum
AND DATE(appointment.AptDateTime)>= '2014-1-1'
AND DATE(appointment.AptDateTime)<= '2014-12-1'
AND AptStatus != 6
AND AptStatus != 3

drtmz

[Tom Zaccaria]

This adds a column for insurance coverage;

/*505 Balances of patients with appointments on a given date*/
SELECT patient.LName,patient.FName,patient.EstBalance AS PatBal, patient.HasIns, g.BalTotal
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
INNER JOIN patient g ON patient.Guarantor=g.PatNum
AND DATE(appointment.AptDateTime)>= '2014-1-1'
AND DATE(appointment.AptDateTime)<= '2014-12-1'
AND AptStatus != 6
AND AptStatus != 3

There are more elegant ways of doing this but this seems to be the simplest.

drtmz

[Tom Zaccaria]

Lastly, this would give you only the patients with insurance coverage:

/*505 Balances of patients with appointments on a given date*/
SELECT patient.LName,patient.FName,patient.EstBalance AS PatBal, patient.HasIns, g.BalTotal
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
INNER JOIN patient g ON patient.Guarantor=g.PatNum
AND DATE(appointment.AptDateTime)>= '2014-1-1'
AND DATE(appointment.AptDateTime)<= '2014-12-1'
AND patient.HasIns <>""
AND AptStatus != 6
AND AptStatus != 3

drtmz

[KevinRossen]

This is #505 from the Examples page

/*505 Balances of patients with appointments on a given date*/
SET @AptDate='2010-08-03';
SELECT appointment.AptDateTime,patient.LName,patient.FName,patient.EstBalance AS PatBal, g.BalTotal
FROM appointment
INNER JOIN patient ON appointment.PatNum=patient.PatNum
INNER JOIN patient g ON patient.Guarantor=g.PatNum
AND DATE(appointment.AptDateTime)=@AptDate
AND AptStatus != 6
AND AptStatus != 3;

Few things I want to change if possible (or let me know if there's already another report that has these features):
- Can I eliminate the appointment time? I don't need it and it's taking up valuable column space when I export this to a spreadsheet.
- Can I put in a range of dates?
- Can I add a column that notes whether or not the patient has insurance (whether it's single, double, whatever- just need to know if they have any), or can I exclude patients who do NOT have any insurance (that might be the easier way to go for what I need it for.)


Thanks in advance!

Tom gave you some good options. What are you looking to accomplish with the report? That might help me give you another level of customization.

[Janet Century]

Thanks for all the input! I'm going to play with all the variations to see what works!

We are doing beta testing of a new e-claim program for OneMind Health. Right now, to trigger the program to pick up a claim, you have to manually change the status to "Sent" instead of leaving it as "Waiting to Send", so while we're waiting for them to work out these quirks, we have no way of knowing whether or not the claim was actually submitted electronically, since according to Open Dental they're all "Sent". We are a fee-for-service office, so we rarely want to accept assignment but there are a few patients who we accept assignment for (e.g., if they have an HSA or FSA, or they're on an in-house financial plan.) The other quirk they have right now is that "Accept Assignment" is the default, unless we manually change it on every claim.

Basically, I need a report to run at the end of the day to see who has insurance, and who has a balance (on themselves or a someone else in their family), so I know who to accept assignment on. Some days I may not have time to run it, so if I can put in a date range to include past days, that would be great.

Hopefully they work these quirks out soon!

[KevinRossen]

I've got two versions for you. The first one only looks at appointments completed on the day that it's run and will only return patients who have insurance and have a patient or family balance.

Code: Select all

SELECT CONCAT(p.LName, ', ', p.FName) AS Patient, p.EstBalance AS PatientBal,g.BalTotal FamilyBal
FROM appointment a
INNER JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient g ON p.Guarantor=g.PatNum
WHERE a.AptStatus=2 AND DATE(a.AptDateTime)=CURDATE() AND p.HasIns<>"" AND (p.EstBalance>0 OR g.BalTotal>0)
ORDER BY p.LName;

This one returns the same information, but you can set the date range on the first two lines (you can set the start and end date to be the same date, too). I also added a column for the appointment date, since it will likely be helpful when searching for a date range.

Code: Select all

SET @StartDate='2014-12-08';
SET @EndDate='2014-12-13';
SELECT DATE(a.AptDateTime) AS AptDate, CONCAT(p.LName, ', ', p.FName) AS Patient, p.EstBalance AS PatientBal,g.BalTotal FamilyBal
FROM appointment a
INNER JOIN patient p ON a.PatNum=p.PatNum
INNER JOIN patient g ON p.Guarantor=g.PatNum
WHERE a.AptStatus=2 AND DATE(a.AptDateTime) BETWEEN @StartDate AND @EndDate AND p.HasIns<>"" AND (p.EstBalance>0 OR g.BalTotal>0)
ORDER BY AptDate, p.LName;

If you're interested in automating this shoot me a PM.

[Janet Century]

Those are very helpful! Thanks, Kevin!