Query Help
Query Help
Can someone give me a query to pull up all patients with treatment plan procedures (similar to query example #2), but instead of listing the patient 10x for 10 procedures, it just list the patient once? This will make printing a lot easier and less pages. I did query example #2, but it came up to 200+ pages. I just want the patient name and not all the procedures. TIA
Re: Query Help
Give this a try. Be sure and change your dates to the period you are looking for. I have not verified all TP's in the report, but it should give a good start.
Set @StartDate='2007-01-01', @EndDate='2008-04-31';
CREATE temporary table tp
SELECT patnum, (sum(procfee))as $Unscheduled, Date_format(datetp,'%Y-%m-%d') as TP_Date from procedurelog
WHERE (procstatus)=1
AND aptnum=0
GROUP by datetp;
SELECT patient.patnum, $Unscheduled, TP_Date from patient
left join tp on tp.patnum=patient.patnum
WHERE $Unscheduled>0
AND TP_Date>@StartDate
AND TP_Date<@EndDate
Group by patient.patnum
Set @StartDate='2007-01-01', @EndDate='2008-04-31';
CREATE temporary table tp
SELECT patnum, (sum(procfee))as $Unscheduled, Date_format(datetp,'%Y-%m-%d') as TP_Date from procedurelog
WHERE (procstatus)=1
AND aptnum=0
GROUP by datetp;
SELECT patient.patnum, $Unscheduled, TP_Date from patient
left join tp on tp.patnum=patient.patnum
WHERE $Unscheduled>0
AND TP_Date>@StartDate
AND TP_Date<@EndDate
Group by patient.patnum
-
richardwaite
- Posts: 57
- Joined: Thu Nov 15, 2007 6:51 am
Re: Query Help
The DISTINCT statement should do what you want. Something to the effect of "SELECT DISTINCT lname, fname FROM patient WHERE <whatever you need to filter out patients who have TP'd procedures>". DISTINCT works on the entire returned row, so only list those columns which should be unique in the query, or it won't do what you think it will.