Return value from table based on max date

[n2nuk]

Hi there,

I have a table of health and safety inspections by organisation. most organisations will have had more than one H&S inspection. I would like to return the latest H&S result for each organisation. I understand that grouping and sorting the columns by date will get work as well, but i'd like to a SQL expression to do this, so far i can return the latest visit_date for each organisation, but what i want is to return the H&S status.

here's the SQL that am currently using.
(select max(DBA.HEALTH_AND_SAFETY_VISIT.Visit_Date)
from DBA.HEALTH_AND_SAFETY_VISIT
where ENTITY.customer_ref = DBA.HEALTH_AND_SAFETY_VISIT.customer_ref)

how can I return the latest visit_status based on the latest date that is recorded on HEALTH_AND_SAFETY_VISIT table? I am using a sybase db environment.

Many thanks

 

[BobSuruncle]

I think that you ought to be able to do this fairly simply.
Create your report as usual, connecting to you Sybase db.
Create a new SQL Expression. Give it a Name - MyMaxDate

(SELECT Max(V.Visit_Date) FROM HEALTH_AND_SAFETY_VISIT V)

In your record Selection Formula, add it to the existing selection formula (if any)

{HEALTH_AND_SAFETY_VISIT.Visit_Date} = {%MyMaxDate}

Bob Suruncle

 

[lbass]

Try this SQL expression:

(
select max(a.Visit_Date)
from DBA.HEALTH_AND_SAFETY_VISIT a
where a.customer_ref=DBA.HEALTH_AND_SAFETY_VISIT.customer_ref
)

How you can reference a field within the max()function varies by CR version, so If this doesn't compile, remove the "a" within the summary only, like this:

(
select max(Visit_Date)
from DBA.HEALTH_AND_SAFETY_VISIT a
where a.customer_ref=DBA.HEALTH_AND_SAFETY_VISIT.customer_ref
)

This will give the maximum date per customer. Then do as Bob suggested in your selection formula:

{DBA.HEALTH_AND_SAFETY_VISIT.Visit_Date} = {%MyMaxDate}

I'm not sure whether the "DBA." is necessary here, but I added it in because you used it in your version of the SQL expression.

-LB

 

[n2nuk]

Hi bob and lbass

Thanks for input, this returns the data, however is it possible to return the H&S status from the SQL expression? that way i can exclude the H&S visit table from my report. I think this is reason the table owner has to be prefixed on the tables that are not included in the report. I am using the customer_ref to link the table from my report to the H&S visit table from the sql expression.

Many thanks

 

[lbass]

You need the table in the report in order to use the field in the selection formula, and I don’t think the SQL expression will compile without it.

-LB

 

[lbass]

PS. The link within my SQL expression is from the alias table to the table itself. This is what returns the maximum per customer—otherwise you’d just get the maximum across all customers.

-LB