Getting most recent field based on date 2

[Scroller52]

Hi, i'm using CR XI on a SQL db. my data looks like this:

Investor Date Tax
1 1/1/07 20%
1 1/1/08 25%

I need the details sections to return just the most recent tax rate of 25%. There should not be a scenario where there are two tax rates on the same date.

any help is appreciated. thanks!

 

[lbass]

Yes, oops. Please clarify whether there is only one date field or two that mark changes in the two tax rates.

-LB

 

[Scroller52]

I'm not sure exactly what you mean? I think it is one. The only difference between the two rows of data is just the date field.

 

[lbass]

Please show me the SQL query that is currently generated by your report (database->show SQL query). This should show both types of tax rate and the date field.

-LB

 

[Scroller52]

SELECT `Driver`.`Type`, `Driver`.`Legal Entity`, `Driver`.`Investor Common ID`, `Driver`.`Investor`, `Driver`.`GL End Date`, `Driver`.`Date`, `Driver`.`Deal Name`, `Driver`.`Position`, `Driver`.`JPY Withholding Amt`, `Driver`.`Conversion Rate`, `Driver`.`USD Withholding Amount`
FROM `Driver$` `Driver`
ORDER BY `Driver`.`Type`, `Driver`.`Investor Common ID`, `Driver`.`Legal Entity` DESC, `Driver`.`Date`

 

[lbass]

I don't see the tax rate fields in here, and there are two date fields. Which one defines the most recent tax rate?

-LB

 

[Scroller52]

ahh, i took the Tax rate formula out since it wasn't working. Heres what it looks like when i put it back into the report:


SELECT `Driver`.`Type`, `Driver`.`Legal Entity`, `Driver`.`Investor Common ID`, `Driver`.`Investor`, `Driver`.`GL End Date`, `Driver`.`Date`, `Driver`.`Deal Name`, `Driver`.`Position`, `Driver`.`JPY Withholding Amt`, `Driver`.`Conversion Rate`, `Driver`.`USD Withholding Amount`, `Tax`.`Date`, `Tax`.`Tax Rate Deal`, `TaxBase`.`Date`, `TaxBase`.`Tax Rate Base`
FROM (`Driver$` `Driver` LEFT OUTER JOIN `Tax$` `Tax` ON (((((`Driver`.`Investor Common ID`=`Tax`.`Investor Common ID`) AND (`Driver`.`Legal Entity`=`Tax`.`Legal Entity`)) AND (`Driver`.`Deal Name`=`Tax`.`Deal Name`)) AND (`Driver`.`Position`=`Tax`.`Position`)) AND (`Driver`.`Investor`=`Tax`.`Investor`)) AND (`Driver`.`Type`=`Tax`.`Type`)) LEFT OUTER JOIN `'Tax Base Rate$'` `TaxBase` ON (((`Driver`.`Type`=`TaxBase`.`Type`) AND (`Driver`.`Legal Entity`=`TaxBase`.`Legal Entity`)) AND (`Driver`.`Investor`=`TaxBase`.`Investor`)) AND (`Driver`.`Investor Common ID`=`TaxBase`.`Investor Common ID`)
ORDER BY `Driver`.`Type`, `Driver`.`Investor Common ID`, `Driver`.`Legal Entity` DESC, `Driver`.`Date`

Here are the formula's being used:
Base Tax:
if {TaxBase.Date} = maximum({TaxBase.Date},{Driver.Legal Entity}) then {TaxBase.Tax Rate Base} else 0

Specific Tax
if {Tax.Date} = maximum({Tax.Date},{Driver.Legal Entity}) then {Tax.Tax Rate Deal}

Tax Rate (actual field being brought into the Details section)
if {Driver.JPY Withholding Amt}=0 then
0 else
if isnull({@Spec Tax})then
{@Base Tax}*100 else
{@Spec Tax}*100

 

[lbass]

Create two SQL expressions like this:
[{%SpecTaxDt}:]
(
select max(`Date`)
from Tax$ A
where A.`Investor Common ID` = Tax$.`Investor Common ID` and
A.`Legal Entity` = Tax$.`Legal Entity` and
A.`Deal Name` = Tax$.`Deal Name` and
A.`Position` = Tax$.`Position` and
A.`Investor` = Tax$.`Investor` and
A.`Type` = Tax$.`Type`
)

[{%BaseTaxDt}:]
(
select max(`Date`)
from `Tax Base Rate$` A
where A.`Investor Common ID` = `Tax Base Rate$`.`Investor Common ID` and
A.`Legal Entity` = `Tax Base Rate$`.`Legal Entity` and
A.`Investor` = `Tax Base Rate$`.`Investor` and
A.`Type` = `Tax Base Rate$`.`Type`
)

Then change your formulas to:

{@basetx}:
if {TaxBase.Date} = {%BaseTaxDt} then {TaxBase.Tax Rate Base}

{@spectx}:
if {Tax.Date} = {%SpecTaxDt} then {Tax.Tax Rate Deal}

Then use:

if {Driver.Foreign Tax Amount}=0 then
0 else
if isnull(maximum({@spectx},{table.investor})then
maximum({@basetx},{table.investor})*100 else
maximum({@spectx},{table.investor})*100

Not sure, as I'm still not totally following this.

-LB

 

[Scroller52]

When i try to save the SQL expression for Spec Tax, i get a syntax error

(
select max(`Date`)
from Tax$ A
where A.`Investor Common ID` = Tax$.`Investor Common ID` and
A.`Legal Entity` = Tax$.`Legal Entity` and
A.`Deal Name` = Tax$.`Deal Name` and
A.`Position` = Tax$.`Position` and
A.`Investor` = Tax$.`Investor` and
A.`Type` = Tax$.`Type`
)

for the Base Tax the error message is 'Too Few parameters.Expected 4

(
select max(`Date`)
from `Tax Base Rate$` A
where A.`Investor Common ID` = `Tax Base Rate$`.`Investor Common ID` and
A.`Legal Entity` = `Tax Base Rate$`.`Legal Entity` and
A.`Investor` = `Tax Base Rate$`.`Investor` and
A.`Type` = `Tax Base Rate$`.`Type`
)

And in the Tax Rate formula, i get a 'A Number is required here' and it highlights the maximum formula

if {Driver.JPY Withholding Amt}=0 then
0 else
if isnull(maximum({%SQL Deal Tax},{Driver.Legal Entity}))then
maximum({%SQL Base Tax},{Driver.Legal Entity})*100 else
maximum({%SQL Deal Tax},{Driver.Legal Entity})*100


I also have very little SQL experience so i can only guess as to how the SQL expressions will return the taxrate with the max date.

thank you for your patience with this one LB

 

[lbass]

1- I don't know why you would get that message.
2- You should be able to return the maximum of a date.
3- You implemented the formula incorrectly. You would reference the SQL expressions for the dates in the formulas {@basetx} and {@spectx} and then use those formulas in the final formula.

I can't really troubleshoot the SQL expressions, so I'm not sure I can offer any further assistance.

-LB