Return most recently dated record PLUS master record from a table

[petejigsaw]

I am working on a report to pull pricing data from a table.

The table contains records of pricing data for each product, as follows:

Prod_Code Promo_No. Effective_Date Price
BERGCHSTBROW 444 299.00
BERGCHSTBROW 001 01/10/2011 259.00
BERGCHSTBROW 001 01/12/2011 299.00
BERGCHSTBROW 001 01/01/2012 249.00

In the above sample data, the record with 'Promo_type' set to 444 is the master price record (Comparison highest price charged for the product), and always needs to be returned (there is only one of these records for every 'Product_code'.

However, I also need to return the most recent record where 'Promo_type' is equal to 001 - i.e. the record with the most recent 'Effective_date'.

Once I have these records in the report, I then need to amalgamate the 2 records into a single line (probably by grouping) to give me a line showing the product_code, comparison highest ever price and the most recent and therefore current price for the product.

I have looked at the

http://www.tek-tips.com/viewthread.cfm?qid=560177

but can't seem to get either my head or my requirements to fit it!

Any help gratefully received.

Thanks

 

[hilfy]

You'll need two copies of your table. In the Database Expert, when you add a table the second time, Crystal will ask you if you want to alias the table. Click on Yes (or OK, I don't remember which shows up...) and you'll see a second copy of the table with "_1" at the end of the name.

For this example, I'll call the tables Pricing and Pricing_1.

1. Join from Pricing to Pricing_1 on Prod_Code. If you can have a master record with no corresponding 001 record, make this a left outer join.
2. Filter Pricing on Promo_No = 444.
3. Filter Pricing_1 on Promo_No
4. Group on Pricing on Prod_Code. Put the master record data in the group header.
5. Group on Pricing_1 on Prod_Code and set the group filter to something like {Pricing_1.Price} = Max({Pricing_1.Price}). Put the detail data in the group header.

Something like this should get you close to what you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

You could alternatively insert a group on {table.prodcode}. Then add a sort on effectivedate->descending. Insert a maximum on price and drag the result into the group header and label it "Maximum Price". Drag the price field into the group header and label it "Current Price". Create a formula {@MasterPrice} like this:

if {table.promono}=444 then {table.price}

Place this in the detail section and insert a maximum on it, drag the result into the group header, and label it "Master Price". Then suppress the detail section.

I didn't test this, but think it should work.

-LB