Transaction table linked to dates
-
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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply