"Rajeev" wrote:

> I have two tables Expense_Addl and ExchangeRates
>
> These are the scripts
>
>
> CREATE TABLE Expense_Addl (
>         FileId int,
>         HostAmt money,
>         HostCurrencyCd char(4),
>         BaseCurrencyCd Char(4),
>         LockDt smalldatetime
>         )
>
>
> INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2002-01-01')
>
> --In the above table HostCurrencycd, BaseCurrencyCd can be any
> currenycode like 'USD',
> --'INR', 'YEN'
>
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
>
> INSERT INTO ExchangeRates values ('INR', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('INR', 0.40, '2006-06-14')
> INSERT INTO ExchangeRates values ('USD', 0.22, '2006-06-22')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd, BaseCurrencyCd from table
> ExchangeRates
> for the nearest date to LockDt like this
>
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 for
> 'INR' because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45 for
> 'INR'
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 for
> 'INR' because
> lockdt 01st jan is closet to 14th june
>
> Similarly HostAmt should take nearest exchangerate for BaseCurrencyCd
> from ExchangeRates table
>
>
>
> I am trying something like below but not getting the correct results.
>
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
> * ISNULL(ER1.ExchangeRate,1)) /
> NULLIF(ISNULL(ER2.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.BaseCurrencyCd =
> 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 =
> ER2.CurrencyCd
> INNER JOIN ExchangeRates ER2(NOLOCK) ON EA.HostCurrencyCd =
> ER2.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
>              ) T2
>   ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
> ER2.CurrencyCd
>
> Can anyone help me with this ?
>
> Thanks and Regards,
> Rajeev Rajput
>
>

"Alejandro Mesa" wrote:

> Rajeev,
>
> See if this helps.
>
> 1 - Update the column where exists a row in [ExchangeRates] where the column
> [ExchangeRateDate] is equal to [LockDt] and [CurrencyCd] is equal to
> [HostCurrencyCd]
>
> update Expense_Addl
> set HostAmt = HostAmt * (
> select a.ExchangeRate
> from ExchangeRates as a
> where
>     a.CurrencyCd = Expense_Addl.HostCurrencyCd
>     and a.ExchangeRateDate = Expense_Addl.LockDt
> )
> where exists (
> select *
> from ExchangeRates as a
> where
>     a.CurrencyCd = Expense_Addl.HostCurrencyCd
>     and a.ExchangeRateDate = Expense_Addl.LockDt
> )
> go
>
> 2 - For those where there is not an exchange rate for that currency and
> [LockDt], I used this expression to find the rate.
>
> case
> when abs(datediff(day, (x), Expense_Addl.LockDt)) > Abs(datediff(day, (y),
> Expense_Addl.LockDt)) then (x) else (y)
> end
>
> -- max of ExchangeRates.ExchangeRateDate less than Expense_Addl.LockDt
> (x) - select max(b.ExchangeRateDate) from ExchangeRates as b where
> b.ExchangeRateDate < Expense_Addl.LockDt
>
> -- min of ExchangeRates.ExchangeRateDate greater than Expense_Addl.LockDt
> (y) - min(b.ExchangeRateDate) from ExchangeRates as b where
> b.ExchangeRateDate > Expense_Addl.LockDt
>
> update
>     Expense_Addl
> set
>     HostAmt = HostAmt * (
>     select
>         a.ExchangeRate
>     from
>         ExchangeRates as a
>     where
>         a.CurrencyCd = Expense_Addl.HostCurrencyCd
>         and a.ExchangeRateDate = case when abs(datediff(day, (select
> max(b.ExchangeRateDate) from ExchangeRates as b where b.ExchangeRateDate <
> Expense_Addl.LockDt), Expense_Addl.LockDt)) < abs(datediff(day, (select
> min(b.ExchangeRateDate) from ExchangeRates as b where b.ExchangeRateDate >
> Expense_Addl.LockDt), Expense_Addl.LockDt)) then (select
> max(b.ExchangeRateDate) from ExchangeRates as b where b.ExchangeRateDate <
> Expense_Addl.LockDt) else (select min(b.ExchangeRateDate) from ExchangeRates
> as b where b.ExchangeRateDate > Expense_Addl.LockDt) end
>     )
> where
>     exists (
>     select
>         *
>     from
>         ExchangeRates as a
>     where
>         a.CurrencyCd = Expense_Addl.HostCurrencyCd
>     )
>     and not exists (
>     select
>         *
>     from
>         ExchangeRates as a
>     where
>         a.CurrencyCd = Expense_Addl.HostCurrencyCd
>         and a.ExchangeRateDate = Expense_Addl.LockDt
>     )
> go
>
>
> AMB
>
>
> "Rajeev" wrote:
>
> > I have two tables Expense_Addl and ExchangeRates
> >
> > These are the scripts
> >
> >
> > CREATE TABLE Expense_Addl (
> >         FileId int,
> >         HostAmt money,
> >         HostCurrencyCd char(4),
> >         BaseCurrencyCd Char(4),
> >         LockDt smalldatetime
> >         )
> >
> >
> > INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2006-06-15')
> > INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2006-06-19')
> > INSERT INTO Expense_addl values (1001, 500, 'INR','USD', '2002-01-01')
> >
> > --In the above table HostCurrencycd, BaseCurrencyCd can be any
> > currenycode like 'USD',
> > --'INR', 'YEN'
> >
> >
> > CREATE TABLE ExchangeRates
> > (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> > SMALLDATETIME)
> >
> >
> > INSERT INTO ExchangeRates values ('INR', 0.45, '2006-06-21')
> > INSERT INTO ExchangeRates values ('INR', 0.40, '2006-06-14')
> > INSERT INTO ExchangeRates values ('USD', 0.22, '2006-06-22')
> >
> > Now i have to update the HostAmt in Expense_Addl table taking
> > exchangerate for the hostcurrencycd, BaseCurrencyCd from table
> > ExchangeRates
> > for the nearest date to LockDt like this
> >
> >
> > For e.g. HOstAmt in first row should take exchangerate as 0.40 for
> > 'INR' because
> > lockdt 15th june is closet to 14th june
> > Similarly HOstAmt in second row should take exchangerate as 0.45 for
> > 'INR'
> > because lockdt 19th june is closet to 21st june
> > Similarly HOstAmt in third row should take exchangerate as 0.40 for
> > 'INR' because
> > lockdt 01st jan is closet to 14th june
> >
> > Similarly HostAmt should take nearest exchangerate for BaseCurrencyCd
> > from ExchangeRates table
> >
> >
> >
> > I am trying something like below but not getting the correct results.
> >
> >
> > UPDATE EA
> > SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
> > * ISNULL(ER1.ExchangeRate,1)) /
> > NULLIF(ISNULL(ER2.ExchangeRate,0),0),0))), 4)
> > FROM Expense_Addl EA(NOLOCK)
> > INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.BaseCurrencyCd =
> > 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 =
> > ER2.CurrencyCd
> > INNER JOIN ExchangeRates ER2(NOLOCK) ON EA.HostCurrencyCd =
> > ER2.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
> >              ) T2
> >   ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
> > ER2.CurrencyCd
> >
> > Can anyone help me with this ?
> >
> > Thanks and Regards,
> > Rajeev Rajput
> >
> >