Query question

[CMJ]

I was wondering if anybody could help me with a query. We currently have handicapped residents who come in for regular 6 month recalls and all have separate accounts but all have the same mailing address and same phone number. My question is this. Could someone tell me how to set up a query for the following:
1. patient's name
2. current address (all residents have the identical address)
3. current phone ( all residents have the identical phone)
I HAVE STEPS 1-3 FIGURED OUT...I need help with 4 & 5.
4. when recall is due
5. recall is scheduled for (date)

I would like to be able to pull a query for this group home and tell the staff when each resident is due for a recall or to let the staff know when the resident is scheduled for their recall.

[atd]

Not sure about #5, but I can help you with #4 without using a query, which may also make #1-3 easier.
1) Turn on the Public Health features under the Easy Options.
2) Add a Site under the Lists->Sites.
3) Assign each of the residents to that Site in the edit patient window in the Public Health section.
4) Use the recall list and filter by that site by choosing it from the dropdown list. This will also work for the planned tracker and unscheduled lists. If that same dropdown was added to the confirmations list you could use that to find scheduled recall appointments which would solve #5 - that's currently not available though.
We're using these features for the nursing home and group home patients we treat at their facilities with our mobile equipment - but I don't see why you couldn't use it even if they are coming into your clinic. I guess it depends on the number of patients and how often you need to access this information if it's worth setting this up or not.

[CMJ]

Thank you ATD for your helpful comments. It worked! It help to find out who is due for their recall.
RE: # 5 -- I can see what you mean about a drop down feature on the confirmation list that would make life easier

Thanks again!
Connie

[atd]

I'll add it to the feature request list and then we can both vote for it.

[fmr]

Could someone tell me how to set up a query for the following:

I'm looking for a list of patients that have treatment needs that have not been completed, and have insurance benefits remaining this calender year,. We'd like to send cards out to these people in hopes of getting them in before the end of the year. There is a query that lists all patients with remaining benefits, but many don't have treatment planned work.

Thank you!

[drtech]

This one gives you the tx plan amt too...


CREATE TEMPORARY TABLE tempused(
PatPlanNum mediumint unsigned NOT NULL,
AmtUsed double NOT NULL,
PRIMARY KEY (PatPlanNum));
CREATE TEMPORARY TABLE tempplanned(
PatNum mediumint unsigned NOT NULL,
AmtPlanned double NOT NULL,
PRIMARY KEY (PatNum));
CREATE TEMPORARY TABLE tempannualmax(
PlanNum mediumint unsigned NOT NULL,
AnnualMax double NOT NULL);
INSERT INTO tempused
SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayAmt,0))
FROM claimproc
LEFT JOIN patplan ON patplan.PatNum = claimproc.PatNum
AND patplan.PlanNum = claimproc.PlanNum
WHERE claimproc.Status IN (1, 3, 4)
AND claimproc.ProcDate BETWEEN makedate(year(curdate()), 1)
AND makedate(year(curdate())+1, 1) /*current calendar year*/
GROUP BY patplan.PatPlanNum;
INSERT INTO tempplanned
SELECT patient.PatNum, SUM(procedurelog.ProcFee)
FROM procedurelog
LEFT JOIN patient ON patient.PatNum = procedurelog.PatNum
WHERE procedurelog.ProcStatus = 1 /*treatment planned*/
GROUP BY patient.PatNum;
INSERT INTO tempannualmax
SELECT benefit.PlanNum, benefit.MonetaryAmt
FROM benefit, covcat
WHERE covcat.CovCatNum = benefit.CovCatNum
AND benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND covcat.EbenefitCat=1
AND benefit.MonetaryAmt <> 0
ORDER BY benefit.PlanNum;
SELECT patient.LName, patient.FName,
LPAD(FORMAT(tempannualmax.AnnualMax, 2), 12, " ") AS "Annual Max",
LPAD(FORMAT(tempused.AmtUsed, 2), 12, " ") AS "Amount Used",
LPAD(FORMAT(tempannualmax.AnnualMax-tempused.AmtUsed, 2), 12, " ") AS "Amt Remaining",
LPAD(FORMAT(tempplanned.AmtPlanned, 2), 12, " ") AS "Treatment Plan"
FROM patient,tempannualmax,patplan,tempused,tempplanned
WHERE patient.PatNum=patplan.PatNum
AND tempannualmax.PlanNum=patplan.PlanNum
AND tempused.PatPlanNum=patplan.PatPlanNum
AND tempplanned.PatNum=patient.PatNum
AND tempannualmax.AnnualMax>0
AND tempplanned.AmtPlanned>0
AND tempannualmax.AnnualMax-tempused.AmtUsed>0
ORDER BY tempplanned.AmtPlanned DESC;

