×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Return value from table based on max date

Return value from table based on max date

Return value from table based on max date

(OP)
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




RE: Return value from table based on max date

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

CODE

(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)

CODE

{HEALTH_AND_SAFETY_VISIT.Visit_Date} = {%MyMaxDate} 

Bob Suruncle

RE: Return value from table based on max date

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

RE: Return value from table based on max date

(OP)
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

RE: Return value from table based on max date

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

RE: Return value from table based on max date

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close