×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Current pricing record

Current pricing record

Current pricing record

(OP)
I have an SQL Database(Progress Openedge) and I am using Crystal reports XI r2

I have a table(prod_price) with the following sample data, and I am trying to do an "Add Command" in Crystal, where I am only grabbing the records for the currently active price records. In the future, as prices are updated there could be several pricing records (5, 6, 10, 20) per product/location.
Prod-Key Loc-Key Seq effective-date price
00010262 00000001 1 01/01/00 240.00
00010262 00000001 1 07/27/18 248.00
00010262 00000001 1 09/01/18 250.00

00010262 00000003 1 01/01/00 245.00
00010262 00000003 1 07/27/18 253.00
00010262 00000003 1 10/01/18 255.00

00010262 00000004 1 01/01/00 250.00
00010262 00000004 1 07/27/18 258.00
00010262 00000004 1 08/15/18 260.00

The data is grouped by Prod-key and then by Loc-key

with only 1 or 2 pricing records for each Product/Location, I was able to use "Report-->Selection Formulas-->Group" and use a formula like below to filter, but I am thinking to the future and how to keep my crystal report working as future pricing is added to the DB.
{prod_price.effective-date} = maximum({prod_price.effective-date},{prod_price.loc-key})

Can anyone help?



RE: Current pricing record

Your group selection formula will work regardless of the number of new records. But, if you only want to return the most recent records and avoid using group selection, then you could build the selection into the command (which I guess is what you want to do). I'm not familiar with your database, so don't know the correct punctuation/syntax, but it would look something like this with an Oracle database:

select "prod_price"."prod-key",
"prod_price"."loc-key",
"prod_price"."effective-date",
"prod_price"."price",
(select max(a."effective-date")
from "prod_price" a
where a."prod-key"="prod_price"."prod-key" and
a."loc-key"="prod_price"."loc-key"
) "maxdt"

from "prod_price" "prod_price"

where "prod_price"."effective-date"=
(select max(a."effective-date")
from "prod_price" a
where a."prod-key"="prod_price"."prod-key" and
a."loc-key"="prod_price"."loc-key"
)

You could, as an alternative, create a max date as a SQL expression, using the same syntax and then in the selection formula set the date field to the SQL expression. You can't use both a command and a SQL expression though. Using the SQL expression editor, you might have to remove the "a" from within the max()--but only there. Depends upon your CR version. In the SQL expression editor you MUST wrap the whole expression in parens (as also shown in the command).

-LB

RE: Current pricing record

(OP)
lbass,
Thanks for the info. I have a number of crystal reports that need updated due to a change made by our ERP provider and how they store the pricing records. Each report has a slightly different need which has forced me back to the drawing board so to say.

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