Declare @TableA table (TrsId int,TrsDate datetime, CurcyCode int)
Declare @TableB table (CurcyCode int,ExchangeDate datetime,ExchangeRate int)
insert into @tablea
select 1,getdate(),10
union all
select 2,getdate()-1,11
insert into @tableb
select 10,getdate(),100
union all
select 11,getdate()-3,200
union all
select 11,getdate()-4,220
union all
select 10,getdate()-1,90
select * from @tablea
select * from @tableb
select t1.*,ISNULL(t2.ExchangeRate,x.ExchangeRate) as ExchangeRate from @tablea t1
left join @TableB t2 on t1.CurcyCode=t2.CurcyCode and t1.TrsDate=t2.ExchangeDate
outer apply ( select top(1) ExchangeRate from @tableB where ExchangeDate < t1.TrsDate order by ExchangeDate desc ) x
-----------------------------------------------
DECLARE @Transaction TABLE (
TrsID INT IDENTITY (1,1)
,TrsDate VARCHAR(20)
,CurcyCode VARCHAR(20)
)
INSERT INTO @Transaction VALUES ('2012-11-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-03', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-07', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-08', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-09', 'USD')
-----------------------------------------------
DECLARE @Exchange TABLE (
CurcyCode VARCHAR(20)
,ExchangeDate VARCHAR(20)
,ExchangeRate MONEY
)
INSERT INTO @Exchange VALUES ('GBP', '2012-12-01', '15000')
INSERT INTO @Exchange VALUES ('USD', '2012-12-02', '9161')
INSERT INTO @Exchange VALUES ('USD', '2012-12-03', '9159')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-04', '15350')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-05', '15500')
INSERT INTO @Exchange VALUES ('USD', '2012-12-06', '9165')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-10', '15750')
INSERT INTO @Exchange VALUES ('USD', '2012-12-11', '9175')
-----------------------------------------------
SELECT
*
,ISNULL((
SELECT TOP 1 e.ExchangeRate
FROM @Exchange e
WHERE e.CurcyCode = t.CurcyCode AND CONVERT(DATETIME, e.ExchangeDate) <= CONVERT(DATETIME, t.TrsDate )
ORDER BY CONVERT(DATETIME, e.ExchangeDate) DESC
), 0) AS ExchangeRate
FROM
@Transaction t
-----------------------------------------------
DECLARE @Transaction TABLE (
TrsID INT IDENTITY (1,1)
,TrsDate VARCHAR(20)
,CurcyCode VARCHAR(20)
)
INSERT INTO @Transaction VALUES ('2012-11-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-03', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-07', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-08', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-09', 'USD')
-----------------------------------------------
DECLARE @Exchange TABLE (
CurcyCode VARCHAR(20)
,ExchangeDate VARCHAR(20)
,ExchangeRate MONEY
)
INSERT INTO @Exchange VALUES ('GBP', '2012-11-01', '14000')
INSERT INTO @Exchange VALUES ('USD', '2012-06-14', '6000') -- New
INSERT INTO @Exchange VALUES ('GBP', '2012-12-01', '15000')
INSERT INTO @Exchange VALUES ('USD', '2012-12-02', '9161')
INSERT INTO @Exchange VALUES ('USD', '2012-12-03', '9159')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-04', '0')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-05', '15500')
INSERT INTO @Exchange VALUES ('USD', '2012-12-06', '9165')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-10', '15750')
INSERT INTO @Exchange VALUES ('USD', '2012-12-11', '9175')
-----------------------------------------------
UPDATE
@Exchange
SET
ExchangeRate = 0
WHERE
CURCYCODE = 'USD'
AND MONTH(ExchangeDate) = 6
AND DAY(ExchangeDate) BETWEEN 12 AND 15
SELECT * FROM @Exchange
SELECT
*
,ISNULL((
SELECT TOP 1 e.ExchangeRate
FROM @Exchange e
WHERE e.CurcyCode = t.CurcyCode AND CONVERT(DATETIME, e.ExchangeDate) <= CONVERT(DATETIME, t.TrsDate ) AND e.ExchangeRate != 0
ORDER BY CONVERT(DATETIME, e.ExchangeDate) DESC
), 0) AS ExchangeRate
FROM
@Transaction t
UPDATE
@Exchange
SET
ExchangeRate = 0
WHERE
CURCYCODE = 'USD'
AND MONTH(ExchangeDate) = 6
AND DAY(ExchangeDate) BETWEEN 12 AND 15
SELECT * FROM @Exchange
select TrsDate ,(select top(1) ExchangeDate from TableB b where b.ExchangeDate <=a.TrsDate order by b.ExchangeDate desc) ExchangeDate
from TableA a