Grouping by Max Record Value

[Eleyre]

I have a report that I'm attempting to write. Essentially, each patient can have multiple doctors attached to their record. I need to group by doctor, but I only want to pull in the doctor that discharged them (which would be the last doctor attached to the patient record). Otherwise the patient will show up in multiple different groups. Each doctor has their own line in the HSP_ATND_PROV table. I'd like to pull in only the last value for HSP_ATND_PROV.LINE. I'm using Crystal Reports 2008.

I've tried the SQL statement below, but it's eliminating patients that need to be in the report and I haven't figured out why. SQL is not my strongest point. If anyone could provide any assistance, I would greatly appreciate it:

SELECT

PAT_ENC_HSP.PAT_ENC_CSN_ID CSN
, CLARITY_SER.PROV_NAME Provider
, PAT_ENC.CONTACT_DATE
, PAT_ENC_HSP.HOSP_ADMSN_TIME
, PAT_ENC_HSP.ADMIT_CONF_STAT_C
, PAT_ENC_HSP.HOSP_DISCH_TIME
, PATIENT.PAT_NAME
, PATIENT.PAT_MRN_ID
, ZC_PAT_CLASS.NAME patient_class
, ZC_PAT_SERVICE.NAME service_line
, HSP_ATND_PROV.LINE
, CLARITY_DEP.DEPARTMENT_ID
, CLARITY_DEP.DEPARTMENT_NAME

FROM

PAT_ENC_HSP

LEFT OUTER JOIN PAT_ENC on (PAT_ENC_HSP.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID)
LEFT OUTER JOIN PATIENT on (PAT_ENC_HSP.PAT_ID = PATIENT.PAT_ID)
LEFT OUTER JOIN HSP_ATND_PROV on (PAT_ENC_HSP.PAT_ENC_CSN_ID = HSP_ATND_PROV.PAT_ENC_CSN_ID)
LEFT OUTER JOIN CLARITY_SER on (HSP_ATND_PROV.PROV_ID = CLARITY_SER.PROV_ID)
LEFT OUTER JOIN CLARITY_SER_2 on (CLARITY_SER.PROV_ID = CLARITY_SER_2.PROV_ID)
LEFT OUTER JOIN ZC_PAT_SERVICE on (CLARITY_SER_2.SERVICE_DEFAULT_C = ZC_PAT_SERVICE.HOSP_SERV_C)
LEFT OUTER JOIN ZC_PAT_CLASS on (PAT_ENC_HSP.ADT_PAT_CLASS_C = ZC_PAT_CLASS.ADT_PAT_CLASS_C)
LEFT OUTER JOIN CLARITY_DEP on (PAT_ENC_HSP.DEPARTMENT_ID = CLARITY_DEP.DEPARTMENT_ID)

WHERE

HSP_ATND_PROV.LINE =
(SELECT Max(t1.LINE)
FROM HSP_ATND_PROV t1
WHERE HSP_ATND_PROV.PAT_ID=t1.PAT_ID)

 

[Madawc]

If you do a left-outer join from A to B, but also do a conditional test on B, then it will eliminate anything where there is no B for A. Is that it?

If so, test for nulls first.

If you're using an SQL Command, you might do better feeding tables into a succession of temporary table.

Or you could ask at the SQL forum, forum183


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Madawc]

PS. If you're going to switch to another forum, please leave a message saying so. Double-posting is not approved of.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[pmax9999]

I'm no SQL expert but it looks to me like your code is selecting the Maximum value of HSP_ATND_PROV.LINE, where I think you need to selecting the Latest value, and which are unlikely to be the same. The latest can probably be calculated using the Time or ID field.

Hope this helps.

Pete.