Select Max Date

[lcfc]

Hi,

below is the SQL from a query we are trying to run.

There are many entries in the field LH_TIME_OUT, but we need it to select only the last date (BTW it is a datetime field). We have tried putting max in the 'total' row, but this does not work. Can anybody help:

SELECT dbo_F_Pat_Present.PP_ARRIVAL_DATE, dbo_F_Pat_Present.PP_LAST_NAME, dbo_F_Pat_Present.PP_GENDER, dbo_F_Pat_Present.PP_AGE_YRS, dbo_F_Visit_Types.VI_DESCRIPTION AS [Bed Bureau], dbo_F_Pat_Present.PP_TREAT_DOC_DATE, dbo_F_Consul_Link.CL_DATE_REQUESTED AS [Referral Time], Min(dbo_F_Consultations.CS_DESCRIPTION) AS [Referred to], dbo_F_Consul_Link.CL_DATE_ATTENDED AS [Ref time seen by specialist], dbo_F_Pat_Present.PP_DEP_READY_DATE AS [Decision to admint], dbo_F_Diagnosis.DI_DESCRIPTION, dbo_F_Nature_Inj.NA_DESCRIPTION, dbo_F_Body_Region.BR_DESCRIPTION, dbo_F_Pat_Present.PP_DELAY_TEXT, dbo_F_Campus.CA_CODE, dbo_F_Pat_Present.PP_DISCHARGE_DATE, dbo_F_Pat_Present.PP_IDENTIFIER, dbo_F_Pat_Present.PP_SEQU, dbo_F_Loc_Groups.LG_DESCRIPTION, dbo_F_Location_History.LH_TIME_OUT
FROM (((((((((((dbo_F_Pat_Present LEFT JOIN dbo_F_Unit ON dbo_F_Pat_Present.PP_UN_SEQU = dbo_F_Unit.UN_SEQU) LEFT JOIN dbo_F_Depart_Dest ON dbo_F_Pat_Present.PP_DE_SEQU = dbo_F_Depart_Dest.DE_SEQU) LEFT JOIN dbo_F_Campus ON dbo_F_Pat_Present.PP_CA_SEQU = dbo_F_Campus.CA_SEQU) LEFT JOIN dbo_F_Visit_Types ON dbo_F_Pat_Present.PP_VI_SEQU = dbo_F_Visit_Types.VI_SEQU) LEFT JOIN dbo_F_Consul_Link ON dbo_F_Pat_Present.PP_SEQU = dbo_F_Consul_Link.CL_PP_SEQU) LEFT JOIN dbo_F_Consultations ON dbo_F_Consul_Link.CL_CS_SEQU = dbo_F_Consultations.CS_SEQU) LEFT JOIN dbo_F_Body_Region ON dbo_F_Pat_Present.PP_BR_SEQU = dbo_F_Body_Region.BR_SEQU) LEFT JOIN dbo_F_Disposition ON dbo_F_Pat_Present.PP_DS_SEQU = dbo_F_Disposition.DS_SEQU) LEFT JOIN dbo_F_Diagnosis ON dbo_F_Pat_Present.PP_DI_SEQU = dbo_F_Diagnosis.DI_SEQU) LEFT JOIN dbo_F_Nature_Inj ON dbo_F_Pat_Present.PP_NA_SEQU = dbo_F_Nature_Inj.NA_SEQU) LEFT JOIN dbo_F_Location_History ON dbo_F_Pat_Present.PP_SEQU = dbo_F_Location_History.LH_PP_SEQU) LEFT JOIN (dbo_F_Locations LEFT JOIN dbo_F_Loc_Groups ON dbo_F_Locations.LO_LG_SEQU = dbo_F_Loc_Groups.LG_SEQU) ON dbo_F_Location_History.LH_LO_SEQU = dbo_F_Locations.LO_SEQU
GROUP BY dbo_F_Pat_Present.PP_ARRIVAL_DATE, dbo_F_Pat_Present.PP_LAST_NAME, dbo_F_Pat_Present.PP_GENDER, dbo_F_Pat_Present.PP_AGE_YRS, dbo_F_Visit_Types.VI_DESCRIPTION, dbo_F_Pat_Present.PP_TREAT_DOC_DATE, dbo_F_Consul_Link.CL_DATE_REQUESTED, dbo_F_Consul_Link.CL_DATE_ATTENDED, dbo_F_Pat_Present.PP_DEP_READY_DATE, dbo_F_Diagnosis.DI_DESCRIPTION, dbo_F_Nature_Inj.NA_DESCRIPTION, dbo_F_Body_Region.BR_DESCRIPTION, dbo_F_Pat_Present.PP_DELAY_TEXT, dbo_F_Campus.CA_CODE, dbo_F_Pat_Present.PP_DISCHARGE_DATE, dbo_F_Pat_Present.PP_IDENTIFIER, dbo_F_Pat_Present.PP_SEQU, dbo_F_Loc_Groups.LG_DESCRIPTION, dbo_F_Location_History.LH_TIME_OUT
HAVING (((dbo_F_Pat_Present.PP_ARRIVAL_DATE) Between #9/12/2004# And #9/13/2004#) AND ((dbo_F_Campus.CA_CODE)="AED") AND ((dbo_F_Loc_Groups.LG_DESCRIPTION) Is Not Null))
ORDER BY dbo_F_Pat_Present.PP_IDENTIFIER;

 

[genomon]

Couldn't begin to wade thru all this, but if you are looking for a "most recent" date, use TOP 1 and ORDER BY the date field DESC.

 

[r937]

i, too, have no desire to wade through that query

your HAVING conditions look like they belong in the WHERE clause

(access is notorious for building GROUP BY queries in design view with HAVING clauses that should be WHERE Clauses)

as for your max(date) question, you can probably (at the risk of further congesting the query) get what you want with a correlated subquery

... FROM ... whichevertable as XX
... WHERE LH_TIME_OUT =
(SELECT MAX(LH_TIME_OUT) FROM whichevertable
where somekey = XX.somekey)

where the XX is a table alias in the main query that allows you to distinguish the two copies of the whichevertable in the subquery

rudy

http://r937.com/

SQL Consulting