I have two tables Expense_Addl and ExchangeRates

These are the scripts

CREATE TABLE Expense_Addl (
    FileId int,
    HostAmt money,
    HostCurrencyCd char(4),
    LockDt smalldatetime
    )

INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')

In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'


CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)

INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')

Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this

For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june

I am trying like below but not getting the correct results.

UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate)  As ExchangeRateDate
             FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
             GROUP BY ER.CurrencyCd
             ) T1
  ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCd

  Can anyone help me with this ?

  Regards,
  Rajeev Rajput

Hi Rajeev,
   I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)

UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates ER1
where
  ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
  order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc),0),0),0))), 4)


Hope this helps.

Hi,
It is working but i have one issue with it.

When Lockdt is NULL i am getting this part as 1 --->>>>>

select top 1 ExchangeRate from ExchangeRates ER1
where
  ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
  order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc

rather i should get it as NULL

Could you please help ?
Omnibuzz  thanks in advance :)



Omnibuzz wrote:

Show quoteHide quote

Oh.. okay...
Let me know if this works fine..

UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL(case when Lockdt is null then null else  (select top
1 ExchangeRate from ExchangeRates ER1
where
  ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
  order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
end,0),0),0))), 4)

But I seriously doubt if you need these many nested functions.
Show quoteHide quote

Hi thanx for your help again.

Yeah you are right select part is getting complicated
Omnibuzz, is it possible to keep select part as it while doing some
modifications in the join conditions to get the same result..

something like this...

UPDATE Expense_Addl
HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, DSREA.HostAmt))
* 100) / NULLIF(ISNULL(ER2.ExchangeRate,0),0),0))), 4)

with inner joins like this :

FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate)  As ExchangeRateDate
             FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
             GROUP BY ER.CurrencyCd
             ) T1
  ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCd

I am asking you because i tried making my topic simpler by omitting few
things from select part.
My actual update query contains one more exchangerate and code...--->>

Update Expese_Addl
SET
HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt)) *
ISNULL(ER1.ExchangeRate,1)) /
NULLIF(ISNULL(ER2.ExchangeRate,0),0),0))), 4) *     CASE DSREA.DebitFlg
WHEN 1 THEN 1
          ELSE -1
          END,
U can see there is one more exchangerate ER1.exchangerate which we have
to find using same logic but by joining BasecurrencyCd of Expense_Addl
with currencycd of ExchangeRate.

I would really appreciate if u could help me with this.

Regards,
Rajeev Rajput


Omnibuzz wrote:

Show quoteHide quote

Replace your exchange rates with one that has (start_date, end_date)
pairs for the periods in which a rate was in effect.  You can then use
a simple between predicate in a VIEW or SELECT to get the answers.

Help with varchar to date
schedule dts at a remote server
can a SELECT return this result ?!
can a SELECT return this result ?!
Issue with T-SQL and SQL 7 to SQL2000 conversion