October 20, 2008 at 4:13 pm
Hello,
I have a problem that I was able to “solve” using RBAR, but unfortunately this isn’t going to work for me long-term. (I wrote a horrible while loop that takes ages and will only get worse.) I know that a calendar tally is (probably) the solution, but I need some help getting it to work right.
I am trying to create a view of all currency conversion rates from all currencies we know about to all other currencies we know about for all dates from the first day we have data to the last day for which we have data. The trick comes in that we don’t have rate data for every day in that period, and for the missing days we should be using the most recent rate that is available on the day < the missing day (so if Monday and Tuesday are missing, both Monday and Tuesday will use Sunday’s rate, for example).
I can use functions, views, and stored procedures to achieve this, but ideally I’d like to be able to use a view.
The source table is just a list of dates, currencies, and the USD to that currency rate on the given day, plus a “factor” which indicates if the number should be ^-1 or as-is.
Here is some example SQL code to set up a simple version of my DB and to run the query that I would like to see as the result set from a view:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#MyTestRates','U') IS NOT NULL
DROP TABLE #MyTestRates
IF OBJECT_ID('TempDB..#MyTestCalendarTally','U') IS NOT NULL
DROP TABLE #MyTestCalendarTally
--===== Create the test table
Create table #MyTestRates (
[FromCurr] char(3),
[ToCurr] char(3),
[Date] datetime,
[Rate] numeric(38,16),
[Factor] int
)
--===== Create the test tally (hoping this is the solution)
Create table #MyTestCalendarTally (
[Date] datetime
)
-- INSERT SAMPLE DATA
insert into #MyTestRates values ('USD', 'IDR', '10/16/2008', 9818.0000000000000000, -1)
insert into #MyTestRates values ('USD', 'AUD', '10/16/2008', 0.6617000000000000, 1)
insert into #MyTestRates values ('USD', 'IDR', '10/20/2008', 9807.5000000000000000, -1)
insert into #MyTestRates values ('USD', 'AUD', '10/20/2008', 0.6889500000000000, 1)
insert into #MyTestCalendarTally values ('10/16/2008')
insert into #MyTestCalendarTally values ('10/17/2008')
insert into #MyTestCalendarTally values ('10/18/2008')
insert into #MyTestCalendarTally values ('10/19/2008')
insert into #MyTestCalendarTally values ('10/20/2008')
--===== Return the results... I want to be able to see the rates for all dates in the tally here...
select r1.[Date], r1.ToCurr as 'FromCurr', r2.ToCurr as 'FromCurr', Cast(Cast(r2.Rate as float(53)) /
Cast(r1.Rate as float(53)) as numeric(38,16)) as 'MidRate'
from #MyTestRates r1
INNER JOIN #MyTestRates r2 ON r1.[Date] = r2.[Date] and r1.ToCurr <> r2.ToCurr
As you can see, the code returns IDR to AUD and AUD to IDR for both Oct 16 and 20. I would like to fill in the blanks for Oct 17, 18, 19 with the data from the 16th. Please note that I'm still in SQL 2000 and we can't change this. Any thoughts? I’m sorry but I’m just having a mental block on this.
Kindest regards.
October 21, 2008 at 10:12 pm
sognibene,
I have been working on this to no avail. There were times when the correct result was right on the tip of my tongue, but I could not catch it. I realize the results are not correct, but I thought I would post what I have so far, to maybe give you some ideas, while I continue to work on it. Also, the best solution will not use a triangular join. I am hoping that one of the set based gurus will figure it out before I go crazy. Also, I added some extra dates for testing purposes
insert into #MyTestRates values ('USD', 'IDR', '10/15/2008', 9801.0000000000000000, -1) --Added to test
insert into #MyTestRates values ('USD', 'AUD', '10/15/2008', 0.6601000000000000, 1) --Added to test
insert into #MyTestRates values ('USD', 'IDR', '10/16/2008', 9818.0000000000000000, -1)
insert into #MyTestRates values ('USD', 'AUD', '10/16/2008', 0.6617000000000000, 1)
insert into #MyTestRates values ('USD', 'IDR', '10/20/2008', 9807.5000000000000000, -1)
insert into #MyTestRates values ('USD', 'AUD', '10/20/2008', 0.6889500000000000, 1)
insert into #MyTestCalendarTally values ('10/15/2008') --Added to test
insert into #MyTestCalendarTally values ('10/16/2008')
insert into #MyTestCalendarTally values ('10/17/2008')
insert into #MyTestCalendarTally values ('10/18/2008')
insert into #MyTestCalendarTally values ('10/19/2008')
insert into #MyTestCalendarTally values ('10/20/2008')
insert into #MyTestCalendarTally values ('10/21/2008') --Added to test
--===== Return the results... I want to be able to see the rates for all dates in the tally here...
--Create a temp table with all possible dates and FromCurr combinations
IF OBJECT_ID('TempDB..#AllDates','u') IS NOT NULL
DROP TABLE #AllDates
GO
CREATE TABLE #AllDates
(
ID INT IDENTITY,
[Date] DATETIME,
FromCurr1 CHAR(3),
FromCurr2 CHAR(3)
)
--Populate the table
INSERT INTO #AllDates
SELECT DISTINCT
c.[Date],
t1.FromCurr1,
t1.FromCurr2
FROM #MyTestCalendarTally c,
(
SELECT DISTINCT
FromCurr1 = r1.ToCurr,
FromCurr2 = r2.ToCurr
FROM
#MyTestRates r1,
#MyTestRates r2
WHERE r1.ToCurr <> r2.ToCurr
) t1
ORDER BY c.[Date],t1.FromCurr1
--Add the MidRate column, and populate the ones you *do* have
ALTER TABLE #AllDates
ADD MidRate NUMERIC(38,16)
GO
UPDATE d
SET MidRate = t1.MidRate
FROM #AllDates d LEFT OUTER JOIN
(
SELECT
r1.[Date],
FromCurr1 = r1.ToCurr,
FromCurr2 = r2.ToCurr,
MidRate = CAST(CAST(r2.Rate AS FLOAT(53))/CAST(r1.Rate AS FLOAT(53)) AS NUMERIC(38,16))
FROM #MyTestRates r1 INNER JOIN
#MyTestRates r2
ON r1.[Date] = r2.[Date]
AND r1.ToCurr <> r2.ToCurr
) t1
ON d.[date] = t1.[date]
AND d.FromCurr1 = t1.FromCurr1
AND d.FromCurr2 = t1.FromCurr2
--See the results
--Its not giving the correct results for the ones you don't have
SELECT
d1.[Date],
d1.FromCurr1,
d1.FromCurr2,
MidRate = ISNULL(d1.MidRate,
(SELECT
MAX(d2.MidRate)
FROM #AllDates d2
WHERE d2.FromCurr1 = d1.FromCurr1
AND d2.FromCurr2 = d2.FromCurr2
AND d2.[Date] <> d1.[Date]))
FROM #AllDates d1
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 21, 2008 at 11:24 pm
I went back and forth on even posting this as is, but maybe it will help you. This solution would build an all encompassing table that covered all currency types for all dates. Ideally, you'd either rebuild this thing using a SP on intervals of your choosing, and just join it to whatever queries you need.
Normally, I'd code all this up to be sure it worked first, but as my computer decided to die on me yesterday, I'm currently without a test environment. In any case, this is the logic I would attempt for this problem.
1. Build a table containing all your dates in the range (probably using a Tally Table)
2. Build a temp table containing distinct currency conversion types.
3. Cross join the two tables into a prebuilt temp table with an identity field.(So that you have a row for every type for every date)
4. Add a clustered index to the new table for currencytype1, currencytype2, date.(Very important, these have to be ordered properly)
5. Update the table with all the information you have for all the rates using normal joins.(to conversion type and date)
6. Ensure there is a value for the MIN(Date) for each conversion type (or you''ll get nothing until the first time there is one, or possibly carry over a different type, Add a 0 if you have no data, or handle it in the following update statement)
7. Run something close to this:
DECLARE @CurrencyRatefloat(53),
@CJIDINT
UPDATE #CJTable
SET @CurrencyRate = CASE WHEN CurrencyRate IS NULL THEN @CurrencyRate ELSE CurrencyRate END,
CurrencyRate = @CurrencyRate,
@CJID = CJID
FROM #CJTable
At that point, you should have a table containing data on every conversion type and date that you can join to. Not sure if maintaining a table with all conversion information is something that is feasible in your environment, but this would give it to you. The advantage to this over a view is that this should be very quick for queries against it(as opposed to any view that I can imagine, which would probably require RBAR). The disadvantage is that it is something that has to be maintained, and won't necessarily have up to the minute data (although it looks like this is specific to day, so keeping it up to date as of the current day shouldn't be an issue) Again, sorry for the somewhat vague answer, but until I get everything fixed, it's as far as I can go.
October 22, 2008 at 8:07 am
I really appreciate that help!
My original solution was a table-valued function that basically did a where loop and subtracted a day every time there was still a null in the result set. It actually isn't too terrible right now - my reports run in about a minute or so - but this is a very new database and I can't imagine what it will be like six months from now.
I will get back to this today in the afternoon and see if I can take another crack. Maintaining a temptable that gets updated every day sounds like a possibility but this is a 3rd party hosted DB and they may present a concern.
October 22, 2008 at 12:17 pm
sognibene
I know there is a better, set based solution, but my yellow belt SQL skills preclude me from grasping it. With that being said, I have a loop solution that works for the test data you provided, with the addition of two extra dates added by me for testing purposes. Basically, this is in line with Garadins's numbered steps. One major caveat that I know of is that there can not be an instance of one currency conversion having a value for a date, and one not having a value for the same date. I'm still messing with it, but I have an accounting exam to study for now, so this is it for today. So here it is.
The test set up again, including the extra data for the 15th and 21st that I added
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#MyTestRates','U') IS NOT NULL
DROP TABLE #MyTestRates
IF OBJECT_ID('TempDB..#MyTestCalendarTally','U') IS NOT NULL
DROP TABLE #MyTestCalendarTally
--===== Create the test table
Create table #MyTestRates (
[FromCurr] char(3),
[ToCurr] char(3),
[Date] datetime,
[Rate] numeric(38,16),
[Factor] int
)
--===== Create the test tally (hoping this is the solution)
Create table #MyTestCalendarTally (
[Date] datetime
) --Should be end paren here, but the face shows up
insert into #MyTestRates values ('USD', 'IDR', '10/15/2008', 9801.0000000000000000, -1) --Added to test
insert into #MyTestRates values ('USD', 'AUD', '10/15/2008', 0.6601000000000000, 1) --Added to test
insert into #MyTestRates values ('USD', 'IDR', '10/16/2008', 9818.0000000000000000, -1)
insert into #MyTestRates values ('USD', 'AUD', '10/16/2008', 0.6617000000000000, 1)
insert into #MyTestRates values ('USD', 'IDR', '10/20/2008', 9807.5000000000000000, -1)
insert into #MyTestRates values ('USD', 'AUD', '10/20/2008', 0.6889500000000000, 1)
insert into #MyTestCalendarTally values ('10/15/2008') --Added to test
insert into #MyTestCalendarTally values ('10/16/2008')
insert into #MyTestCalendarTally values ('10/17/2008')
insert into #MyTestCalendarTally values ('10/18/2008')
insert into #MyTestCalendarTally values ('10/19/2008')
insert into #MyTestCalendarTally values ('10/20/2008')
insert into #MyTestCalendarTally values ('10/21/2008') --Added to test
Now, create an all encompassing table using your original query, and populate it with all dates in the range you want, with all conversion types, and the MidRates you do have. This would be akin to Garadin's number 5
--Create a temp table with all possible dates and FromCurr combinations
IF OBJECT_ID('TempDB..#AllDates','u') IS NOT NULL
DROP TABLE #AllDates
GO
CREATE TABLE #AllDates
(
ID INT IDENTITY,
[Date] DATETIME,
FromCurr1 CHAR(3),
FromCurr2 CHAR(3)
)
--Populate the table
INSERT INTO #AllDates
SELECT DISTINCT
c.[Date],
t1.FromCurr1,
t1.FromCurr2
FROM #MyTestCalendarTally c,
(
SELECT DISTINCT
FromCurr1 = r1.ToCurr,
FromCurr2 = r2.ToCurr
FROM
#MyTestRates r1,
#MyTestRates r2
WHERE r1.ToCurr <> r2.ToCurr
) t1
ORDER BY c.[Date],t1.FromCurr1
--Add the MidRate column, and populate the ones you *do* have
ALTER TABLE #AllDates
ADD MidRate NUMERIC(38,16)
GO
UPDATE d
SET MidRate = t1.MidRate
FROM #AllDates d LEFT OUTER JOIN
(--Select the dates for which there is MidRate, this is your original query
SELECT
r1.[Date],
FromCurr1 = r1.ToCurr,
FromCurr2 = r2.ToCurr,
MidRate = CAST(CAST(r2.Rate AS FLOAT(53))/CAST(r1.Rate AS FLOAT(53)) AS NUMERIC(38,16))
FROM #MyTestRates r1 INNER JOIN
#MyTestRates r2
ON r1.[Date] = r2.[Date]
AND r1.ToCurr <> r2.ToCurr
) t1
ON d.[date] = t1.[date]
AND d.FromCurr1 = t1.FromCurr1
AND d.FromCurr2 = t1.FromCurr2
--Verify the results
SELECT
*
FROM #AllDates
Now, update the missing MidRates in #AllDates with the MidRate of the most recent date that has values. Again, this is RBAR, and will probably not perform well as your database grows. You could turn it into a procedure, though, and run it at 2 AM, or what ever time your server demand is lowest. I hope this helps.
DECLARE @Counter INT
DECLARE @MaxCount INT--Number of rows with no MidRate
SET @Counter = 1
SET @MaxCount = (SELECT COUNT(*)
FROM #AllDates
WHERE MidRate IS NULL)
WHILE @Counter < @MaxCount
BEGIN
UPDATE d3
SET MidRate = d4.Midrate
FROM #AllDates d3,
(--Add in the MidRate from the most recent one you have
SELECT
MaxDate,
d2.FromCurr1,
d2.FromCurr2,
d1.MidRate
FROM #AllDates d1,
(--Select the lowest date with no MidRate
SELECT
FromCurr1,
FromCurr2,
MaxDate = MIN(DATEADD(d,-1,[date]))
FROM #AllDates
WHERE MidRate IS NULL
GROUP BY FromCurr1,FromCurr2
) d2--should be end paren where the face is, before 'd2'
WHERE d1.[date] = d2.MaxDate
AND d1.FromCurr1 = d2.FromCurr1
AND d1.FromCurr2 = d2.FromCurr2
) d4
WHERE DATEADD(d,-1,d3.[date]) = d4.MaxDate
AND d3.FromCurr1 = d4.FromCurr1
AND d3.FromCurr2 = d4.FromCurr2
SET @Counter = @Counter + 1
END
--Verify the results
SELECT
*
FROM #AllDates
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2008 at 12:50 pm
I am somewhat skeptical that all of your requirements can be met using SQL Server 2000 (That is, non-RBAR, non-Triangular View). however, I am willing to give it a try.
Some Questions:
1) What is the earliest date that you need to support?
2) What is the latest date that you need to support?
3) How many different currencies do you know about?
4) What is the frequency of currency changes per day?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 1:43 pm
Barry
One more thing you might want to consider, is the scenario that broke my loop situation. Is there ever going to be an instance where for a given date, one conversion has a rate, and another does not have one for the same date. For example
DATE FromCurr ToCurr RATE
10/1/2008 USD IDR 9801.0000000000000000
10/1/2008 USD AUD NULL
OR
DATE FromCurr ToCurr RATE
10/1/2008 USD IDR 9801.0000000000000000
10/1/2008 USD AUD 0.6601000000000000
10/1/2008 USD ABC NULL
10/1/2008 USD DEF NULL
Just a thought.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2008 at 8:16 pm
OK, this should do it.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#MyTestRates','U') IS NOT NULL
DROP TABLE #MyTestRates
IF OBJECT_ID('TempDB..#MyTestCalendarTally','U') IS NOT NULL
DROP TABLE #MyTestCalendarTally
IF OBJECT_ID('TempDB..#MyTestCurrencies','U') IS NOT NULL
DROP TABLE #MyTestCurrencies
IF OBJECT_ID('TempDB..#Tally','U') IS NOT NULL
DROP TABLE #Tally
--===== Create the test tables
Create table #MyTestCurrencies(
Country char(3)
, Freq float
, base float
, mag float
, period float)
Create table #MyTestRates (
[FromCurr] char(3),
[ToCurr] char(3),
[Date] datetime,
[Rate] numeric(38,16),
[Factor] int
CONSTRAINT [PK_MyTestRates] PRIMARY KEY CLUSTERED
(ToCurr ASC, Date ASC)
)
Select Identity(int) as Num
Into #Tally
From master..syscolumns
Alter Table #Tally
Add CONSTRAINT [PK_tmpTally] PRIMARY KEY CLUSTERED
(Num ASC)
--===== Create the test tally (hoping this is the solution)
Create table #MyTestCalendarTally (
[Date] datetime, Num int
)
-- INSERT SAMPLE DATA
--====== Create more extensive sample data
Set Rowcount 30
Insert into #MyTestCurrencies
Select Upper(Left(Name,3))
, 1.0+((min(colorder)-1.0-+(count(*))/100.0)/1.414)
, count(*)
, (count(*)+3.0)/avg(len(name))
, avg(xtype - 1.0)
From master..syscolumns
Where Len(Name) >= 3
And Not Name Like '@%'
Group by Left(Name, 3)
Set RowCount 0
Insert into #MyTestCalendarTally
Select DateAdd(dd, Num-1, '2005-01-01'), Num-1
From #Tally
Where Num < 2000
Insert into #MyTestRates
Select 'USD'
, Country
, Date
, base + Mag*Sin((2.0*3.14157*Num)/period)
, 1
From #MyTestCurrencies
Cross Join #MyTestCalendarTally
Where Num - floor(Num/freq)*freq < 1.0
--====== Simulate a reasonable select range
declare @StartDate datetime
Set @StartDate = DateAdd(dd, -30, GetDate())
--====== OK, let's do it:
select r1.[Date]
, r1.ToCurr as 'FromCurr'
, r2.ToCurr as 'ToCurr'
, Cast(Cast(r2.Rate as float(53)) /
Cast(r1.Rate as float(53)) as numeric(38,16)) as 'MidRate'
From #myTestCurrencies C1 --ON 1=1
Join #myTestCurrencies C2 ON C1.country <> C2.country
Join #MyTestCalendarTally D ON D.Date <= GetDate()
And D.Date >= @StartDate
Join #MyTestRates R1 ON R1.ToCurr = C1.Country
And R1.Date = (Select MAX(RX.Date) From #MyTestRates RX
Where RX.ToCurr = C1.Country
And RX.Date <= D.Date)
Join #MyTestRates R2 ON R2.ToCurr = C2.Country
And R2.Date = (Select MAX(RX.Date) From #MyTestRates RX
Where RX.ToCurr = C2.Country
And RX.Date <= D.Date)
This whole thing runs in about 2 seconds on my laptop. And most of that is the setup.
Please note the Primary Key & clustered index on the rates table. This is critical to making this work.
The #myTestCurrencies is very useful, but you can work around it if you need to.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 23, 2008 at 7:35 am
Wow! Thanks so much for the feedback, Greg, rbarryyoung and the others. I am working right now on compiling all of this into a working solution.
Note for rbarryyoung: the code you sent seems to choke for SQL 2000 when building #MyTestCurrencies - it thinks Name isn't in the group by. No matter, since this is sample data I can just work around that with a smaller set of sample data with manual inserts. I also had to change r1.[Date] to D.[Date] in the final select.
Note for Greg: yes - that is something that we have to consider in terms of a rate being missing on a day. Also possible is a new currency being added which would introduce the possibility of issues with old dates.
I will post what I am able to get working. Thank you so much!
By the way - since I am a VB Dev wearing a SQL hat - what would be the way you hinted at doing this in SQL 2005+?
October 23, 2008 at 7:44 am
sognibene, no clue about 2005.
Barry, just so you know...I'm sucking my thumb right now. Thanks so much for lending us your time an superior expertise, I have so much to absorb from your solution.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 23, 2008 at 7:50 am
sognibene (10/23/2008)
Note for rbarryyoung: the code you sent seems to choke for SQL 2000 when building #MyTestCurrencies - it thinks Name isn't in the group by.
This will fix it.
Group by Upper(Left(Name, 3))
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 23, 2008 at 8:04 am
Thanks for the assist Greg. I don't usually have a SQL 2000 server available to test on. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 23, 2008 at 2:05 pm
Here's a solution that should work on SQL 2000, but if table vars are 2005 only, just convert them to temp tables:
--===== Create the test rates table
DECLARE @MyTestRates TABLE (
FromCurr char(3),
ToCurr char(3),
Date DateTime,
Rate numeric(38,16),
Factor int
)
insert into @MyTestRates values ('USD', 'IDR', '10/15/2008', 9801.0000000000000000, -1)
insert into @MyTestRates values ('USD', 'AUD', '10/15/2008', 0.6601000000000000, 1)
insert into @MyTestRates values ('USD', 'IDR', '10/16/2008', 9818.0000000000000000, -1)
insert into @MyTestRates values ('USD', 'AUD', '10/16/2008', 0.6617000000000000, 1)
insert into @MyTestRates values ('USD', 'IDR', '10/20/2008', 9807.5000000000000000, -1)
insert into @MyTestRates values ('USD', 'AUD', '10/20/2008', 0.6889500000000000, 1)
--===== Create the test tally table
DECLARE @MyTestCalendarTally TABLE (
Date DateTime
)
insert into @MyTestCalendarTally values ('10/15/2008')
insert into @MyTestCalendarTally values ('10/16/2008')
insert into @MyTestCalendarTally values ('10/17/2008')
insert into @MyTestCalendarTally values ('10/18/2008')
insert into @MyTestCalendarTally values ('10/19/2008')
insert into @MyTestCalendarTally values ('10/20/2008')
insert into @MyTestCalendarTally values ('10/21/2008')
--===== Create a table to hold ALL currency pairs
DECLARE @CURRENCY_PAIRS TABLE (
FromCurr char(3),
ToCurr char(3)
PRIMARY KEY(FromCurr, ToCurr)
)
INSERT INTO @CURRENCY_PAIRS
SELECT DISTINCT FromCurr, ToCurr
FROM @MyTestRates
--===== Create a results table and initially populate with all combos of dates and currency pairs
DECLARE @DATES_AND_PAIRS TABLE (
FromCurr char(3),
ToCurr char(3),
Date DateTime,
Rate numeric(38,16),
Factor int
PRIMARY KEY(FromCurr, ToCurr, Date)
)
INSERT INTO @DATES_AND_PAIRS (Date, FromCurr, ToCurr)
SELECT C.Date, P.FromCurr, P.ToCurr
FROM @MyTestCalendarTally AS C, @CURRENCY_PAIRS AS P
--===== Update the table with existing Rates and Factors
UPDATE @DATES_AND_PAIRS
SET Rate = R.Rate, Factor = R.Factor
FROM @MyTestRates AS R INNER JOIN @DATES_AND_PAIRS AS DP
ON R.Date = DP.Date AND
R.FromCurr = DP.FromCurr AND
R.ToCurr = DP.ToCurr
--===== Update the table again to propagate rates forward by date
WHILE EXISTS (SELECT * FROM @DATES_AND_PAIRS WHERE Rate IS NULL)
BEGIN
UPDATE D1
SET Rate = D2.Rate, Factor = D2.Factor
FROM @DATES_AND_PAIRS AS D1 INNER JOIN @DATES_AND_PAIRS AS D2
ON D1.FromCurr = D2.FromCurr AND
D1.ToCurr = D2.ToCurr AND
D1.Rate IS NULL AND
D1.Date = DateAdd(d, 1, D2.Date)
WHERE D1.Rate IS NULL
END
--===== Show us the results
SELECT *
FROM @DATES_AND_PAIRS
ORDER BY FromCurr, ToCurr, Date
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2008 at 2:21 pm
I just realized my code will do an infinite loop if you add a new currency pair and don't have a rate for the first date in the range. Here's the mod:
--===== Create the test rates table
DECLARE @MyTestRates TABLE (
FromCurr char(3),
ToCurr char(3),
Date DateTime,
Rate numeric(38,16),
Factor int
)
insert into @MyTestRates values ('USD', 'IDR', '10/15/2008', 9801.0000000000000000, -1)
insert into @MyTestRates values ('USD', 'AUD', '10/15/2008', 0.6601000000000000, 1)
insert into @MyTestRates values ('USD', 'IDR', '10/16/2008', 9818.0000000000000000, -1)
insert into @MyTestRates values ('USD', 'AUD', '10/16/2008', 0.6617000000000000, 1)
insert into @MyTestRates values ('USD', 'IDR', '10/20/2008', 9807.5000000000000000, -1)
insert into @MyTestRates values ('USD', 'AUD', '10/20/2008', 0.6889500000000000, 1)
--===== Create the test tally table
DECLARE @MyTestCalendarTally TABLE (
Date DateTime
)
insert into @MyTestCalendarTally values ('10/14/2008')
insert into @MyTestCalendarTally values ('10/15/2008')
insert into @MyTestCalendarTally values ('10/16/2008')
insert into @MyTestCalendarTally values ('10/17/2008')
insert into @MyTestCalendarTally values ('10/18/2008')
insert into @MyTestCalendarTally values ('10/19/2008')
insert into @MyTestCalendarTally values ('10/20/2008')
insert into @MyTestCalendarTally values ('10/21/2008')
--===== Create a table to hold ALL currency pairs
DECLARE @CURRENCY_PAIRS TABLE (
FromCurr char(3),
ToCurr char(3)
PRIMARY KEY(FromCurr, ToCurr)
)
INSERT INTO @CURRENCY_PAIRS
SELECT DISTINCT FromCurr, ToCurr
FROM @MyTestRates
--===== Create a results table and initially populate with all combos of dates and currency pairs
DECLARE @DATES_AND_PAIRS TABLE (
FromCurr char(3),
ToCurr char(3),
Date DateTime,
Rate numeric(38,16),
Factor int
PRIMARY KEY(FromCurr, ToCurr, Date)
)
INSERT INTO @DATES_AND_PAIRS (Date, FromCurr, ToCurr)
SELECT C.Date, P.FromCurr, P.ToCurr
FROM @MyTestCalendarTally AS C, @CURRENCY_PAIRS AS P
--===== Update the table with existing Rates and Factors
UPDATE @DATES_AND_PAIRS
SET Rate = R.Rate, Factor = R.Factor
FROM @MyTestRates AS R INNER JOIN @DATES_AND_PAIRS AS DP
ON R.Date = DP.Date AND
R.FromCurr = DP.FromCurr AND
R.ToCurr = DP.ToCurr
--===== Update the table again to propagate rates forward by date
WHILE EXISTS (SELECT * FROM @DATES_AND_PAIRS WHERE Rate IS NULL)
BEGIN
UPDATE D1
SET Rate = D2.Rate, Factor = D2.Factor
FROM @DATES_AND_PAIRS AS D1 INNER JOIN @DATES_AND_PAIRS AS D2
ON D1.FromCurr = D2.FromCurr AND
D1.ToCurr = D2.ToCurr AND
D1.Rate IS NULL AND
D1.Date = DateAdd(d, 1, D2.Date)
WHERE D1.Rate IS NULL
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
END
--===== Show us the results
SELECT *
FROM @DATES_AND_PAIRS
ORDER BY FromCurr, ToCurr, Date
The net result now is that rows for dates with no historical rate get a NULL value for Rate and Factor. You could also choose to DELETE any remaining rows with NULL values. The @@ROWCOUNT check determines that the last update didn't do anything, and breaks out of the WHILE loop.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2008 at 2:45 pm
smunson (10/23/2008)
I just realized my code will do an infinite loop if you add a new currency pair and don't have a rate for the first date in the range
I did not even think of that in my code...I wonder what will happen.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply