lookup value for each row
-
I have a table with currencies, and a table where each day the exchange rates for those currencies are downloaded. I want to write 1 query to generate a list of all currencies and their exchange rate at a certain point in the past. The difficulty here is that sometimes the download of all or some rates fails. I simulated this in the sample data, where the exchange rate of USD on 2016-10-3 is missing.
In this case, I want to show the list of exchange rates on 2016-10-3, for EUR and AED there are rates available, but for USD he should show the most recent rate for that day, so this will be the one of 2016-10-2
I do this now with 1 query per currency... it works but of course is way too slow.
Can anyone help me out?
create table currencies (id int primary key identity(1,1), ISO varchar(3))
insert into currencies (ISO) values ('EUR'),('USD'),('AED')
create table exchangerates (id int primary key identity(1,1), currency int default(0) not null, rate numeric(19,6), ddate smalldatetime default(getdate()) not null)
insert into exchangerates (currency, rate, ddate) values
(1, 1.056, '2016-10-1 12:00'), (2, 1.088, '2016-10-1 12:00'), (3, 1.785, '2016-10-1 12:00')
, (1, 1.014, '2016-10-2 12:00'), (2, 1.062, '2016-10-2 12:00'), (3, 1.74, '2016-10-2 12:00')
, (1, 1.01, '2016-10-3 12:00'), (3, 1.77, '2016-10-3 12:00')
, (1, 1.027, '2016-10-4 12:00'), (2, 1.055, '2016-10-4 12:00'), (3, 1.64, '2016-10-4 12:00')
-
marc.corbeel (10/14/2016)
I have a table with currencies, and a table where each day the exchange rates for those currencies are downloaded. I want to write 1 query to generate a list of all currencies and their exchange rate at a certain point in the past. The difficulty here is that sometimes the download of all or some rates fails. I simulated this in the sample data, where the exchange rate of USD on 2016-10-3 is missing.In this case, I want to show the list of exchange rates on 2016-10-3, for EUR and AED there are rates available, but for USD he should show the most recent rate for that day, so this will be the one of 2016-10-2
I do this now with 1 query per currency... it works but of course is way too slow.
Can anyone help me out?
create table currencies (id int primary key identity(1,1), ISO varchar(3))
insert into currencies (ISO) values ('EUR'),('USD'),('AED')
create table exchangerates (id int primary key identity(1,1), currency int default(0) not null, rate numeric(19,6), ddate smalldatetime default(getdate()) not null)
insert into exchangerates (currency, rate, ddate) values
(1, 1.056, '2016-10-1 12:00'), (2, 1.088, '2016-10-1 12:00'), (3, 1.785, '2016-10-1 12:00')
, (1, 1.014, '2016-10-2 12:00'), (2, 1.062, '2016-10-2 12:00'), (3, 1.74, '2016-10-2 12:00')
, (1, 1.01, '2016-10-3 12:00'), (3, 1.77, '2016-10-3 12:00')
, (1, 1.027, '2016-10-4 12:00'), (2, 1.055, '2016-10-4 12:00'), (3, 1.64, '2016-10-4 12:00')
Based on your sample data what do you expect as output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (10/14/2016)
marc.corbeel (10/14/2016)
I have a table with currencies, and a table where each day the exchange rates for those currencies are downloaded. I want to write 1 query to generate a list of all currencies and their exchange rate at a certain point in the past. The difficulty here is that sometimes the download of all or some rates fails. I simulated this in the sample data, where the exchange rate of USD on 2016-10-3 is missing.In this case, I want to show the list of exchange rates on 2016-10-3, for EUR and AED there are rates available, but for USD he should show the most recent rate for that day, so this will be the one of 2016-10-2
I do this now with 1 query per currency... it works but of course is way too slow.
Can anyone help me out?
create table currencies (id int primary key identity(1,1), ISO varchar(3))
insert into currencies (ISO) values ('EUR'),('USD'),('AED')
create table exchangerates (id int primary key identity(1,1), currency int default(0) not null, rate numeric(19,6), ddate smalldatetime default(getdate()) not null)
insert into exchangerates (currency, rate, ddate) values
(1, 1.056, '2016-10-1 12:00'), (2, 1.088, '2016-10-1 12:00'), (3, 1.785, '2016-10-1 12:00')
, (1, 1.014, '2016-10-2 12:00'), (2, 1.062, '2016-10-2 12:00'), (3, 1.74, '2016-10-2 12:00')
, (1, 1.01, '2016-10-3 12:00'), (3, 1.77, '2016-10-3 12:00')
, (1, 1.027, '2016-10-4 12:00'), (2, 1.055, '2016-10-4 12:00'), (3, 1.64, '2016-10-4 12:00')
Based on your sample data what do you expect as output?
The situation on 2016-10-3 should give this list:
EUR 1.01
USD 1.062 (downloaded one day before, failed on 2016-10-3)
AED 1.77
-
You can leverage a tally table for this sort of thing. I keep one in a view on my systems.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
Many thanks to Jeff Moden for this code and teaching so many this technique. You can read more about this here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
This produces the desired output.
select c.ISO
, CurrentRates.rate
from exchangerates er
join currencies c on c.id = er.currency
outer apply
(
select top 1 rate
, DATEADD(day, t.N - 1, er2.ddate) as SortDate
from cteTally t
join exchangerates er2 on er2.ddate <= DATEADD(day, t.N - 1, er2.ddate)
where er2.currency = c.id
and er2.ddate >= er.ddate
) CurrentRates
order by CurrentRates.SortDate
, er.currency
There is probably another way to do this with the triangular join but it is late Friday afternoon and my brain is mush. Hope this helps. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This gives me a list of all days, not the rates on 2016-10-3
-
marc.corbeel (10/14/2016)
This gives me a list of all days, not the rates on 2016-10-3
I thought you wanted each days information. I am guessing as to what you want for output here. Do you only want to see the rate for 10/3?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
That's right. I want a list with ALL currencies and their most recent rate at a certain date (example on Oct 3)
-
maybe ??
DECLARE @d SMALLDATETIME= '2016-10-04';
SELECT c.ISO,
e.rate
FROM exchangerates AS e
INNER JOIN currencies AS c ON e.currency = c.id
CROSS APPLY
(
SELECT MAX(id) AS id
FROM exchangerates
WHERE(ddate < @d)
GROUP BY currency
) x
WHERE x.id = e.id;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
This gives exactly what I want. The "cross apply" function is new to me.... wil look into that.
Thanks alot... if ever we meet, I bye you a beer!
-
could also use a CTE
DECLARE @d SMALLDATETIME= '2016-10-04';WITH x AS
(
SELECT MAX(id) AS id
FROM exchangerates
WHERE(ddate < @d)
GROUP BY currency
)
SELECT c.ISO,
e.rate
FROM exchangerates AS e
INNER JOIN currencies AS c ON e.currency = c.id
INNER JOIN X ON E.id = X.id;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Even this works.
DECLARE @d SMALLDATETIME= '2016-10-04';
SELECT c.ISO,
e.rate
FROM exchangerates AS e
INNER JOIN currencies AS c ON e.currency = c.id
INNER JOIN
(
SELECT MAX(id) AS id
FROM exchangerates
WHERE(ddate < @d)
GROUP BY currency
) x
on x.id = e.id;
But when to use cross apply over inner join please explain.
Thankyou
-
To learn more about APPLY take a look at the two articles in my signature. Paul White's explanation on this is the best one I have seen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
There are two issues with all of the solutions provided so far. They all read the exchangerates table twice: once to get the exchange rate and once to get the max id for the most recent record. Here is a solution that only reads the table once.
WITH exchangeratesordered AS (
SELECT e.currency, e.rate, ROW_NUMBER() OVER(PARTITION BY e.currency ORDER BY e.ddate DESC) AS rn
FROM exchangerates e
WHERE e.ddate < @d
)
SELECT c.ISO, e.rate
FROM exchangeratesordered AS e
INNER JOIN currencies c
ON e.currency = c.id
WHERE e.rn = 1
Also, this setup is likely to perform well with a cross apply in conjunction with a top 1, because it meets the criteria for this to perform well (although the sample is too small to demonstrate this). The criteria are that the number of records in the main query is small and that the number of records in each partition of the subquery is large. You also need an appropriate index in place on the exchangerates table.
SELECT c.ISO, e.rate
FROM currencies c
CROSS APPLY (
SELECT TOP 1 e.rate
FROM exchangerates e
WHERE c.id = e.currency
AND e.ddate < @d
ORDER BY e.ddate DESC
) e
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Hi Draw.
I tried your 2nd solution, and it is much slower than the earlier solutions... (for 160 currencies)
-
marc.corbeel (10/17/2016)
Hi Draw.I tried your 2nd solution, and it is much slower than the earlier solutions... (for 160 currencies)
I did say that it required that the appropriate index be in place. You didn't say whether you had an appropriate index.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply