Date selection help

[CIBS]

Hi,

I am using CR 9

I am putting a creating a report based on our stock and I want to compare the 2008 price with the 2009 price.

In the table we can update the price and insert the effictive date of the new price. So one product can have many different values over the time it is on our system. For example

Product - 52.9423 - Nut Bolt

Effective date 31/02/06 Price £2.01
Effective date 31/03/07 Price £2.65
Effective date 03/03/08 Price £1.12
Effective date 01/04/09 Price £1.50

When I insert the price field it just displays the last price (2009)

I want to write a formula to display the last value before a required date (In this case 01/04/09). what I have tbare in mind is that the effictive date could be any date. so in this case I want it to show the following

Product - 52.9423 - Nut Bolt

Effective date 03/03/08 Price £1.12

What I have to bare in mind as will is that I can not use a parameter as this would restrict the parts so in this case the 2009 price would have to be displayed but in a different colour

List of fields are as follows

Table (Product)

Fields

Part_ID
Part_Description
Effective_Date
Net_Price

Any Help much appriecated

Kind Regards

Stephen

 

[IanWaterman]

Joining your effective date table into the report will cause multiple duplications of data making any summaries really difficult.

YOu will be best served to build a view on your database which finds the appropriate price for each entry.

You do not state which DB, if Oracle there is a function LAG which allows you to compare a records to a preceding record. Or if you can not produce views you could build a command to do the same.

Ian

 

[CIBS]

Hi

Sorry about that,

I am using Oracle

 

[lbass]

It is unclear whether there are two tables or only one. If there is only one, and if it always displays only the most recent price regardless of effective date shown, then I don't know how you would be able recover the earlier price.

When I insert the price field it just displays the last price (2009)

-LB