Latest test record for each well in table of well tests 2

[langgang]

I need the information from the latest well test for each well in the database. The wells have multiple tests, and not in any order (one well may have 3 tests before another have 2 tests, so overall ranking doesnt' work). I have a well table and a well test table. Any ideas?

 

[suj2000]

Hi langgang,

Could you describe the structure of your tables and some sample data to understand your query better.

Thanks,
Suj

 

[langgang]

I have a table containing the test date, well number, and test results fields. Whenever a well is tested a new record is added to the table. Some wells can be tested multiple times before others are tested at all. I want to list the latest single well test for each well and show the date of the test and what the results were. I have done this in freehand sql, but I wanted to do it within Business Objects

 

[blom0344]

I's say use 2 dataproviders for this one:

1. DP1: Fetches the well number and test date based on the last date for the well:

Select well_number,max(test_date) from ........
group by well_number

2. DP2:

Fetches all data from test_table

Link the DP's over well_number and use a global filter on the test_date from DP1 to suppress all unwanted data.

So the only object needed within the universe is :

max(test_date)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

This one is classic example or case to Use Correlated Subquery. One can do this either at the Universe Level or do in the Reporter module. Following is how to do in Reportrer Module.

Place Well, Date of Test and Results object in the Results pane and then in the Condition pane place Date of Test and then "Equal To" Operator and choose Calculation and there select Date of Test as the Object and choose Max for the Agg Fn. and then say evaluate w.r.t each Well.

I'm typing that on top of my head. So make the necessary changes. Infact the wizard should be user friendly.

In case you need further assistance drop a note.

Good Luck
Sri

 

[langgang]

Thank you both for responding. Sri, your suggestion was exactly what I was looking for and it worked!

Kudos!

 

[job1]

Hey I have a question, Is it possible to find the second maxium record....

Thanks