Returning a value based off of current date

[Sweetworks]

I'm trying to do this in Crystal 8.5 on an Oracle 8.1.7.4 database

I have a table (SALES_TAX_PCT) with the following 3 fields:

ID
Effective_Date
Percent

On one Sales Tax ID's there will be various percentages but the one with the most recent effective Date is the value I want

So for Sales Tax ID 1

ID Date Percent
1 1/1/2002 8
1 6/1/2003 8.25

So If I have a simple report just off this table with the 3 fileds as items on the Detail line, I want to make a selection criteria that will just give me the most recent info on that tax_id.

SO far I have
CurrentDate < {SALES_TAX_PCT.EFFECTIVE_DATE} but that will give me everyting as both dates are less then the current date.

Further more I want to take this and associate the percentage with a sales. If I can get this I can probably getting it workin in my other report.

 

[lbass]

To return only the most recent record, first create a group on {Sales_Tax_Pct.ID} and then go to report->edit selection formula->GROUP and enter:

{Sales_Tax_Pct.Date} = Maximum({Sales_Tax_Pct.Date},{Sales_Tax_Pct.ID})

-LB

 

[Sweetworks]

Well that worked...
I can print out a Sales Tax Report only showing the Current Info.

Here is my New Problem.
In NYS we have to charge Tax on Freight to NYS Taxable customers. OUr system CAN'T. I have been able to get this info but I am getting duplicates tax info.

I have 2 Tables
{SALES_TAX_PCT} with the following 3 fields:
ID
Effective_Date
Percent
{RECEIVABLE_LINE} with the following fields
LINE_ID
INVOICE_ID
CUSTOMER_ID
INVOICE_DATE
AMOUNT
REFERENCE

I have 1 Group and that is by {SALES_TAX_PCT.ID}.
My Record Selection formula Filters the Receiveable Line For a Start and End Dates, and any RECEIVABLE_LINE.REFERENCE that says Freight.
For example on one invoice (12782) If I run it I get the following

Invoice_ID DATE AMOUNT TaxPCT (Formula that Calcs Tax)
12782 3/11/03 10.00 8.00 0.80
12782 3/11/03 10.00 8.25 0.83

I have a Record Selection as follows.
{RECEIVABLE_LINE.INVOICE_DATE} > {SALES_TAX_PCT.EFFECTIVE_DATE}
As the {SALES_TAX_PCT} Table has the following for this ID
ID Effective_Date Percent
46 7/1/01 8.00
46 6/1/03 8.25

I now get this info instead
Invoice_ID DATE AMOUNT TaxPCT (Formula that Calcs Tax)
12782 3/11/03 10.00 8.00 0.80

Which is accurate, however I know going forward if the invoice date is after 6/1/03 I will again run into the same problem.

I would like to know how I can have the Invoice Date look up the data in the Sales Tax table based off of date.

 

[lbass]

I can't see how you are linking these two tables unless you are using a &quot;greater than or = join&quot; from {RECEIVABLE_LINE.INVOICE_DATE} to {SALES_TAX_PCT.EFFECTIVE_DATE}. It might help to know your joins.

Anyway, I'm not sure about this, but you might try a formula to replace {SALES_TAX_PCT.PERCENT}:

if {RECEIVABLE_LINE.INVOICE_DATE} >= maximum({SALES_TAX_PCT.EFFECTIVE_DATE}, {SALES_TAX_PCT.ID}) or
{RECEIVABLE_LINE.INVOICE_DATE} in {SALES_TAX_PCT.EFFECTIVE_DATE} to next({SALES_TAX_PCT.EFFECTIVE_DATE}) then {SALES_TAX_PCT.PERCENT} else 0

-LB