November 5, 2003 at 9:22 am
hello all
I'm pretty new to T/SQL so please excuse the ignorance. I have a csv file of exchange rates over time, only they only have the start date and not the end date (thank you IBM AS400). So I am going to have to calculate the end date as the start date -1 day of the following row. I have done something previously with Oracle where i made use of the internal row_id of the table and joined row_id to row_id-1, but I dont believe a similar system column is available within SQLServer. So I'm thinking that it is possible to do by sorting a query by start date and setting up 2 cursors - but my coding isnt up to it - can anyone help?
csv contains (for example):
currency varchar2(3),start_date date,value decimal (8,3)
EUR,01-01-2001,6.43
EUR,01-02-2001,6.44
EUR,01-03-2001,6.46
EUR,01-05-2001,6.47
Rgds
Ryan
November 5, 2003 at 11:24 am
No need for cursors if I understand the problem.
SELECT Currency, Value, Start_Date,
(SELECT MIN(Start_Date) - 1
FROM Csv
WHERE Currency = c.Currency
AND Start_Date > c.Start_Date) End_Date
FROM Csv c
--Jonathan
--Jonathan
November 5, 2003 at 12:41 pm
You could just calculate the value once it's in SQL by finding the row just before it (in terms of date) like this:
select
a.currency
, a.currdate 'current date'
, b.currdate 'last date'
from table a
inner join table b
on a.currency = b.currency
where b.currdate = ( select max( currdate)
from Table c
where c.currency = a.currency
and c.currdate < a.currdate
)
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 7, 2003 at 7:14 am
Thanks guys - works a treat!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply