August 21, 2018 at 8:06 am
Hi folks,
I have costs that I need to convert into a different currency based on the currency at the time. I have a historical record of the currency by week. The problem is sometimes the currency in a particular week is missing so I'm being asked to grab the previously available exchange rate.
So consider the following as an example:
DECLARE @currency TABLE (ID INT, code VARCHAR(3), exchangerateexchangerate FLOAT, [year] INT, [week] INT)
DECLARE @cost TABLE (costs FLOAT, currency VARCHAR(3), [year] INT, [week] INT)
INSERT INTO @currency
VALUES
(1,'USD',1.31741322,2017,52),
(2,'USD',1.30589892,2018,4),
(3,'USD',1.3020579,2018,5),
(4,'USD',1.31138359,2018,7)
INSERT INTO @cost
VALUES
(2003.45,'USD',2018,7),
(525.50,'CND',2018,7),
(11961.03,'USD',2018,5),
(5643.12,'USD',2018,6),
(785.25,'USD',2018,3)
SELECT
c.costs,
exchangerate,
CASE WHEN c.currency = 'CND' THEN c.costs ELSE c.costs*cu.exchangerate END AS CostInCND
FROM
@cost c
LEFT JOIN @currency cu ON cu.year = c.year AND cu.week = c.week
Everything is pretty straightforward until I hit a week that's not in the lookup table. Also not a fan of using a year/week over an actual date but this is the data that I have to work with.
Any ideas?
August 21, 2018 at 8:21 am
Y.B. - Tuesday, August 21, 2018 8:06 AMHi folks,I have costs that I need to convert into a different currency based on the currency at the time. I have a historical record of the currency by week. The problem is sometimes the currency in a particular week is missing so I'm being asked to grab the previously available exchange rate.
So consider the following as an example:
DECLARE @currency TABLE (ID INT, code VARCHAR(3), exchangerateexchangerate FLOAT, [year] INT, [week] INT)
DECLARE @cost TABLE (costs FLOAT, currency VARCHAR(3), [year] INT, [week] INT)INSERT INTO @currency
VALUES
(1,'USD',1.31741322,2017,52),
(2,'USD',1.30589892,2018,4),
(3,'USD',1.3020579,2018,5),
(4,'USD',1.31138359,2018,7)INSERT INTO @cost
VALUES
(2003.45,'USD',2018,7),
(525.50,'CND',2018,7),
(11961.03,'USD',2018,5),
(5643.12,'USD',2018,6),
(785.25,'USD',2018,3)SELECT
c.costs,
exchangerate,
CASE WHEN c.currency = 'CND' THEN c.costs ELSE c.costs*cu.exchangerate END AS CostInCNDFROM
@cost c
LEFT JOIN @currency cu ON cu.year = c.year AND cu.week = c.weekEverything is pretty straightforward until I hit a week that's not in the lookup table. Also not a fan of using a year/week over an actual date but this is the data that I have to work with.
Any ideas?
DECLARE @currency TABLE (ID INT, code VARCHAR(3), exchangerate FLOAT, [year] INT, [week] INT)
DECLARE @cost TABLE (costs FLOAT, currency VARCHAR(3), [year] INT, [week] INT)
INSERT INTO @currency
VALUES
(1,'USD',1.31741322,2017,52),
(2,'USD',1.30589892,2018,4),
(3,'USD',1.3020579,2018,5),
(4,'USD',1.31138359,2018,7)
INSERT INTO @cost
VALUES
(2003.45,'USD',2018,7),
(525.50,'CND',2018,7),
(11961.03,'USD',2018,5),
(5643.12,'USD',2018,6),
(785.25,'USD',2018,3)
SELECT
c.costs,
curr.exchangerate,
CASE WHEN c.currency = 'CND' THEN c.costs ELSE c.costs*curr.exchangerate END AS CostInCND
FROM @cost c
CROSS APPLY (SELECT TOP(1) exchangerate
FROM @currency cu
WHERE (cu.year = c.year AND cu.week <= c.week)
OR (cu.year < c.year)
ORDER BY cu.year DESC, cu.week DESC) AS curr
August 21, 2018 at 8:25 am
Jonathan AC Roberts - Tuesday, August 21, 2018 8:21 AMY.B. - Tuesday, August 21, 2018 8:06 AMHi folks,I have costs that I need to convert into a different currency based on the currency at the time. I have a historical record of the currency by week. The problem is sometimes the currency in a particular week is missing so I'm being asked to grab the previously available exchange rate.
So consider the following as an example:
DECLARE @currency TABLE (ID INT, code VARCHAR(3), exchangerateexchangerate FLOAT, [year] INT, [week] INT)
DECLARE @cost TABLE (costs FLOAT, currency VARCHAR(3), [year] INT, [week] INT)INSERT INTO @currency
VALUES
(1,'USD',1.31741322,2017,52),
(2,'USD',1.30589892,2018,4),
(3,'USD',1.3020579,2018,5),
(4,'USD',1.31138359,2018,7)INSERT INTO @cost
VALUES
(2003.45,'USD',2018,7),
(525.50,'CND',2018,7),
(11961.03,'USD',2018,5),
(5643.12,'USD',2018,6),
(785.25,'USD',2018,3)SELECT
c.costs,
exchangerate,
CASE WHEN c.currency = 'CND' THEN c.costs ELSE c.costs*cu.exchangerate END AS CostInCNDFROM
@cost c
LEFT JOIN @currency cu ON cu.year = c.year AND cu.week = c.weekEverything is pretty straightforward until I hit a week that's not in the lookup table. Also not a fan of using a year/week over an actual date but this is the data that I have to work with.
Any ideas?
DECLARE @currency TABLE (ID INT, code VARCHAR(3), exchangerate FLOAT, [year] INT, [week] INT)
DECLARE @cost TABLE (costs FLOAT, currency VARCHAR(3), [year] INT, [week] INT)INSERT INTO @currency
VALUES
(1,'USD',1.31741322,2017,52),
(2,'USD',1.30589892,2018,4),
(3,'USD',1.3020579,2018,5),
(4,'USD',1.31138359,2018,7)INSERT INTO @cost
VALUES
(2003.45,'USD',2018,7),
(525.50,'CND',2018,7),
(11961.03,'USD',2018,5),
(5643.12,'USD',2018,6),
(785.25,'USD',2018,3)SELECT
c.costs,
curr.exchangerate,
CASE WHEN c.currency = 'CND' THEN c.costs ELSE c.costs*curr.exchangerate END AS CostInCND
FROM @cost c
CROSS APPLY (SELECT TOP(1) exchangerate
FROM @currency cu
WHERE (cu.year = c.year AND cu.week <= c.week)
OR (cu.year < c.year)
ORDER BY cu.year DESC, cu.week DESC) AS curr
Much appreciated, exactly what I needed!
Cheers,
August 21, 2018 at 10:33 am
Y.B. - Tuesday, August 21, 2018 8:06 AMAny ideas?
>>I have costs that I need to convert into a different currency based on the currency at the time. I have a historical record of the currency by week. The problem is sometimes the currency in a particular week is missing so I'm being asked to grab the previously available exchange rate. <<
You might want to read the ISO 8601 standards for temporal data. The correct format for a date expressed as a day within a week is: "yyyyWww-[1-7]", where the yyyy is the year, the letter W is a separator, the next two digits are the week expressed as 01 thru 52 or 53, the dash is a separator, and the final digit is the day of the week, where 1 = Monday. .
I see you are using float for the exchange rate. Please check on this, but isn't that illegal? Last time I looked the EU, GAAP, et al had conversion rules. They were based on decimal numbers and not floating-point. I believe it was called triangulation for Euros. I think it's supposed to be five decimal places, but I haven't looked at this in years What you've done by splitting the week out as a separate column is called "attribute splitting" and it's a very common design error.
There's also no such thing as a generic "id" in RDBMS. The ISO currency codes are always three letters, so using VARCHAR(3) is a really bad idea. I believe that all currencies are now decimal (there used to be two or three that were still fractional in godforsaken countries, which just don't matter to anyone else). You have a natural key and you failed to use it. Essentially, your DDL invites incredible errors, prevents accurate computations and is probably illegal. Why don't we fix it and make it into real DDL? When you been at this for a few decades to come to the conclusion that most of the work in SQL is done in the DDL, not the DML.
This would give you tables that looks like this:
CREATE TABLE Currrency_Exchange_Rate_History
(exchange_week CHAR (7) NOT NULL
CHECK (exchange_week LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]'),
currency_code CHAR(3) NOT NULL,
PRIMARY KEY (exchange_week, currency_code), --- key required, not optional!
exchange_rate DECIMAL (12, 5) NOT NULL --- check ISO and EU laws!
);
INSERT INTO Currrency_Exchange_Rate_History
VALUES
('2017W52', 'USD', 1.31741),
('2018W04', 'USD', 1.30589),
('2018W05', 'USD', 1.30205),
('2018W07', 'USD', 1.31138);
CREATE TABLE Something_Costs
(exchange_week CHAR (7) NOT NULL
CHECK (exchange_week LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]'),
something_costs DECIMAL (12, 2) NOT NULL, ---decimal units
currency_code CHAR(3) NOT NULL );
INSERT INTO Something_Costs
VALUES
('2018W07', 'USD', 2003.45),
('2018W07', 'CND', 525.50),
('2018W05', 'USD', 11961.03),
('2018W06', 'USD', 5643.12),
('2018W03', 'USD', 785.25);
You want to be able to use the lead () and lag () functions to get your last known exchange rate information. Just throw it into a CTE or a view in the query becomes pretty easy
SELECT currency_code, exchange_week, exchange_rate,
LAG(exchange_week) OVER (PARTITION BY currency_code
ORDER BY exchange_week)
AS last_known_exchange_week,
LAG(exchange_rate) OVER (PARTITION BY currency_code
ORDER BY exchange_week)
AS last_known_exchange_rate
FROM Currrency_Exchange_Rate_History AS H
>> Also not a fan of using a year/week over an actual date but this is the data that I have to work with. <<
I agree! Rates can change by the hour.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply