Where to start? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where to start?

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-14 : 12:01:42

I'm really stuck and not sure if i can do this.

The objective is to look at each row of invoice data and populate the correct exchange rate for the row based on the Invoice Date field in the row.

The invoice table is pretty standard so i won't detail it too much (obviously it has an InvoiceDate field and a Currency Field)

The exchange rates are stored in another table called Currency.
The data is laid out below

StartDate TO FROM ExRate
2006-11-23 00:00:00.000 GBP EUR 1.44929
2007-01-12 00:00:00.000 GBP EUR 1.492537
2007-01-19 00:00:00.000 GBP USD 0.526316
2007-10-05 00:00:00.000 GBP EUR 0.680272
2008-03-11 00:00:00.000 GBP EUR 0.686672
2008-10-07 00:00:00.000 GBP USD 0.561136
2008-11-04 00:00:00.000 GBP USD 0.633252
2008-12-01 00:00:00.000 GBP USD 0.664355
2009-02-03 00:00:00.000 GBP EUR 0.833333
2009-02-03 00:00:00.000 GBP USD 0.693674
2009-03-02 00:00:00.000 GBP USD 0.698568
2010-01-01 00:00:00.000 GBP EUR 0.899685
2010-01-01 00:00:00.000 GBP USD 0.625
2009-02-04 00:00:00.000 GBP EUR 0.904977

You see that each possible option has a StartDate
I need to establish the range into which each invoice row fits.
i.e. If the Invoice is in USD and the InvoiceDate is the 5th NOV 2008 then the Exchange rate is 0.633252. (in Bold above) Because the rate for USD of 0.633252 was started on 4th NOV 2008 and teh next rate for USD didn't start until the 1st DEC 2008

Hope this makes sense.
I'm really stuck on this
So thanks for any help you can offer

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-14 : 23:56:20

With 18 reads and no replies , i guess this is impossible?
Or I haven't done a good job of explaining it?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 02:36:56

In our systems we have the exchange_rating table like this:
Date_start
Date_end
to
from
rate

and then it is easy to get the right record using invoice_date between Date_start and Date_end.

No, you're never too old to Yak'n'Roll if you're too young to die.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-15 : 02:49:37

Thanks Fred

I just figured it out using Cross Apply from this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147250

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources