Transaction table linked to dates
-
I have two tables, a transaction table with sales order line items, with the following (simplified) structure.
[docdate, product, unitprice, qty, currency]
The second table contains the relevant currency exchange rates entered by the accounts department.
[currency, ratedate, exch_rate]
Currencies are not entered everyday, but instead are entered as and when fluctutaion is great enough for the accts dept to enter a new one. What I want to do is query the two tables so that each sales order line item in the transaction table will have an exchange rate. If there is no exchange rate for that date of the transaction, then it would take the last entered exchange rate for that particular currency.
e.g. exchange rate table can look like
USD 01.01.2006 1.65
USD 05.01.2006 1.63
therefore, any sales with dates such as the following :
03.01.2006 XXYYZZ $10 100 USD (WOULD TAKE USD @ 1.65)
05.01.2006 XXYYZZ $10 50 USD (WOULD TAKE USD @ 1.63)
I'm a bit stuck on how to go forwards with this query...any help is greatly appreciated
-
Here is one solution:
create
table dbo.SalesLine (
docdate datetime,
product
varchar(12),
unitprice
decimal(12, 2),
qty
int,
currency
char(3)
)
create
table exchrate (
currency
char(3),
ratedate
datetime,
exchrate
decimal(5, 2)
)
insert
into dbo.SalesLine
values
('2006-01-03', 'XXYYZZ', 10.00, 100, 'USD')
insert
into dbo.SalesLine
values
('2006-01-05', 'XXYYZZ', 10.00, 50, 'USD')
insert
into dbo.exchrate
values
('USD', '2006-01-01', 1.65)
insert
into dbo.exchrate
values
('USD', '2006-01-05', 1.63)
select
sl.docdate,
sl
.product,
sl
.unitprice,
sl
.qty,
sl
.currency,
(select top 1
er
.exchrate
from
dbo
.exchrate er
where
er
.currency = sl.currency
and er.ratedate <= sl.docdate
order by
er
.ratedate desc) as exchrate
from
dbo
.SalesLine sl
Lynn
-
Thx!!!
That has worked a treat, and given me a good way to understand it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply