June 13, 2014 at 3:52 am
I have a task to provide users with a list of currency rates in the following format:
Base Currency
Hedged Currency
Base to Hedge Rate
Hedged to Base Rate
e.g.
"Base Currency"..."Hedged Currency"..."Base to Hedge Rate"..."Hedged to Base Rate"
GBP....................USD.......................1.70.........................0.59
GBP....................EUR.......................1.25..........................0.80
(the dots are for the visual only)
the table I have been provided in SQL
has the following structure:
Currency Code
Currency Rate (Rate against the Base Currency)
Base Currency (BIT to recognise which currency is the base one as it can vary from site to site. Only once currency can be Base per a given site)
I have started to implement, but ended up with cursor and temporary table, where I insert the data.
Is there any better approach you can advise please?
Massive thanks!
June 13, 2014 at 4:22 am
which part are you struggling with?
care to share what you have done so far?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 13, 2014 at 4:46 am
Thanks for looking!
I think after giving it a proper thought, the solution was easy after all:
WITH currencies AS (
SELECT 'AED' AS Currency,
0.454438 AS BaseToHedgeRate,
0 AS BaseCurrency
UNION ALL
SELECT 'AUD',
0.126028,
0
UNION ALL
SELECT 'CNY',
0.792131,
0
UNION ALL
SELECT 'DKK',
0.738462,
0
UNION ALL
SELECT 'HKD',
1,
1)
SELECT (SELECT c.Currency
FROM currencies c
WHERE c.BaseCurrency = 1) AS BaseCurrency,
c.Currency AS HedgedCurrency,
c.BaseToHedgeRate,
1/c.BaseToHedgeRate AS HedgedToBaseRate
FROM currencies c
WHERE c.BaseCurrency =0
UNION ALL
SELECT c.Currency,
(SELECT c.Currency
FROM currencies c
WHERE c.BaseCurrency = 1),
1/c.BaseToHedgeRate,
c.BaseToHedgeRate
FROM currencies c
WHERE c.BaseCurrency =0
June 13, 2014 at 4:54 am
good to hear you have solved it.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply