October 17, 2007 at 4:03 pm
Need help in logic for function..
The function should take the inputs year, translation_code and business unit from the database table.
Then it should return the currency rate for current month.
The currency rates are available from jan till dec in the table as rate_01...02..03 etc..
I wanted to call the above function in the select statement to do the currency conversion with the returned value.
Please help.
October 17, 2007 at 4:21 pm
forgot to mention the columns in the currency rate table are rate_01, rate_02 till rate_12...
There is no column which identifies period in the table..so I need to check if it the period is 10 i.e current month, then i need pull the value from rate_10 column and return as output.
Please help
October 18, 2007 at 3:42 am
You can create a function something like below
--==========================================
CREATE FUNCTION dbo.fnGetCurrencyRate
(
@p_year int,
@p_translation_code varchar,
@p_business_unit varchar
)
RETURNS decimal(27,10)
AS
BEGIN
-- Declare the return variable here
DECLARE @m_value decimal(27,10)
SELECT @m_value = CASE MONTH(GETDATE())
WHEN 1 THEN rate_01
WHEN 2 THEN rate_02
WHEN 3 THEN rate_03
WHEN 4 THEN rate_04
WHEN 5 THEN rate_05
WHEN 6 THEN rate_06
WHEN 7 THEN rate_07
WHEN 8 THEN rate_08
WHEN 9 THEN rate_09
WHEN 10 THEN rate_10
WHEN 11 THEN rate_11
WHEN 12 THEN rate_12
END
FROM dbo.rates
WHERE year = @p_year
AND translation_code = @p_translation_code
AND business_unit = @p_business_unit
RETURN @m_value
END
GO
--=====================================
then you can call it with static values
select dbo.fnGetCurrencyRate(1996,1,1) as Rate
or
use in any select statement by passing parameters
select dt.year,dt.translation_code,dt.business_unit,
dbo.fnGetCurrencyRate(dt.year,dt.translation_code,dt.business_unit) as Rate
from datatable dt
hope this helps
October 18, 2007 at 9:30 am
Thanks a lot
October 20, 2007 at 7:16 pm
Sanju (10/17/2007)
forgot to mention the columns in the currency rate table are rate_01, rate_02 till rate_12...There is no column which identifies period in the table..so I need to check if it the period is 10 i.e current month, then i need pull the value from rate_10 column and return as output.
Please help
The function posted previously will probably work... I wanted to talk about the terrible thing you've done to your rate table... you should not have a column per month, you should have a row per month. In other words, you should normalize the table. The table should look like this...
BusinessUnit, TranslationCode, RateDate, Rate
RateDate would be the DateTime datatype so that it would contain both year an month. If the rates reflect a full month, as the appear to be, assign the first of each month as the date.
Primary key would be the whole table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2007 at 3:55 am
I certainly agree with Jeff and normalization is the solution.
Mine was just a work around.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply