Record Selection to return active price amount

[danmeljeff]

Using crystal reports XI with an Oracle Database.

I have 3 tables: INVMASTER, CONTRACTLINE, and INVRENTALPRICE

CONTRACTLINE is left outer joined with INVMASTER on the INVIID field. INVRENTALPRICE is left outer joined with INVMASTER on INVIID.

The INVRENTALPRICE table stores the rental rates for products in inventory. Each product can contain one or many prices with an ACTIVE DATE record to denote the active period for the price for that product.

When a contract is written, there is a DATE CREATED record written. This DATE CREATED field is referenced against the ACTIVE DATE on the INVRENTALPRICE table to determine what the charge should be for the product selected.

I am trying to write a report that returns only the correct rental rate for the product selected on the line.

For instance, if a contract is written on 7/1/2008 and a product with sku ABC123 is placed on the contract, the rental rate that populates the contract should be $100. The stored rental rates for product ABC123 are...

4/1/2007 = $225
4/1/2008 = $200
6/8/2008 = $100
2/2/2009 = $75

Currently, for each contract line on my report I am getting 4 price records returned, whereas I only want the record that is the correct rental rate for the period of time as defined by the contract's DATE CREATED vs the products price ACTIVE DATE.

Please let me know if you have any suggestions as to how I can limit my returned records.

Thanks,

Jeff

 

[lbass]

You could do this by changing your joins so that invrentalprice is linked to the contractline table with a left outer join FROM contractline TO invrentalprice and link on invID and product ID(equal joins) and on contractline.createddate to invrentalprice.activedate with a >= join. Then in the report you could use a group selection formula based on a product group to get the minimum activedate that is greater than the contract date:

{invrentalprice.activedate} = minimum({invrentalprice.activedate}, {invrentalprice.productID})

-LB

 

[danmeljeff]

I tried what you suggested, and I'm getting incorrect results.

For instance, product XC555KIT has 4 returned records.

Price Active Date Date Created
$200 04/01/2008 08/24/2008
$200 04/01/2008 09/30/2008
$200 04/01/2008 01/21/2009
$200 04/01/2008 01/21/2009

The returned records should look like this though

Price Active Date Date Created
$200 04/01/2008 08/24/2008
$200 04/01/2008 09/30/2008
$250 10/29/2008 01/21/2009
$250 10/29/2008 01/21/2009

And where you say, "get the minimum Active Date that is greater than the contract date" ... I actually need to return the maximum active date that is less than the contract date.

I tried changing the link to <= and the group selection formula to "maximum"...but that didn't work.

Thanks for your help so far...this is definitely more progress than I was getting going it alone.

 

[lbass]

Why would there be more than one date created date--this is the contract date, right? There should be multiple active dates per one contract date. I think it would help if you showed product ID and contract Id fields in your sample data. Also please identify the fields you are grouping on in the report.

-LB

 

[danmeljeff]

You're right, I left out some info. What you instructed me to do worked...I was able to limit the returned records to a single record per contract line (instead of 4). However, the Active Date returned wasn't correct in all cases.

The table below is what is returned for product XC555KIT with your suggestions from the previous post.

Price Active Date Date Created ContractID LineNo
$200 04/01/2008 08/24/2008 CTLA003393 105
$200 04/01/2008 09/30/2008 CTLA003509 221
$200 04/01/2008 01/21/2009 CTLA004233 7
$200 04/01/2008 01/21/2009 CTLA004233 42

Since there are 2 pricing records for this product (1 with an active date of 04/01/2008 and another on 10/29/2008), the last 2 records in the table above should have active dates of 10/29/2008 because the Date Created is > 10/29/2008 (and 10/29/2008 is the most recent record relative to the Date Created field on the contract).

I'm grouping on Product ID.

 

[lbass]

I assumed you had an outer group on contract. If you group on contract and then on product ID and use the group selection formula I suggested earlier, I think you will get the correct value for the active date.

-LB

 

[danmeljeff]

You're right. I was linking the incorrect DATE fields together.

Thanks for your help, the report is working as needed now.

You rock lbass!