Current pricing record

[klgoist]

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?

 

[lbass]

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

 

[klgoist]

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.