December 19, 2012 at 6:41 am
Hi,
I have a table with exchange rates between 2 countries. There's 90 days of history for every exchange rate. I've found that there's some NULL's in the trade rate column, which I need to fill. It's been agreed that for the purposes of the report I'm working on, I can use the previous days trade rate for the exchange. howerver, I can't quite figure out how to get it.
Sample DDL and data, with expected results below:
create table test(
currency_to varchar(3),
currency_from varchar(3),
tradedate datetime,
traderate decimal(27,7)
)
go
insert into test(currency_to, currency_from, tradedate, traderate)
select 'GBP', 'EUR', CURRENT_TIMESTAMP, 1.2
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -1, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -2, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -3, 1.9
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -4, 1.6
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -5, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -6, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -7, 1.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -8, 1.1
SELECT currency_to, currency_from, tradedate,
CASE WHEN traderate = NULL THEN tradedate = (select previousdaystraderateforsamecurrencymatch) ELSE traderate END FROM test
--EXPECTED OUTPUT
select 'GBP', 'EUR', CURRENT_TIMESTAMP, 1.2
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -1, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -2, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -3, 1.9
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -4, 1.6
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -5, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -6, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -7, 1.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -8, 1.1
Many thanks, Andrew
December 19, 2012 at 7:19 am
If you maintain the previous days trade rate for each currency (or a default trade rate) in a table (or a CTE from historical table) you can obtain the default rate from it in case the current rate is NULL using coalesce something like this:
SELECT currency_to, currency_from, tradedate,
COALESCE(A.traderate, B.traderate)
from test A
JOIN previousdaystraderate B on A.currency_to = B.currency_to
and A.currency_from = B.currency_from
The probability of survival is inversely proportional to the angle of arrival.
December 19, 2012 at 7:32 am
How's this:
IF OBJECT_ID(N'tempdb..#test') IS NOT NULL
DROP TABLE #test;
CREATE TABLE #test
(currency_to VARCHAR(3),
currency_from VARCHAR(3),
tradedate DATETIME,
traderate DECIMAL(27, 7));
INSERT INTO #test
(currency_to,
currency_from,
tradedate,
traderate)
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP,
1.2
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 1,
NULL
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 2,
2.3
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 3,
1.9
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 4,
1.6
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 5,
NULL
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 6,
2.4
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 7,
1.3
UNION
SELECT 'GBP',
'EUR',
CURRENT_TIMESTAMP - 8,
1.1;
SELECT T.currency_to,
T.currency_from,
T.tradedate,
COALESCE(T.traderate, PriorRate.traderate) AS traderate
FROM #test AS T
OUTER APPLY (SELECT TOP (1)
T2.traderate
FROM #test AS T2
WHERE T2.currency_from = T.currency_from
AND T2.currency_to = T.currency_to
AND T2.traderate IS NOT NULL
AND T2.tradedate <= T.tradedate
ORDER BY T2.tradedate DESC) AS PriorRate;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 7:38 am
If you can add a identity column to your table then ...you can write code like this...
---------------------------------
create table test(
id int identity(1,1),
currency_to varchar(3),
currency_from varchar(3),
tradedate datetime,
traderate decimal(27,7)
)
go
insert into test(currency_to, currency_from, tradedate, traderate)
select 'GBP', 'EUR', CURRENT_TIMESTAMP, 1.2
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -1, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -2, 2.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -3, 1.9
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -4, 1.6
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -5, NULL
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -6, 2.4
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -7, 1.3
UNION
SELECT 'GBP', 'EUR', CURRENT_TIMESTAMP -8, 1.1
select t1.currency_to, t1.currency_from, t1.tradedate, isnull(t1.traderate ,t2.traderate) as traderate
from test t1 left join test t2 on
t1.id=t2.id+1
order by t1.tradedate desc
December 19, 2012 at 7:42 am
Another data smudge question, seem to come in waves 🙂
SELECT currency_to, currency_from, tradedate, ad.traderate
FROM test a
CROSS APPLY (SELECT TOP 1 traderate
FROM test b
WHERE b.tradedate <= a.tradedate
AND b.traderate IS NOT NULL
AND b.currency_from = a.currency_from
AND b.currency_to = a.currency_to
ORDER BY b.tradedate DESC) ad;
Which returns: -
currency_to currency_from tradedate traderate
----------- ------------- ----------------------- ---------------------------------------
GBP EUR 2012-12-11 14:35:45.820 1.1000000
GBP EUR 2012-12-12 14:35:45.820 1.3000000
GBP EUR 2012-12-13 14:35:45.820 2.4000000
GBP EUR 2012-12-14 14:35:45.820 2.4000000
GBP EUR 2012-12-15 14:35:45.820 1.6000000
GBP EUR 2012-12-16 14:35:45.820 1.9000000
GBP EUR 2012-12-17 14:35:45.820 2.3000000
GBP EUR 2012-12-18 14:35:45.820 2.3000000
GBP EUR 2012-12-19 14:35:45.820 1.2000000
Or: -
SELECT currency_to, currency_from, tradedate, ad.traderate
FROM test a
CROSS APPLY (SELECT TOP 1 CAST(traderate AS FLOAT)
FROM test b
WHERE b.tradedate <= a.tradedate
AND b.traderate IS NOT NULL
AND b.currency_from = a.currency_from
AND b.currency_to = a.currency_to
ORDER BY b.tradedate DESC) ad(traderate);
For: -
currency_to currency_from tradedate traderate
----------- ------------- ----------------------- ----------------------
GBP EUR 2012-12-11 14:42:05.897 1.1
GBP EUR 2012-12-12 14:42:05.897 1.3
GBP EUR 2012-12-13 14:42:05.897 2.4
GBP EUR 2012-12-14 14:42:05.897 2.4
GBP EUR 2012-12-15 14:42:05.897 1.6
GBP EUR 2012-12-16 14:42:05.897 1.9
GBP EUR 2012-12-17 14:42:05.897 2.3
GBP EUR 2012-12-18 14:42:05.897 2.3
GBP EUR 2012-12-19 14:42:05.897 1.2
If you want to do the presentation layer task of formatting the result-set in the database.
December 19, 2012 at 7:48 am
As a note on the variations using Apply, I recommend using Outer Apply, in case you have a currency pair that has never had a valid exchange rate in your data. That way, you'll get a row for that. Otherwise, if you use Cross Apply, the currency pair simply won't generate a row at all.
Same for a currency pair that started out NULL and doesn't have a prior exchange rate recorded. Even if it has later valid rows, you won't get the earlier ones.
So, unless you want to exclude those entirely, use Outer Apply instead of Cross Apply. That will at least give people reviewing the data a chance to notice the issue.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 8:04 am
perfect. Thank you
December 20, 2012 at 7:06 am
Joe, I've seen your "tricks" that pretend to avoid date gaps.
I've already proved to you that they do not work. They allow gaps, they allow overlaps. I showed you this a LONG time ago.
Why do you keep insisting that they work? It's dishonest.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply