×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Retrieving pricing for the most current Effective date

Retrieving pricing for the most current Effective date

Retrieving pricing for the most current Effective date

(OP)
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?

RE: Retrieving pricing for the most current Effective date

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 --> Group_Selection_Formula

{Table.Effective_Date} = Maximum({Table.Effective_Date}, {Table.SKU}) 

Hope this helps.

Cheers, Pete

RE: Retrieving pricing for the most current Effective date

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.

RE: Retrieving pricing for the most current Effective date

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

RE: Retrieving pricing for the most current Effective date

(OP)
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.

RE: Retrieving pricing for the most current Effective date

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

RE: Retrieving pricing for the most current Effective date

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.

RE: Retrieving pricing for the most current Effective date

(OP)
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.

CODE --> SQL

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.

RE: Retrieving pricing for the most current Effective date

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close