ABS function issue

Hi Onibuzz,
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
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.
--
-Omnibuzz (The SQL GC)
My old query - >
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.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> 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.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> 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
>