trouble with a command line that selects MAX 1

[scottaherbst]

I'm working with CR10 on a SQL server DB.

I'm working with patient records and I want to bring up the most recent diagnosis for each different service they receive. I wrote the command line:

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, history_diagnosis.axis_i_diag_value_1, history_diagnosis.axis_ii_diag_value_1, history_diagnosis.axis_III_diag_value_1, history_diagnosis.axis_IV_prim_sup_grp_code, history_diagnosis.axis_IV_soc_env_code, history_diagnosis.axis_IV_educ_code, history_diagnosis.axis_IV_occ_code, history_diagnosis.axis_IV_housing_code, history_diagnosis.axis_IV_housing_value, history_diagnosis.axis_IV_eco_value, history_diagnosis.axis_IV_health_serv_value, history_diagnosis.axis_IV_legsys_value, history_diagnosis.axis_IV_other_prob_value, history_diagnosis.diagnosis_type_value, history_diagnosis.diagnosis_type_code, history_diagnosis.axis_v_gaf_code 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 A, episode_history B WHERE a.patid = b.patid AND a.episode_number = b.episode_number)

And after a few minutes got a general server error. Then I tried a version of the same thing but changed the A's and the B's specifying tables back to the original table names. So now it looks like this:

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, history_diagnosis.axis_i_diag_value_1, history_diagnosis.axis_ii_diag_value_1, history_diagnosis.axis_III_diag_value_1, history_diagnosis.axis_IV_prim_sup_grp_code, history_diagnosis.axis_IV_soc_env_code, history_diagnosis.axis_IV_educ_code, history_diagnosis.axis_IV_occ_code, history_diagnosis.axis_IV_housing_code, history_diagnosis.axis_IV_housing_value, history_diagnosis.axis_IV_eco_value, history_diagnosis.axis_IV_health_serv_value, history_diagnosis.axis_IV_legsys_value, history_diagnosis.axis_IV_other_prob_value, history_diagnosis.diagnosis_type_value, history_diagnosis.diagnosis_type_code, history_diagnosis.axis_v_gaf_code 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)

Now, I'm not getting anything. I let it run for almost 2 hours and it never finished. I stopped it and ran it over and now its been going for about 1/2 hour. To provide some context, our DB is really slow and clunky, so its not irregular for a report such as this to take 10-15 minutes to run (depending on the time of day and traffic), but its extremely unusual that it take this long, so I'm thinking there is something wrong with my command. Any ideas?

 

[lbass]

Try:

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

-LB

 

[scottaherbst]

Actually, the second try eventually worked, though it took a really, really long time. After I stopped it the first time, I started it again and then just let it keep running and went on to other things. 2.5 hours later I left work and kept it running. When I got in this morning, it was done. So, for some reason its just taking an inordinate amount of time to run. Thanks for the help, though.

 

[scottaherbst]

Ok - so it mostly works, however, when a client has more than one service, it is filling in the most recent date in each of the service areas. This is a sample of how it looks

Client Service # Date
11111 12 12/31/2006
11111 11 12/31/2006
11111 9 12/31/2006

What I'd like for it to do is this

11111 12 12/31/2006
11111 11
11111 9 6/30/2005

Oh - and here's the full command that I am currently using.

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, history_diagnosis.axis_i_diag_value_1, history_diagnosis.axis_ii_diag_value_1, history_diagnosis.axis_III_diag_value_1, history_diagnosis.axis_IV_prim_sup_grp_code, history_diagnosis.axis_IV_soc_env_code, history_diagnosis.axis_IV_educ_code, history_diagnosis.axis_IV_occ_code, history_diagnosis.axis_IV_housing_code, history_diagnosis.axis_IV_housing_value, history_diagnosis.axis_IV_eco_value, history_diagnosis.axis_IV_health_serv_value, history_diagnosis.axis_IV_legsys_value, history_diagnosis.axis_IV_other_prob_value, history_diagnosis.diagnosis_type_value, history_diagnosis.diagnosis_type_code, history_diagnosis.axis_v_gaf_code 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(A.date_of_diagnosis)
FROM SYSTEM.history_diagnosis A, episode_history B
WHERE
A.patid = history_diagnosis.patid and
A.patid = B.patid AND
A.episode_number = B.episode_number
)

 

