Retrieving Data with most current date

[jenni]

In our data wharehouse, each product has a price with an effective date. If I want to pull the most current price for all products, how do I write the query in impromtu?

Each product may have had a price change at different periods in time (even various years). How is it possible to retrieve one data line per product with the most current price?

Thank you in advance!
Jennifer

 

[drlex]

jennifer,
one way would be to use the summary filter for minimum(<today>-<effective_date>) for <product>.
To run efficiently, this will require the use of whichever date function returns the database's current date (look for the little grey cylinder symbol next to the function name) - eg curdate() or now() for <today> and the product effective date being an indexed field in the table.
lex
sorry to be brief, but just on way out of the door

 

[griffindm]

Jenni,

Create an Impromptu report that groups on the product id and returns the max(effective_date) and avg(unit_price). Then create a summary filter where the Max(effective_date) = effective_date. This will give you what you need. If you are going to use this result in other queries where the forced grouping on product id would conflict, then create a database view doing the same thing and add it to your catalog.

Hope this helps,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[drlex]

Jennifer,
apologies - Dave's method (and explanation) is better.
we front-load pricing here, so need to exclude future pricing that would be retrieved in a maximum situation, and consider a minimum interval (excluding negatives).
I neglected to remove this consideration when making my suggestion. I think I'd better think more before posting!
lex

 

[jenni]

Thank you all for your help, however, I am still not getting the results I need.

Using the max command is returning only those products and their prices with the most current date - as opposed to returning all products in the data wharehouse and their most current price.

Am I doing something wrong, or is there another way to retrieve this data? I also do not want to average the prices, I need whatever price is the most current for each product, not an average. The fields I am dealing with are product id, cost price, & cost price effective date. Any new suggestions?

Thanks,
Jenni

 

[drlex]

Jennifer,
sounds like you haven't grouped on the product-id, so that the maximum(effective date) is the latest date for all products.
lex

 

[jenni]

I've grouped the product id, but I'm still getting the same results.

Example:
Let's say this is my data...

Product Id Cost price Cost price effective date
1 1.00 01/03/2002
1 1.50 10/10/2003
2 5.50 06/18/2003
2 5.75 10/10/2003
3 3.00 01/01/2001
3 3.25 09/10/2003

The results I'm getting now would be:
1 1.50 10/10/2003
2 5.75 10/10/2003

Group Product Id, summary filter max(cost price effective date)=cost price effective date.

The results I would like to get are:
1 1.50 10/10/2003
2 5.75 10/10/2003
3 3.25 09/10/2003

I am in desperate need of help! I appreciate all your suggestions. What am I missing?

Thanks again in advance!
Jenni

 

[nagrajm]

Jenni

Dave wanted you phrase the statement in the summary filter as

max(cost price effective date) for Product_id = cost price effective date

Nagraj

 

[griffindm]

Jenni,

To help us all understand what Impromptu is doing, could you go to the menu command Report | Query | Profile, then select the SQL button, and copy and paste the SQL here so we can see it?

Thanks,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com