Retrieving pricing for the most current Effective date 1

[klgoist]

I have a table that is storing pricing with an effective-date, but no end-date. I need to get the pricing for the most recent effective date and I have been struggling to find a solution in CR2016. Below I am providing an example of two SKU's with different effective dates and they also have a Future date.

Data example
SKU - Effective date - Price
SKU1 - 2/1/2021 - 415.00 (Old Price)
SKU1 - 4/15/2021 - 425.00 (Current Price)**
SKU1 - 8/1/2021 - 435.00 (Future Price)
SKU2 - 1/1/2020 - 125.00 (Old Price)
SKU2 - 6/1/2020 - 150.00 (Old Price)
SKU2 - 12/1/2020 - 155.00 (Current Price)**
SKU2 - 8/1/2021 - 175.00 (Future Price)

I need to be able to retrieve just the most recent effective date. What would be the best way to do this?

 

[pmax9999]

The basic approach would be to exclude the future dated prices with the Record Selection formula and return only the current prices using a Group Selection formula to select the prices with the latest (maximum) date.

I did the formulas as follows (note, this approach will require a group on SKU):

[Code Record_Selection_Formula]
{Table.Effective_Date} <= CurrentDate
[/Code]

[Code Group_Selection_Formula]
{Table.Effective_Date} = Maximum({Table.Effective_Date}, {Table.SKU})
[/Code]

Hope this helps.

Cheers, Pete

 

[pmax9999]

It is now a week since I provided a possible solution to your query and just wanting to be sure you have been able to resolve the issue raised.

It is usual to provide feedback on suggested approaches. This allows us to provide further assistance if your issue has not been resolved, or to indicate to others experiencing similar issues that the solution provided works.

Regards, Pete.

 

[klgoist]

I am working on it this week and will let you know. Thanks.

 

[klgoist]

pmax9999,
This almost works, I found an issue with the way my data on my report is grouped and thus it is causing Summed Pricing to be much higher than it should be. I am still working on this solution since it has gotten me the closest to the solution I want.

 

[klgoist]

Just an update to let you know that this solution is not going to work for the report I am building.

 

[pmax9999]

Perhaps you could explain why. There is pretty much always a solution (Command, SQL Expression, Sub-Report) but you will need to provide detail if you want help to get there.

 

[klgoist]

I have SKU's that some are "KIT's" (made up of multiple components) and some are not. My crystal report drills into the KIT sku's in-order to sum the price for components and simply return the price when its not a kit.

I have been working with the "Add Command" function in crystal reports, and the following SQL Query has gotten me the closest to my desired results, but I need to figure out how to return the first row of each group, and the "Row_Number()" option is not available within the OpenEdge Progress DB structure.

SELECT *
FROM
(
Select *
FROM "APPRISE"."PUB"."product-price" p
WHERE  (p."location-key"='00000001' OR p."location-key"='00000008') AND 
    (p."list-price-sequence" = '1' OR p."list-price-sequence" = '3') AND
    p."effective-date"<= curdate() AND 
    p."system-id"='Aamerica'
ORDER BY p."product-key", p."location-key", p."list-price-sequence", p."effective-date" 
DESC
    ) t

I know it's something simple I'm missing.

 

[pmax9999]

A Command looks to be the best approach and at this stage it is really an issue around your SQL query, rather than Crystal Reports.

I'd suggest you look for a forum that specialises in the SQL flavour you are working with.

Sorry I can't offer anything further.

Regards