I'm only getting one record

[scottaherbst]

Working with CR10, SQL server DB.

In our business, we see patients and a lot of them get more than one service. They may or may not get a diagnosis for each service they get. They may also have more than one diagnosis for a single service. I want to write a report that returns the most recent diagnosis for each service. This is a distilled version of how it would look.

Patient Service_Code Date_of_diagnosis

00000 1 12/31/06
00000 2 7/4/2007
00000 4
11111 1 6/3/2005
11111 2 5/5/2006

I'm running the following SQL in a command line and its only returning one record, for a guy who was supposedly given the diagnosis in 1957.

Select episode_history.patid, episode_history.episode_number, episode_history.program_X_type_code, episode_history.program_X_type_value, history_diagnosis.date_of_diagnosis, FROM SYSTEM.episode_history episode_history LEFT OUTER JOIN SYSTEM.history_diagnosis history_diagnosis ON episode_history.PATID = history_diagnosis.PATID AND episode_history.episode_number = history_diagnosis.episode_number WHERE history_diagnosis.date_of_diagnosis = (SELECT MAX(history_diagnosis.date_of_diagnosis) FROM SYSTEM.history_diagnosis, episode_history WHERE history_diagnosis.patid = episode_history.patid AND history_diagnosis.episode_number = episode_history.episode_number)

Any ideas where I've gone off. I at least thought, if it was only going to return one record, that it would have been a record for the last day or two, given that I selected MAX

 

[dgillz]

In your group selection formula, enter this:

{Date_of_diagnosis}=maximum({Date_of_diagnosis},{Patient})

This presupposes that you are grouping by patient.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

I believe I responded to another of your posts with the solution. I think you need to be using aliases in your select subquery, since you have to create the patient grouping within the select as follows:

(
SELECT MAX(A.date_of_diagnosis) FROM SYSTEM.history_diagnosis A, episode_history B WHERE
A.patid = SYSTEM.history_diagnosis.patid and//creates group
A.patid = B.patid AND
A.episode_number = B.episode_number
)

-LB