Query Help Needed

[tcdc]

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

[nathansparks]

Here you go

/*Patient list of Sum of the Fees of all Treatment planned and all scheduled procedures*/
DROP TABLE IF EXISTS tmp1; /*Create a temp table containing summary info by procedure*/
CREATE TABLE tmp1
SELECT patient.PatNum, sum(ProcFee) AS '$Scheduled', 0 AS '$TP' FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
AND AptStatus=1 AND PatStatus=0 GROUP BY patient.PatNum;
INSERT INTO tmp1(PatNum, $Scheduled, $TP)
SELECT patient.PatNum, 0 AS '$Scheduled', sum(ProcFee) AS '$TP' FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
LEFT JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE (isnull(ap.aptnum) OR AptStatus=6 OR AptStatus=3)
AND PatStatus=0 GROUP BY patient.PatNum;
/*Display Results*/
SELECT PatNum, SUM($Scheduled), SUM($TP) FROM tmp1
GROUP BY PatNum HAVING SUM($TP)>0
ORDER BY SUM($TP) DESC;
DROP TABLE IF EXISTS tmp1;

Cheers,
Nathan