[lbass]

Then you have changed the requirement. The report currently should be returning ONLY the most recent date (and corresponding service) per patient. So how does the most recent date factor into this at all?

-LB

 

[scottaherbst]

Not sure if I get what you're asking. Let me see if I can be more clear.

A client can get more than one service and any time they start a new service it is assigned an EPISODE_NUMBER. They may or may not be given a diagnosis with each service (sometimes they got a diagnosis with a different service and then were routed to the new service b/c of it). Also, they may get more than one diagnosis per service if part of the service requirements are ongoing evaluation. When that's the case, I only want to check the most recent one. What I'm doing this for is b/c the law requires the client to have a complete diagnosis that is made up of several data elements and when an audit comes up, they'll want to see that the most recent one for each service (for services where it is required) is complete.

Did that even remotely answer your question?

 

[scottaherbst]

Oh - also - the report isn't returning only the last date and corresponding service. For a lot of clients, it returns several service records.

 

[lbass]

The problem is that I'm not familiar with your database. The above formula should return only the maximum record per patient per episode. Your sample data doesn't reference the {table.fields} used in your SQL query, so I can't tell what column is what. For example, you refer to a service #, but there is none in your SQL query.

Please provide a sample that is large enough to show how the date works in the situations you describe (before selection on the most recent date) and then show the dates you would expect to be returned--but please use actual field names.

-LB

 

[scottaherbst]

Sure. Thanks.

PATID EPSIODE_NUMBER DATE_OF_DIAGNOSIS
11111 12 12/31/2006
11111 11 12/31/2006
11111 9 12/31/2006
22222 1 6/5/2004
33333 2 6/6/2007

Does that give you enough of a picture. I haven't included the other fields b/c those are the elements of the diagnosis and they won't actually appear in the report - I'm only referencing them in a formula that calculates whehter the diagnosis is complete or not.

 

[lbass]

This is the detail level sample? So what do you expect to see for your report results?

-LB

 

[scottaherbst]

Of course. This is what I'm hoping to see.

PATID EPSIODE_NUMBER DATE_OF_DIAGNOSIS
11111 12 12/31/2006
11111 11 11/7/2004 111111 9 1/3/2003
22222 1 6/5/2004
33333 2 6/6/2007

 

[lbass]

Then why are you asking for the most recent date? You have not shown a sample that explains where the most recent date fits in. Why not just add in the datefield and remove the requirement for the most recent date? If there IS a need to select based on a most recent date, you need to show a sample that displays the issue.

-LB

 

[scottaherbst]

So here is what it would look like if I pulled all the dates from the database:

PATID EPISODE_NUMBER DATE_OF_DIAGNOSIS
1111 1 8/9/2007
1111 1 6/3/2006
1111 1 6/1/2004
1111 2 8/5/2005
1111 2 12/13/2003

Here's how I would want the same data to look in the report:


PATID EPISODE_NUMBER DATE_OF_DIAGNOSIS
1111 1 8/9/2007
1111 2 8/5/2005

In other words, I want the most recent date for each episode, not just the most recent date overall. Clear?

 

[lbass]

I thought that the expression WOULD return the maximum per patID per episode. I can't really test this, but try:

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

-LB

 

[scottaherbst]

Still a no. It is selecting the maximum date and if there are two different episodes with a diagnosis on the same day, it returns two, buts its not returning for anything but the maximum date, regardless of whether there are other episodes for which a diagnosis was and should have been given. Thanks for taking such a long look at it. Any other ideas?