Selecting an attending Doctor based on the MAX of a date field

[rfoote]

I have a table that has a list of dates and Doctor Names and Patient Numbers. This table contains a history of the attending doctors for a given patient. The current doctor is the one with the greatest date for a given patient.

I do not have access to SQL so I cannot create a stored procedure to return the desired data.

Any hints would be a great help

 

[fisheromacse]

can you create a SQL expression within crystal?
something like the below but with your specific table information.
(
select max("Fieldname")
from Tablename
where "table"."Field"="table2"."Field2"
)

 

[lbass]

I would write the SQL expression {%maxdate} like this:

(
select max("date")
from table A
where A."PatientNo"="table"."PatientNo"
)

Then in the record selection formula use:

{table.date} = {%maxdate}

A less efficient but alternative approach would be to go to report-> selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.patientno})

-LB