Below is the SQL of the view I am using for a report:
SELECT Pathis.patnumber AS caseid,;
ALLTRIM(Pathis.lastname)+", "+SUBSTR(Pathis.firstname,1,1) AS patient,;
Blank.describe AS staffmember, Pathis.comptime AS caseseconds,;
Pathis.in_or_out AS inoutstatus, Patproc.dcode AS proccode,;
Patproc.procname AS procname, Pathis.time AS starttime, Pathis.endtime,;
Pathis.or_date AS ordate, Pathis.d1 AS surgeon;
FROM doc!blank INNER JOIN doc!pathis;
INNER JOIN doc!patproc ;
ON Pathis.control = Patproc.patnumber ;
ON Blank.refdbf = Pathis.patnumber;
WHERE Pathis.or_date BETWEEN ?mq_start AND ?mq_end;
AND Blank.type = "S";
ORDER BY Blank.describe, Pathis.patnumber, Patproc.dcode
The report has two grouping: StaffMember and CaseID
Data looks something like this:
STAFFMEMBER
CASEID CASEINFO
proc1 procdesc1
proc2 procdesc2
Total Case: ### Total Procedures ###
My problem is I can't figure out how to get the variables setup right for getting the total number of cases. For example, the first group is for brenda, there are a total of 3 cases, 2 cases have 2 procedures listed in the detail line, and 1 has 1 procedure listed in the detail line.
So I want the Group Footer to reflect Total Cases: 3 Procs: 5
Thanks
Kirk
kkelly@cqisol.com