[fmr]

Thank you drtech!!

[jordansparks]

The above query is obviously absurd. No ordinary user should have to deal with such jibberish. We may be starting this too late in the year, but we are going to attempt to make the above query into an included report.

[jordansparks]

While adding the query to the program, I discovered a critical flaw. It was only showing patients who had been in within the last year. There are a lot more patients that have treatment that have not used any of their benefits yet. Here is the improved version. You won't need this once you switch to version 6.1.
It's all one big long query, in spite of the extra blank lines.

DROP TABLE IF EXISTS tempused;
DROP TABLE IF EXISTS tempplanned;
DROP TABLE IF EXISTS tempannualmax;

CREATE TABLE tempused(
PatPlanNum mediumint unsigned NOT NULL,
AmtUsed double NOT NULL,
PRIMARY KEY (PatPlanNum));

CREATE TABLE tempplanned(
PatNum mediumint unsigned NOT NULL,
AmtPlanned double NOT NULL,
PRIMARY KEY (PatNum));

CREATE TABLE tempannualmax(
PlanNum mediumint unsigned NOT NULL,
AnnualMax double NOT NULL,
PRIMARY KEY (PlanNum));

INSERT INTO tempused
SELECT patplan.PatPlanNum,
SUM(IFNULL(claimproc.InsPayAmt,0))
FROM claimproc
LEFT JOIN patplan ON patplan.PatNum = claimproc.PatNum
AND patplan.PlanNum = claimproc.PlanNum
WHERE claimproc.Status IN (1, 3, 4)
AND claimproc.ProcDate BETWEEN makedate(year(curdate()), 1)
AND makedate(year(curdate())+1, 1) /*current calendar year*/
GROUP BY patplan.PatPlanNum;

INSERT INTO tempplanned
SELECT PatNum, SUM(ProcFee)
FROM procedurelog
WHERE ProcStatus = 1 /*treatment planned*/
GROUP BY PatNum;

INSERT INTO tempannualmax
SELECT benefit.PlanNum, benefit.MonetaryAmt
FROM benefit, covcat
WHERE covcat.CovCatNum = benefit.CovCatNum
AND benefit.BenefitType = 5 /* limitation */
AND benefit.TimePeriod = 2 /* calendar year */
AND covcat.EbenefitCat=1
AND benefit.MonetaryAmt <> 0
ORDER BY benefit.PlanNum;

SELECT patient.LName, patient.FName,
tempannualmax.AnnualMax $AnnualMax,
tempused.AmtUsed $AmountUsed,
tempannualmax.AnnualMax-IFNULL(tempused.AmtUsed,0) $AmtRemaining,
tempplanned.AmtPlanned $TreatmentPlan
FROM patient
LEFT JOIN tempplanned ON tempplanned.PatNum=patient.PatNum
LEFT JOIN patplan ON patient.PatNum=patplan.PatNum
LEFT JOIN tempused ON tempused.PatPlanNum=patplan.PatPlanNum
LEFT JOIN tempannualmax ON tempannualmax.PlanNum=patplan.PlanNum
AND tempannualmax.AnnualMax>0
WHERE tempplanned.AmtPlanned>0
AND AnnualMax > 0
ORDER BY tempplanned.AmtPlanned DESC;
DROP TABLE tempused;
DROP TABLE tempplanned;
DROP TABLE tempannualmax;