Get last non null value from column

Hi I have tried a number of different attempts to get this going and am not having any luck,

I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday

Here was my latest attempt:

;with cte as 
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
from cte A
outer Apply (
select top 1 * from cte
where [From] = a.[From] and [TO] = a.[To] and
CurrXDate < a.CurrXDate
and CurrXRate is not null
order by a.CurrXDate desc) b
order by a.CurrXDate

Open in new window


Test data

 IF OBJECT_ID('TempDB..#CurrXChange','U') IS NOT NULL
         DROP TABLE #CurrXChange

--===== Create the test table with 
 CREATE TABLE #CurrXChange
        (
        CurrencyID nvarchar(50),
        CurrXDate DATETIME,
        CurrFrom nvarchar(50),
        CurrTo nvarchar(50),
        CurrXRate nvarchar(50)
        )

insert into #CurrXChange
(CurrencyID, CurrXDate, CurrFrom, CurrTo, CurrXRate)
--select 'Select '
--+ QUOTENAME(CurrencyID, '''')+','
--+ QUOTENAME(CurrXDate, '''')+','
--+ Quotename([From], '''')+','
--+ QUOTENAME([To], '''')+','
--+ QUOTENAME(isnull(CurrXRate,0), '''')
--+' Union All'
--from CurrXchange
--where CurrXDate > GETDATE()-45
Select '16525','Apr 13 2015 12:00AM','CAD','USD','1.2602' Union All
Select '16526','Apr 10 2015 12:00AM','CAD','USD','1.2595' Union All
Select '16527','Apr  9 2015 12:00AM','CAD','USD','1.258' Union All
Select '16528','Apr  8 2015 12:00AM','CAD','USD','1.2508' Union All
Select '16529','Apr  7 2015 12:00AM','CAD','USD','1.2488' Union All
Select '16530','Apr  6 2015 12:00AM','CAD','USD','1.2452' Union All
Select '16531','Apr  3 2015 12:00AM','CAD','USD','0' Union All
Select '16532','Apr  2 2015 12:00AM','CAD','USD','1.2585' Union All
Select '16533','Apr  1 2015 12:00AM','CAD','USD','1.2612' Union All
Select '16534','Mar 31 2015 12:00AM','CAD','USD','1.2683' Union All
Select '16535','Mar 30 2015 12:00AM','CAD','USD','1.2689' Union All
Select '16536','Mar 27 2015 12:00AM','CAD','USD','1.258' Union All
Select '16537','Mar 26 2015 12:00AM','CAD','USD','1.2471' Union All
Select '16538','Mar 25 2015 12:00AM','CAD','USD','1.2513' Union All
Select '16539','Mar 24 2015 12:00AM','CAD','USD','1.2511' Union All
Select '16540','Mar 23 2015 12:00AM','CAD','USD','1.2516' Union All
Select '16541','Mar 20 2015 12:00AM','CAD','USD','1.2595' Union All
Select '16542','Mar 19 2015 12:00AM','CAD','USD','1.2744' Union All
Select '16543','Mar 18 2015 12:00AM','CAD','USD','1.2771' Union All
Select '16544','Mar 17 2015 12:00AM','CAD','USD','1.2769' Union All
Select '16545','Mar 16 2015 12:00AM','CAD','USD','1.2765' Union All
Select '16546','Mar 13 2015 12:00AM','CAD','USD','1.2803' Union All
Select '16547','Mar 12 2015 12:00AM','CAD','USD','1.2691' Union All
Select '16548','Mar 11 2015 12:00AM','CAD','USD','1.2764' Union All
Select '16549','Mar 10 2015 12:00AM','CAD','USD','1.2633' Union All
Select '16550','Mar  9 2015 12:00AM','CAD','USD','1.2598' Union All
Select '16551','Mar  6 2015 12:00AM','CAD','USD','1.2616' Union All
Select '16552','Mar  5 2015 12:00AM','CAD','USD','1.2482' Union All
Select '16553','Mar  4 2015 12:00AM','CAD','USD','1.244' Union All
Select '16554','Mar  3 2015 12:00AM','CAD','USD','1.2452' Union All
Select '16555','Mar  2 2015 12:00AM','CAD','USD','1.2535' Union All
Select '17902','Apr 13 2015 12:00AM','USD','CAD','0.7935' Union All
Select '17903','Apr 10 2015 12:00AM','USD','CAD','0.794' Union All
Select '17904','Apr  9 2015 12:00AM','USD','CAD','0.7949' Union All
Select '17905','Apr  8 2015 12:00AM','USD','CAD','0.7995' Union All
Select '17906','Apr  7 2015 12:00AM','USD','CAD','0.8008' Union All
Select '17907','Apr  6 2015 12:00AM','USD','CAD','0.8031' Union All
Select '17908','Apr  3 2015 12:00AM','USD','CAD','0' Union All
Select '17909','Apr  2 2015 12:00AM','USD','CAD','0.7946' Union All
Select '17910','Apr  1 2015 12:00AM','USD','CAD','0.7929' Union All
Select '17911','Mar 31 2015 12:00AM','USD','CAD','0.7885' Union All
Select '17912','Mar 30 2015 12:00AM','USD','CAD','0.7881' Union All
Select '17913','Mar 27 2015 12:00AM','USD','CAD','0.7949' Union All
Select '17914','Mar 26 2015 12:00AM','USD','CAD','0.8019' Union All
Select '17915','Mar 25 2015 12:00AM','USD','CAD','0.7992' Union All
Select '17916','Mar 24 2015 12:00AM','USD','CAD','0.7993' Union All
Select '17917','Mar 23 2015 12:00AM','USD','CAD','0.799' Union All
Select '17918','Mar 20 2015 12:00AM','USD','CAD','0.794' Union All
Select '17919','Mar 19 2015 12:00AM','USD','CAD','0.7847' Union All
Select '17920','Mar 18 2015 12:00AM','USD','CAD','0.783' Union All
Select '17921','Mar 17 2015 12:00AM','USD','CAD','0.7831' Union All
Select '17922','Mar 16 2015 12:00AM','USD','CAD','0.7834' Union All
Select '17923','Mar 13 2015 12:00AM','USD','CAD','0.7811' Union All
Select '17924','Mar 12 2015 12:00AM','USD','CAD','0.788' Union All
Select '17925','Mar 11 2015 12:00AM','USD','CAD','0.7835' Union All
Select '17926','Mar 10 2015 12:00AM','USD','CAD','0.7916' Union All
Select '17927','Mar  9 2015 12:00AM','USD','CAD','0.7938' Union All
Select '17928','Mar  6 2015 12:00AM','USD','CAD','0.7926' Union All
Select '17929','Mar  5 2015 12:00AM','USD','CAD','0.8012' Union All
Select '17930','Mar  4 2015 12:00AM','USD','CAD','0.8039' Union All
Select '17931','Mar  3 2015 12:00AM','USD','CAD','0.8031' Union All
Select '17932','Mar  2 2015 12:00AM','USD','CAD','0.7978' Union All
Select '9640','Apr 13 2015 12:00AM','MXN','CAD','12.1492' Union All
Select '9641','Apr 10 2015 12:00AM','MXN','CAD','12.0424' Union All
Select '9642','Apr  9 2015 12:00AM','MXN','CAD','11.9703' Union All
Select '9643','Apr  8 2015 12:00AM','MXN','CAD','11.919' Union All
Select '9644','Apr  7 2015 12:00AM','MXN','CAD','11.9489' Union All
Select '9645','Apr  6 2015 12:00AM','MXN','CAD','11.9062' Union All
Select '9646','Apr  3 2015 12:00AM','MXN','CAD','0' Union All
Select '9647','Apr  2 2015 12:00AM','MXN','CAD','11.9732' Union All
Select '9648','Apr  1 2015 12:00AM','MXN','CAD','11.9847' Union All
Select '9649','Mar 31 2015 12:00AM','MXN','CAD','12.0207' Union All
Select '9650','Mar 30 2015 12:00AM','MXN','CAD','12.0192' Union All
Select '9651','Mar 27 2015 12:00AM','MXN','CAD','12.0642' Union All
Select '9652','Mar 26 2015 12:00AM','MXN','CAD','12.0963' Union All
Select '9653','Mar 25 2015 12:00AM','MXN','CAD','11.936' Union All
Select '9654','Mar 24 2015 12:00AM','MXN','CAD','11.9446' Union All
Select '9655','Mar 23 2015 12:00AM','MXN','CAD','11.9517' Union All
Select '9656','Mar 20 2015 12:00AM','MXN','CAD','11.9517' Union All
Select '9657','Mar 19 2015 12:00AM','MXN','CAD','12.0091' Union All
Select '9658','Mar 18 2015 12:00AM','MXN','CAD','12.0802' Union All
Select '9659','Mar 17 2015 12:00AM','MXN','CAD','12.0802' Union All
Select '9660','Mar 16 2015 12:00AM','MXN','CAD','12.0831' Union All
Select '9661','Mar 13 2015 12:00AM','MXN','CAD','12.108' Union All
Select '9662','Mar 12 2015 12:00AM','MXN','CAD','12.1095' Union All
Select '9663','Mar 11 2015 12:00AM','MXN','CAD','12.1139' Union All
Select '9664','Mar 10 2015 12:00AM','MXN','CAD','12.335' Union All
Select '9665','Mar  9 2015 12:00AM','MXN','CAD','12.2745' Union All
Select '9666','Mar  6 2015 12:00AM','MXN','CAD','12.2474' Union All
Select '9667','Mar  5 2015 12:00AM','MXN','CAD','12.1655' Union All
Select '9668','Mar  4 2015 12:00AM','MXN','CAD','12.1256' Union All
Select '9669','Mar  3 2015 12:00AM','MXN','CAD','11.9962' Union All
Select '9670','Mar  2 2015 12:00AM','MXN','CAD','11.9603' Union All
Select '8263','Apr 13 2015 12:00AM','CAD','MXN','0.08231' Union All
Select '8264','Apr 10 2015 12:00AM','CAD','MXN','0.08304' Union All
Select '8265','Apr  9 2015 12:00AM','CAD','MXN','0.08354' Union All
Select '8266','Apr  8 2015 12:00AM','CAD','MXN','0.0839' Union All
Select '8267','Apr  7 2015 12:00AM','CAD','MXN','0.08369' Union All
Select '8268','Apr  6 2015 12:00AM','CAD','MXN','0.08399' Union All
Select '8269','Apr  3 2015 12:00AM','CAD','MXN','0' Union All
Select '8270','Apr  2 2015 12:00AM','CAD','MXN','0.08352' Union All
Select '8271','Apr  1 2015 12:00AM','CAD','MXN','0.08344' Union All
Select '8272','Mar 31 2015 12:00AM','CAD','MXN','0.08319' Union All
Select '8273','Mar 30 2015 12:00AM','CAD','MXN','0.0832' Union All
Select '8274','Mar 27 2015 12:00AM','CAD','MXN','0.08289' Union All
Select '8275','Mar 26 2015 12:00AM','CAD','MXN','0.08267' Union All
Select '8276','Mar 25 2015 12:00AM','CAD','MXN','0.08378' Union All
Select '8277','Mar 24 2015 12:00AM','CAD','MXN','0.08372' Union All
Select '8278','Mar 23 2015 12:00AM','CAD','MXN','0.08367' Union All
Select '8279','Mar 20 2015 12:00AM','CAD','MXN','0.08367' Union All
Select '8280','Mar 19 2015 12:00AM','CAD','MXN','0.08327' Union All
Select '8281','Mar 18 2015 12:00AM','CAD','MXN','0.08278' Union All
Select '8282','Mar 17 2015 12:00AM','CAD','MXN','0.08278' Union All
Select '8283','Mar 16 2015 12:00AM','CAD','MXN','0.08276' Union All
Select '8284','Mar 13 2015 12:00AM','CAD','MXN','0.08259' Union All
Select '8285','Mar 12 2015 12:00AM','CAD','MXN','0.08258' Union All
Select '8286','Mar 11 2015 12:00AM','CAD','MXN','0.08255' Union All
Select '8287','Mar 10 2015 12:00AM','CAD','MXN','0.08107' Union All
Select '8288','Mar  9 2015 12:00AM','CAD','MXN','0.08147' Union All
Select '8289','Mar  6 2015 12:00AM','CAD','MXN','0.08165' Union All
Select '8290','Mar  5 2015 12:00AM','CAD','MXN','0.0822' Union All
Select '8291','Mar  4 2015 12:00AM','CAD','MXN','0.08247' Union All
Select '8292','Mar  3 2015 12:00AM','CAD','MXN','0.08336' Union All
Select '8293','Mar  2 2015 12:00AM','CAD','MXN','0.08361' Union All
Select '11017','Apr 13 2015 12:00AM','MXN','USD','15.3104' Union All
Select '11018','Apr 10 2015 12:00AM','MXN','USD','15.1674' Union All
Select '11019','Apr  9 2015 12:00AM','MXN','USD','15.0587' Union All
Select '11020','Apr  8 2015 12:00AM','MXN','USD','14.9082' Union All
Select '11021','Apr  7 2015 12:00AM','MXN','USD','14.9217' Union All
Select '11022','Apr  6 2015 12:00AM','MXN','USD','14.8256' Union All
Select '11023','Apr  3 2015 12:00AM','MXN','USD','0' Union All
Select '11024','Apr  2 2015 12:00AM','MXN','USD','15.0682' Union All
Select '11025','Apr  1 2015 12:00AM','MXN','USD','15.1151' Union All
Select '11026','Mar 31 2015 12:00AM','MXN','USD','15.2458' Union All
Select '11027','Mar 30 2015 12:00AM','MXN','USD','15.2512' Union All
Select '11028','Mar 27 2015 12:00AM','MXN','USD','15.1767' Union All
Select '11029','Mar 26 2015 12:00AM','MXN','USD','15.0853' Union All
Select '11030','Mar 25 2015 12:00AM','MXN','USD','14.9355' Union All
Select '11031','Mar 24 2015 12:00AM','MXN','USD','14.9439' Union All
Select '11032','Mar 23 2015 12:00AM','MXN','USD','14.9588' Union All
Select '11033','Mar 20 2015 12:00AM','MXN','USD','15.0532' Union All
Select '11034','Mar 19 2015 12:00AM','MXN','USD','15.3044' Union All
Select '11035','Mar 18 2015 12:00AM','MXN','USD','15.4276' Union All
Select '11036','Mar 17 2015 12:00AM','MXN','USD','15.4252' Union All
Select '11037','Mar 16 2015 12:00AM','MXN','USD','15.4241' Union All
Select '11038','Mar 13 2015 12:00AM','MXN','USD','15.5019' Union All
Select '11039','Mar 12 2015 12:00AM','MXN','USD','15.3681' Union All
Select '11040','Mar 11 2015 12:00AM','MXN','USD','15.4621' Union All
Select '11041','Mar 10 2015 12:00AM','MXN','USD','15.5828' Union All
Select '11042','Mar  9 2015 12:00AM','MXN','USD','15.4634' Union All
Select '11043','Mar  6 2015 12:00AM','MXN','USD','15.4513' Union All
Select '11044','Mar  5 2015 12:00AM','MXN','USD','15.1849' Union All
Select '11045','Mar  4 2015 12:00AM','MXN','USD','15.0843' Union All
Select '11046','Mar  3 2015 12:00AM','MXN','USD','14.9376' Union All
Select '11047','Mar  2 2015 12:00AM','MXN','USD','14.9922' Union All
Select '12394','Apr 13 2015 12:00AM','USD','MXN','0.06532' Union All
Select '12395','Apr 10 2015 12:00AM','USD','MXN','0.06593' Union All
Select '12396','Apr  9 2015 12:00AM','USD','MXN','0.06641' Union All
Select '12397','Apr  8 2015 12:00AM','USD','MXN','0.06708' Union All
Select '12398','Apr  7 2015 12:00AM','USD','MXN','0.06702' Union All
Select '12399','Apr  6 2015 12:00AM','USD','MXN','0.06745' Union All
Select '12400','Apr  3 2015 12:00AM','USD','MXN','0' Union All
Select '12401','Apr  2 2015 12:00AM','USD','MXN','0.06636' Union All
Select '12402','Apr  1 2015 12:00AM','USD','MXN','0.06616' Union All
Select '12403','Mar 31 2015 12:00AM','USD','MXN','0.06559' Union All
Select '12404','Mar 30 2015 12:00AM','USD','MXN','0.06557' Union All
Select '12405','Mar 27 2015 12:00AM','USD','MXN','0.06589' Union All
Select '12406','Mar 26 2015 12:00AM','USD','MXN','0.06629' Union All
Select '12407','Mar 25 2015 12:00AM','USD','MXN','0.06695' Union All
Select '12408','Mar 24 2015 12:00AM','USD','MXN','0.06692' Union All
Select '12409','Mar 23 2015 12:00AM','USD','MXN','0.06685' Union All
Select '12410','Mar 20 2015 12:00AM','USD','MXN','0.06643' Union All
Select '12411','Mar 19 2015 12:00AM','USD','MXN','0.06534' Union All
Select '12412','Mar 18 2015 12:00AM','USD','MXN','0.06482' Union All
Select '12413','Mar 17 2015 12:00AM','USD','MXN','0.06483' Union All
Select '12414','Mar 16 2015 12:00AM','USD','MXN','0.06483' Union All
Select '12415','Mar 13 2015 12:00AM','USD','MXN','0.06451' Union All
Select '12416','Mar 12 2015 12:00AM','USD','MXN','0.06507' Union All
Select '12417','Mar 11 2015 12:00AM','USD','MXN','0.06467' Union All
Select '12418','Mar 10 2015 12:00AM','USD','MXN','0.06417' Union All
Select '12419','Mar  9 2015 12:00AM','USD','MXN','0.06467' Union All
Select '12420','Mar  6 2015 12:00AM','USD','MXN','0.06472' Union All
Select '12421','Mar  5 2015 12:00AM','USD','MXN','0.06585' Union All
Select '12422','Mar  4 2015 12:00AM','USD','MXN','0.06629' Union All
Select '12423','Mar  3 2015 12:00AM','USD','MXN','0.06695' Union All
Select '12424','Mar  2 2015 12:00AM','USD','MXN','0.0667' Union All
Select '22513','Mar  1 2015 12:00AM','CAD','MXN','0' Union All
Select '22514','Mar  1 2015 12:00AM','CAD','USD','0' Union All
Select '22515','Mar  1 2015 12:00AM','MXN','CAD','0' Union All
Select '22516','Mar  1 2015 12:00AM','MXN','USD','0' Union All
Select '22517','Mar  1 2015 12:00AM','USD','CAD','0' Union All
Select '22518','Mar  1 2015 12:00AM','USD','MXN','0' Union All
Select '22519','Mar  7 2015 12:00AM','CAD','MXN','0' Union All
Select '22520','Mar  7 2015 12:00AM','CAD','USD','0' Union All
Select '22521','Mar  7 2015 12:00AM','MXN','CAD','0' Union All
Select '22522','Mar  7 2015 12:00AM','MXN','USD','0' Union All
Select '22523','Mar  7 2015 12:00AM','USD','CAD','0' Union All
Select '22524','Mar  7 2015 12:00AM','USD','MXN','0' Union All
Select '22525','Mar  8 2015 12:00AM','CAD','MXN','0' Union All
Select '22526','Mar  8 2015 12:00AM','CAD','USD','0' Union All
Select '22527','Mar  8 2015 12:00AM','MXN','CAD','0' Union All
Select '22528','Mar  8 2015 12:00AM','MXN','USD','0' Union All
Select '22529','Mar  8 2015 12:00AM','USD','CAD','0' Union All
Select '22530','Mar  8 2015 12:00AM','USD','MXN','0' Union All
Select '22531','Mar 14 2015 12:00AM','CAD','MXN','0' Union All
Select '22532','Mar 14 2015 12:00AM','CAD','USD','0' Union All
Select '22533','Mar 14 2015 12:00AM','MXN','CAD','0' Union All
Select '22534','Mar 14 2015 12:00AM','MXN','USD','0' Union All
Select '22535','Mar 14 2015 12:00AM','USD','CAD','0' Union All
Select '22536','Mar 14 2015 12:00AM','USD','MXN','0' Union All
Select '22537','Mar 15 2015 12:00AM','CAD','MXN','0' Union All
Select '22538','Mar 15 2015 12:00AM','CAD','USD','0' Union All
Select '22539','Mar 15 2015 12:00AM','MXN','CAD','0' Union All
Select '22540','Mar 15 2015 12:00AM','MXN','USD','0' Union All
Select '22541','Mar 15 2015 12:00AM','USD','CAD','0' Union All
Select '22542','Mar 15 2015 12:00AM','USD','MXN','0' Union All
Select '22543','Mar 21 2015 12:00AM','CAD','MXN','0' Union All
Select '22544','Mar 21 2015 12:00AM','CAD','USD','0' Union All
Select '22545','Mar 21 2015 12:00AM','MXN','CAD','0' Union All
Select '22546','Mar 21 2015 12:00AM','MXN','USD','0' Union All
Select '22547','Mar 21 2015 12:00AM','USD','CAD','0' Union All
Select '22548','Mar 21 2015 12:00AM','USD','MXN','0' Union All
Select '22549','Mar 22 2015 12:00AM','CAD','MXN','0' Union All
Select '22550','Mar 22 2015 12:00AM','CAD','USD','0' Union All
Select '22551','Mar 22 2015 12:00AM','MXN','CAD','0' Union All
Select '22552','Mar 22 2015 12:00AM','MXN','USD','0' Union All
Select '22553','Mar 22 2015 12:00AM','USD','CAD','0' Union All
Select '22554','Mar 22 2015 12:00AM','USD','MXN','0' Union All
Select '22555','Mar 28 2015 12:00AM','CAD','MXN','0' Union All
Select '22556','Mar 28 2015 12:00AM','CAD','USD','0' Union All
Select '22557','Mar 28 2015 12:00AM','MXN','CAD','0' Union All
Select '22558','Mar 28 2015 12:00AM','MXN','USD','0' Union All
Select '22559','Mar 28 2015 12:00AM','USD','CAD','0' Union All
Select '22560','Mar 28 2015 12:00AM','USD','MXN','0' Union All
Select '22561','Mar 29 2015 12:00AM','CAD','MXN','0' Union All
Select '22562','Mar 29 2015 12:00AM','CAD','USD','0' Union All
Select '22563','Mar 29 2015 12:00AM','MXN','CAD','0' Union All
Select '22564','Mar 29 2015 12:00AM','MXN','USD','0' Union All
Select '22565','Mar 29 2015 12:00AM','USD','CAD','0' Union All
Select '22566','Mar 29 2015 12:00AM','USD','MXN','0' Union All
Select '22567','Apr  4 2015 12:00AM','CAD','MXN','0' Union All
Select '22568','Apr  4 2015 12:00AM','CAD','USD','0' Union All
Select '22569','Apr  4 2015 12:00AM','MXN','CAD','0' Union All
Select '22570','Apr  4 2015 12:00AM','MXN','USD','0' Union All
Select '22571','Apr  4 2015 12:00AM','USD','CAD','0' Union All
Select '22572','Apr  4 2015 12:00AM','USD','MXN','0' Union All
Select '22573','Apr  5 2015 12:00AM','CAD','MXN','0' Union All
Select '22574','Apr  5 2015 12:00AM','CAD','USD','0' Union All
Select '22575','Apr  5 2015 12:00AM','MXN','CAD','0' Union All
Select '22576','Apr  5 2015 12:00AM','MXN','USD','0' Union All
Select '22577','Apr  5 2015 12:00AM','USD','CAD','0' Union All
Select '22578','Apr  5 2015 12:00AM','USD','MXN','0' Union All
Select '22579','Apr 11 2015 12:00AM','CAD','MXN','0' Union All
Select '22580','Apr 11 2015 12:00AM','CAD','USD','0' Union All
Select '22581','Apr 11 2015 12:00AM','MXN','CAD','0' Union All
Select '22582','Apr 11 2015 12:00AM','MXN','USD','0' Union All
Select '22583','Apr 11 2015 12:00AM','USD','CAD','0' Union All
Select '22584','Apr 11 2015 12:00AM','USD','MXN','0' Union All
Select '22585','Apr 12 2015 12:00AM','CAD','MXN','0' Union All
Select '22586','Apr 12 2015 12:00AM','CAD','USD','0' Union All
Select '22587','Apr 12 2015 12:00AM','MXN','CAD','0' Union All
Select '22588','Apr 12 2015 12:00AM','MXN','USD','0' Union All
Select '22589','Apr 12 2015 12:00AM','USD','CAD','0' Union All
Select '22590','Apr 12 2015 12:00AM','USD','MXN','0' Union All
Select '22591','Apr 14 2015 12:00AM','CAD','MXN','0' Union All
Select '22592','Apr 14 2015 12:00AM','CAD','USD','0' Union All
Select '22593','Apr 14 2015 12:00AM','MXN','CAD','0' Union All
Select '22594','Apr 14 2015 12:00AM','MXN','USD','0' Union All
Select '22595','Apr 14 2015 12:00AM','USD','CAD','0' Union All
Select '22596','Apr 14 2015 12:00AM','USD','MXN','0'

select * from #CurrXChange order by CurrXDate

Open in new window

Scarlett72Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Please try query below.  Btw, you should cluster the exchange rate table on ( CurrFrom, CurrTo, CurrXDate ):

SELECT cx.currxdate, cx.[currfrom], cx.[currTO],
    ISNULL(NULLIF(CAST(cx.CurrXRate AS decimal(19, 5)), 0),
          (SELECT TOP (1) cx2.CurrXRate
           FROM #CurrXchange cx2
           WHERE
               cx2.CurrFrom = cx.CurrFrom AND
               cx2.CurrTo = cx2.CurrTo AND
               cx2.CurrXDate < cx.CurrXDate AND
               (cx2.CurrXRate <> '0' AND cx2.CurrXRate IS NOT NULL)
           ORDER BY cx2.CurrXDate DESC
          )) AS CurrXRate          
FROM #CurrXchange cx
0
 
dsackerContract ERP Admin/ConsultantCommented:
Actually, rather than put NULL in your test data, you put '0'. Did you mean for those to be null?
0
 
Scarlett72Author Commented:
yes, I did put 0 more by accident because of the script i used to generate the test data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dsackerContract ERP Admin/ConsultantCommented:
Your query is on the right track. I cleaned it up just a little, added some extra test data for 02/27/2015 (the Friday before 03/01/2015), and ran it starting with 03/01/2015. I added the two Rates (RateA and RateB), as well as their respective days (DayA and DayB), so you could verify whether you liked what it was ISNULL'ing to. :)
DECLARE @CurrXChange TABLE (
    CurrencyID  smallint        NOT NULL,
    CurrXDate   datetime        NOT NULL,
    CurrFrom    varchar(3)      NOT NULL,
    CurrTo      varchar(3)      NOT NULL,
    CurrXRate   decimal(12, 7) )

INSERT INTO @CurrXChange
Select '100',  'Feb 27 2015 12:00AM','CAD','USD','1.2600' Union All
Select '101',  'Feb 27 2015 12:00AM','USD','CAD','0.7932' Union All
Select '102',  'Feb 27 2015 12:00AM','MXN','CAD','12.1490' Union All
Select '103',  'Feb 27 2015 12:00AM','CAD','MXN','0.08230' Union All
Select '104',  'Feb 27 2015 12:00AM','MXN','USD','15.3100' Union All
Select '105',  'Feb 27 2015 12:00AM','USD','MXN','0.06530' Union All
Select '16525','Apr 13 2015 12:00AM','CAD','USD','1.2602' Union All
Select '16526','Apr 10 2015 12:00AM','CAD','USD','1.2595' Union All
Select '16527','Apr  9 2015 12:00AM','CAD','USD','1.258' Union All
Select '16528','Apr  8 2015 12:00AM','CAD','USD','1.2508' Union All
Select '16529','Apr  7 2015 12:00AM','CAD','USD','1.2488' Union All
Select '16530','Apr  6 2015 12:00AM','CAD','USD','1.2452' Union All
Select '16531','Apr  3 2015 12:00AM','CAD','USD',NULL Union All
Select '16532','Apr  2 2015 12:00AM','CAD','USD','1.2585' Union All
Select '16533','Apr  1 2015 12:00AM','CAD','USD','1.2612' Union All
Select '16534','Mar 31 2015 12:00AM','CAD','USD','1.2683' Union All
Select '16535','Mar 30 2015 12:00AM','CAD','USD','1.2689' Union All
Select '16536','Mar 27 2015 12:00AM','CAD','USD','1.258' Union All
Select '16537','Mar 26 2015 12:00AM','CAD','USD','1.2471' Union All
Select '16538','Mar 25 2015 12:00AM','CAD','USD','1.2513' Union All
Select '16539','Mar 24 2015 12:00AM','CAD','USD','1.2511' Union All
Select '16540','Mar 23 2015 12:00AM','CAD','USD','1.2516' Union All
Select '16541','Mar 20 2015 12:00AM','CAD','USD','1.2595' Union All
Select '16542','Mar 19 2015 12:00AM','CAD','USD','1.2744' Union All
Select '16543','Mar 18 2015 12:00AM','CAD','USD','1.2771' Union All
Select '16544','Mar 17 2015 12:00AM','CAD','USD','1.2769' Union All
Select '16545','Mar 16 2015 12:00AM','CAD','USD','1.2765' Union All
Select '16546','Mar 13 2015 12:00AM','CAD','USD','1.2803' Union All
Select '16547','Mar 12 2015 12:00AM','CAD','USD','1.2691' Union All
Select '16548','Mar 11 2015 12:00AM','CAD','USD','1.2764' Union All
Select '16549','Mar 10 2015 12:00AM','CAD','USD','1.2633' Union All
Select '16550','Mar  9 2015 12:00AM','CAD','USD','1.2598' Union All
Select '16551','Mar  6 2015 12:00AM','CAD','USD','1.2616' Union All
Select '16552','Mar  5 2015 12:00AM','CAD','USD','1.2482' Union All
Select '16553','Mar  4 2015 12:00AM','CAD','USD','1.244' Union All
Select '16554','Mar  3 2015 12:00AM','CAD','USD','1.2452' Union All
Select '16555','Mar  2 2015 12:00AM','CAD','USD','1.2535' Union All
Select '17902','Apr 13 2015 12:00AM','USD','CAD','0.7935' Union All
Select '17903','Apr 10 2015 12:00AM','USD','CAD','0.794' Union All
Select '17904','Apr  9 2015 12:00AM','USD','CAD','0.7949' Union All
Select '17905','Apr  8 2015 12:00AM','USD','CAD','0.7995' Union All
Select '17906','Apr  7 2015 12:00AM','USD','CAD','0.8008' Union All
Select '17907','Apr  6 2015 12:00AM','USD','CAD','0.8031' Union All
Select '17908','Apr  3 2015 12:00AM','USD','CAD',NULL Union All
Select '17909','Apr  2 2015 12:00AM','USD','CAD','0.7946' Union All
Select '17910','Apr  1 2015 12:00AM','USD','CAD','0.7929' Union All
Select '17911','Mar 31 2015 12:00AM','USD','CAD','0.7885' Union All
Select '17912','Mar 30 2015 12:00AM','USD','CAD','0.7881' Union All
Select '17913','Mar 27 2015 12:00AM','USD','CAD','0.7949' Union All
Select '17914','Mar 26 2015 12:00AM','USD','CAD','0.8019' Union All
Select '17915','Mar 25 2015 12:00AM','USD','CAD','0.7992' Union All
Select '17916','Mar 24 2015 12:00AM','USD','CAD','0.7993' Union All
Select '17917','Mar 23 2015 12:00AM','USD','CAD','0.799' Union All
Select '17918','Mar 20 2015 12:00AM','USD','CAD','0.794' Union All
Select '17919','Mar 19 2015 12:00AM','USD','CAD','0.7847' Union All
Select '17920','Mar 18 2015 12:00AM','USD','CAD','0.783' Union All
Select '17921','Mar 17 2015 12:00AM','USD','CAD','0.7831' Union All
Select '17922','Mar 16 2015 12:00AM','USD','CAD','0.7834' Union All
Select '17923','Mar 13 2015 12:00AM','USD','CAD','0.7811' Union All
Select '17924','Mar 12 2015 12:00AM','USD','CAD','0.788' Union All
Select '17925','Mar 11 2015 12:00AM','USD','CAD','0.7835' Union All
Select '17926','Mar 10 2015 12:00AM','USD','CAD','0.7916' Union All
Select '17927','Mar  9 2015 12:00AM','USD','CAD','0.7938' Union All
Select '17928','Mar  6 2015 12:00AM','USD','CAD','0.7926' Union All
Select '17929','Mar  5 2015 12:00AM','USD','CAD','0.8012' Union All
Select '17930','Mar  4 2015 12:00AM','USD','CAD','0.8039' Union All
Select '17931','Mar  3 2015 12:00AM','USD','CAD','0.8031' Union All
Select '17932','Mar  2 2015 12:00AM','USD','CAD','0.7978' Union All
Select '9640','Apr 13 2015 12:00AM','MXN','CAD','12.1492' Union All
Select '9641','Apr 10 2015 12:00AM','MXN','CAD','12.0424' Union All
Select '9642','Apr  9 2015 12:00AM','MXN','CAD','11.9703' Union All
Select '9643','Apr  8 2015 12:00AM','MXN','CAD','11.919' Union All
Select '9644','Apr  7 2015 12:00AM','MXN','CAD','11.9489' Union All
Select '9645','Apr  6 2015 12:00AM','MXN','CAD','11.9062' Union All
Select '9646','Apr  3 2015 12:00AM','MXN','CAD',NULL Union All
Select '9647','Apr  2 2015 12:00AM','MXN','CAD','11.9732' Union All
Select '9648','Apr  1 2015 12:00AM','MXN','CAD','11.9847' Union All
Select '9649','Mar 31 2015 12:00AM','MXN','CAD','12.0207' Union All
Select '9650','Mar 30 2015 12:00AM','MXN','CAD','12.0192' Union All
Select '9651','Mar 27 2015 12:00AM','MXN','CAD','12.0642' Union All
Select '9652','Mar 26 2015 12:00AM','MXN','CAD','12.0963' Union All
Select '9653','Mar 25 2015 12:00AM','MXN','CAD','11.936' Union All
Select '9654','Mar 24 2015 12:00AM','MXN','CAD','11.9446' Union All
Select '9655','Mar 23 2015 12:00AM','MXN','CAD','11.9517' Union All
Select '9656','Mar 20 2015 12:00AM','MXN','CAD','11.9517' Union All
Select '9657','Mar 19 2015 12:00AM','MXN','CAD','12.0091' Union All
Select '9658','Mar 18 2015 12:00AM','MXN','CAD','12.0802' Union All
Select '9659','Mar 17 2015 12:00AM','MXN','CAD','12.0802' Union All
Select '9660','Mar 16 2015 12:00AM','MXN','CAD','12.0831' Union All
Select '9661','Mar 13 2015 12:00AM','MXN','CAD','12.108' Union All
Select '9662','Mar 12 2015 12:00AM','MXN','CAD','12.1095' Union All
Select '9663','Mar 11 2015 12:00AM','MXN','CAD','12.1139' Union All
Select '9664','Mar 10 2015 12:00AM','MXN','CAD','12.335' Union All
Select '9665','Mar  9 2015 12:00AM','MXN','CAD','12.2745' Union All
Select '9666','Mar  6 2015 12:00AM','MXN','CAD','12.2474' Union All
Select '9667','Mar  5 2015 12:00AM','MXN','CAD','12.1655' Union All
Select '9668','Mar  4 2015 12:00AM','MXN','CAD','12.1256' Union All
Select '9669','Mar  3 2015 12:00AM','MXN','CAD','11.9962' Union All
Select '9670','Mar  2 2015 12:00AM','MXN','CAD','11.9603' Union All
Select '8263','Apr 13 2015 12:00AM','CAD','MXN','0.08231' Union All
Select '8264','Apr 10 2015 12:00AM','CAD','MXN','0.08304' Union All
Select '8265','Apr  9 2015 12:00AM','CAD','MXN','0.08354' Union All
Select '8266','Apr  8 2015 12:00AM','CAD','MXN','0.0839' Union All
Select '8267','Apr  7 2015 12:00AM','CAD','MXN','0.08369' Union All
Select '8268','Apr  6 2015 12:00AM','CAD','MXN','0.08399' Union All
Select '8269','Apr  3 2015 12:00AM','CAD','MXN',NULL Union All
Select '8270','Apr  2 2015 12:00AM','CAD','MXN','0.08352' Union All
Select '8271','Apr  1 2015 12:00AM','CAD','MXN','0.08344' Union All
Select '8272','Mar 31 2015 12:00AM','CAD','MXN','0.08319' Union All
Select '8273','Mar 30 2015 12:00AM','CAD','MXN','0.0832' Union All
Select '8274','Mar 27 2015 12:00AM','CAD','MXN','0.08289' Union All
Select '8275','Mar 26 2015 12:00AM','CAD','MXN','0.08267' Union All
Select '8276','Mar 25 2015 12:00AM','CAD','MXN','0.08378' Union All
Select '8277','Mar 24 2015 12:00AM','CAD','MXN','0.08372' Union All
Select '8278','Mar 23 2015 12:00AM','CAD','MXN','0.08367' Union All
Select '8279','Mar 20 2015 12:00AM','CAD','MXN','0.08367' Union All
Select '8280','Mar 19 2015 12:00AM','CAD','MXN','0.08327' Union All
Select '8281','Mar 18 2015 12:00AM','CAD','MXN','0.08278' Union All
Select '8282','Mar 17 2015 12:00AM','CAD','MXN','0.08278' Union All
Select '8283','Mar 16 2015 12:00AM','CAD','MXN','0.08276' Union All
Select '8284','Mar 13 2015 12:00AM','CAD','MXN','0.08259' Union All
Select '8285','Mar 12 2015 12:00AM','CAD','MXN','0.08258' Union All
Select '8286','Mar 11 2015 12:00AM','CAD','MXN','0.08255' Union All
Select '8287','Mar 10 2015 12:00AM','CAD','MXN','0.08107' Union All
Select '8288','Mar  9 2015 12:00AM','CAD','MXN','0.08147' Union All
Select '8289','Mar  6 2015 12:00AM','CAD','MXN','0.08165' Union All
Select '8290','Mar  5 2015 12:00AM','CAD','MXN','0.0822' Union All
Select '8291','Mar  4 2015 12:00AM','CAD','MXN','0.08247' Union All
Select '8292','Mar  3 2015 12:00AM','CAD','MXN','0.08336' Union All
Select '8293','Mar  2 2015 12:00AM','CAD','MXN','0.08361' Union All
Select '11017','Apr 13 2015 12:00AM','MXN','USD','15.3104' Union All
Select '11018','Apr 10 2015 12:00AM','MXN','USD','15.1674' Union All
Select '11019','Apr  9 2015 12:00AM','MXN','USD','15.0587' Union All
Select '11020','Apr  8 2015 12:00AM','MXN','USD','14.9082' Union All
Select '11021','Apr  7 2015 12:00AM','MXN','USD','14.9217' Union All
Select '11022','Apr  6 2015 12:00AM','MXN','USD','14.8256' Union All
Select '11023','Apr  3 2015 12:00AM','MXN','USD',NULL Union All
Select '11024','Apr  2 2015 12:00AM','MXN','USD','15.0682' Union All
Select '11025','Apr  1 2015 12:00AM','MXN','USD','15.1151' Union All
Select '11026','Mar 31 2015 12:00AM','MXN','USD','15.2458' Union All
Select '11027','Mar 30 2015 12:00AM','MXN','USD','15.2512' Union All
Select '11028','Mar 27 2015 12:00AM','MXN','USD','15.1767' Union All
Select '11029','Mar 26 2015 12:00AM','MXN','USD','15.0853' Union All
Select '11030','Mar 25 2015 12:00AM','MXN','USD','14.9355' Union All
Select '11031','Mar 24 2015 12:00AM','MXN','USD','14.9439' Union All
Select '11032','Mar 23 2015 12:00AM','MXN','USD','14.9588' Union All
Select '11033','Mar 20 2015 12:00AM','MXN','USD','15.0532' Union All
Select '11034','Mar 19 2015 12:00AM','MXN','USD','15.3044' Union All
Select '11035','Mar 18 2015 12:00AM','MXN','USD','15.4276' Union All
Select '11036','Mar 17 2015 12:00AM','MXN','USD','15.4252' Union All
Select '11037','Mar 16 2015 12:00AM','MXN','USD','15.4241' Union All
Select '11038','Mar 13 2015 12:00AM','MXN','USD','15.5019' Union All
Select '11039','Mar 12 2015 12:00AM','MXN','USD','15.3681' Union All
Select '11040','Mar 11 2015 12:00AM','MXN','USD','15.4621' Union All
Select '11041','Mar 10 2015 12:00AM','MXN','USD','15.5828' Union All
Select '11042','Mar  9 2015 12:00AM','MXN','USD','15.4634' Union All
Select '11043','Mar  6 2015 12:00AM','MXN','USD','15.4513' Union All
Select '11044','Mar  5 2015 12:00AM','MXN','USD','15.1849' Union All
Select '11045','Mar  4 2015 12:00AM','MXN','USD','15.0843' Union All
Select '11046','Mar  3 2015 12:00AM','MXN','USD','14.9376' Union All
Select '11047','Mar  2 2015 12:00AM','MXN','USD','14.9922' Union All
Select '12394','Apr 13 2015 12:00AM','USD','MXN','0.06532' Union All
Select '12395','Apr 10 2015 12:00AM','USD','MXN','0.06593' Union All
Select '12396','Apr  9 2015 12:00AM','USD','MXN','0.06641' Union All
Select '12397','Apr  8 2015 12:00AM','USD','MXN','0.06708' Union All
Select '12398','Apr  7 2015 12:00AM','USD','MXN','0.06702' Union All
Select '12399','Apr  6 2015 12:00AM','USD','MXN','0.06745' Union All
Select '12400','Apr  3 2015 12:00AM','USD','MXN',NULL Union All
Select '12401','Apr  2 2015 12:00AM','USD','MXN','0.06636' Union All
Select '12402','Apr  1 2015 12:00AM','USD','MXN','0.06616' Union All
Select '12403','Mar 31 2015 12:00AM','USD','MXN','0.06559' Union All
Select '12404','Mar 30 2015 12:00AM','USD','MXN','0.06557' Union All
Select '12405','Mar 27 2015 12:00AM','USD','MXN','0.06589' Union All
Select '12406','Mar 26 2015 12:00AM','USD','MXN','0.06629' Union All
Select '12407','Mar 25 2015 12:00AM','USD','MXN','0.06695' Union All
Select '12408','Mar 24 2015 12:00AM','USD','MXN','0.06692' Union All
Select '12409','Mar 23 2015 12:00AM','USD','MXN','0.06685' Union All
Select '12410','Mar 20 2015 12:00AM','USD','MXN','0.06643' Union All
Select '12411','Mar 19 2015 12:00AM','USD','MXN','0.06534' Union All
Select '12412','Mar 18 2015 12:00AM','USD','MXN','0.06482' Union All
Select '12413','Mar 17 2015 12:00AM','USD','MXN','0.06483' Union All
Select '12414','Mar 16 2015 12:00AM','USD','MXN','0.06483' Union All
Select '12415','Mar 13 2015 12:00AM','USD','MXN','0.06451' Union All
Select '12416','Mar 12 2015 12:00AM','USD','MXN','0.06507' Union All
Select '12417','Mar 11 2015 12:00AM','USD','MXN','0.06467' Union All
Select '12418','Mar 10 2015 12:00AM','USD','MXN','0.06417' Union All
Select '12419','Mar  9 2015 12:00AM','USD','MXN','0.06467' Union All
Select '12420','Mar  6 2015 12:00AM','USD','MXN','0.06472' Union All
Select '12421','Mar  5 2015 12:00AM','USD','MXN','0.06585' Union All
Select '12422','Mar  4 2015 12:00AM','USD','MXN','0.06629' Union All
Select '12423','Mar  3 2015 12:00AM','USD','MXN','0.06695' Union All
Select '12424','Mar  2 2015 12:00AM','USD','MXN','0.0667' Union All
Select '22513','Mar  1 2015 12:00AM','CAD','MXN',NULL Union All
Select '22514','Mar  1 2015 12:00AM','CAD','USD',NULL Union All
Select '22515','Mar  1 2015 12:00AM','MXN','CAD',NULL Union All
Select '22516','Mar  1 2015 12:00AM','MXN','USD',NULL Union All
Select '22517','Mar  1 2015 12:00AM','USD','CAD',NULL Union All
Select '22518','Mar  1 2015 12:00AM','USD','MXN',NULL Union All
Select '22519','Mar  7 2015 12:00AM','CAD','MXN',NULL Union All
Select '22520','Mar  7 2015 12:00AM','CAD','USD',NULL Union All
Select '22521','Mar  7 2015 12:00AM','MXN','CAD',NULL Union All
Select '22522','Mar  7 2015 12:00AM','MXN','USD',NULL Union All
Select '22523','Mar  7 2015 12:00AM','USD','CAD',NULL Union All
Select '22524','Mar  7 2015 12:00AM','USD','MXN',NULL Union All
Select '22525','Mar  8 2015 12:00AM','CAD','MXN',NULL Union All
Select '22526','Mar  8 2015 12:00AM','CAD','USD',NULL Union All
Select '22527','Mar  8 2015 12:00AM','MXN','CAD',NULL Union All
Select '22528','Mar  8 2015 12:00AM','MXN','USD',NULL Union All
Select '22529','Mar  8 2015 12:00AM','USD','CAD',NULL Union All
Select '22530','Mar  8 2015 12:00AM','USD','MXN',NULL Union All
Select '22531','Mar 14 2015 12:00AM','CAD','MXN',NULL Union All
Select '22532','Mar 14 2015 12:00AM','CAD','USD',NULL Union All
Select '22533','Mar 14 2015 12:00AM','MXN','CAD',NULL Union All
Select '22534','Mar 14 2015 12:00AM','MXN','USD',NULL Union All
Select '22535','Mar 14 2015 12:00AM','USD','CAD',NULL Union All
Select '22536','Mar 14 2015 12:00AM','USD','MXN',NULL Union All
Select '22537','Mar 15 2015 12:00AM','CAD','MXN',NULL Union All
Select '22538','Mar 15 2015 12:00AM','CAD','USD',NULL Union All
Select '22539','Mar 15 2015 12:00AM','MXN','CAD',NULL Union All
Select '22540','Mar 15 2015 12:00AM','MXN','USD',NULL Union All
Select '22541','Mar 15 2015 12:00AM','USD','CAD',NULL Union All
Select '22542','Mar 15 2015 12:00AM','USD','MXN',NULL Union All
Select '22543','Mar 21 2015 12:00AM','CAD','MXN',NULL Union All
Select '22544','Mar 21 2015 12:00AM','CAD','USD',NULL Union All
Select '22545','Mar 21 2015 12:00AM','MXN','CAD',NULL Union All
Select '22546','Mar 21 2015 12:00AM','MXN','USD',NULL Union All
Select '22547','Mar 21 2015 12:00AM','USD','CAD',NULL Union All
Select '22548','Mar 21 2015 12:00AM','USD','MXN',NULL Union All
Select '22549','Mar 22 2015 12:00AM','CAD','MXN',NULL Union All
Select '22550','Mar 22 2015 12:00AM','CAD','USD',NULL Union All
Select '22551','Mar 22 2015 12:00AM','MXN','CAD',NULL Union All
Select '22552','Mar 22 2015 12:00AM','MXN','USD',NULL Union All
Select '22553','Mar 22 2015 12:00AM','USD','CAD',NULL Union All
Select '22554','Mar 22 2015 12:00AM','USD','MXN',NULL Union All
Select '22555','Mar 28 2015 12:00AM','CAD','MXN',NULL Union All
Select '22556','Mar 28 2015 12:00AM','CAD','USD',NULL Union All
Select '22557','Mar 28 2015 12:00AM','MXN','CAD',NULL Union All
Select '22558','Mar 28 2015 12:00AM','MXN','USD',NULL Union All
Select '22559','Mar 28 2015 12:00AM','USD','CAD',NULL Union All
Select '22560','Mar 28 2015 12:00AM','USD','MXN',NULL Union All
Select '22561','Mar 29 2015 12:00AM','CAD','MXN',NULL Union All
Select '22562','Mar 29 2015 12:00AM','CAD','USD',NULL Union All
Select '22563','Mar 29 2015 12:00AM','MXN','CAD',NULL Union All
Select '22564','Mar 29 2015 12:00AM','MXN','USD',NULL Union All
Select '22565','Mar 29 2015 12:00AM','USD','CAD',NULL Union All
Select '22566','Mar 29 2015 12:00AM','USD','MXN',NULL Union All
Select '22567','Apr  4 2015 12:00AM','CAD','MXN',NULL Union All
Select '22568','Apr  4 2015 12:00AM','CAD','USD',NULL Union All
Select '22569','Apr  4 2015 12:00AM','MXN','CAD',NULL Union All
Select '22570','Apr  4 2015 12:00AM','MXN','USD',NULL Union All
Select '22571','Apr  4 2015 12:00AM','USD','CAD',NULL Union All
Select '22572','Apr  4 2015 12:00AM','USD','MXN',NULL Union All
Select '22573','Apr  5 2015 12:00AM','CAD','MXN',NULL Union All
Select '22574','Apr  5 2015 12:00AM','CAD','USD',NULL Union All
Select '22575','Apr  5 2015 12:00AM','MXN','CAD',NULL Union All
Select '22576','Apr  5 2015 12:00AM','MXN','USD',NULL Union All
Select '22577','Apr  5 2015 12:00AM','USD','CAD',NULL Union All
Select '22578','Apr  5 2015 12:00AM','USD','MXN',NULL Union All
Select '22579','Apr 11 2015 12:00AM','CAD','MXN',NULL Union All
Select '22580','Apr 11 2015 12:00AM','CAD','USD',NULL Union All
Select '22581','Apr 11 2015 12:00AM','MXN','CAD',NULL Union All
Select '22582','Apr 11 2015 12:00AM','MXN','USD',NULL Union All
Select '22583','Apr 11 2015 12:00AM','USD','CAD',NULL Union All
Select '22584','Apr 11 2015 12:00AM','USD','MXN',NULL Union All
Select '22585','Apr 12 2015 12:00AM','CAD','MXN',NULL Union All
Select '22586','Apr 12 2015 12:00AM','CAD','USD',NULL Union All
Select '22587','Apr 12 2015 12:00AM','MXN','CAD',NULL Union All
Select '22588','Apr 12 2015 12:00AM','MXN','USD',NULL Union All
Select '22589','Apr 12 2015 12:00AM','USD','CAD',NULL Union All
Select '22590','Apr 12 2015 12:00AM','USD','MXN',NULL Union All
Select '22591','Apr 14 2015 12:00AM','CAD','MXN',NULL Union All
Select '22592','Apr 14 2015 12:00AM','CAD','USD',NULL Union All
Select '22593','Apr 14 2015 12:00AM','MXN','CAD',NULL Union All
Select '22594','Apr 14 2015 12:00AM','MXN','USD',NULL Union All
Select '22595','Apr 14 2015 12:00AM','USD','CAD',NULL Union All
Select '22596','Apr 14 2015 12:00AM','USD','MXN',NULL

;WITH CTE AS
(
SELECT * FROM @CurrXChange
)
SELECT a.CurrXDate,
       a.CurrFrom,
       a.CurrTo,
       ISNULL(a.CurrXRate, b.CurrXRate) AS CurrXRate,
       a.CurrXRate AS RateA,
       b.CurrXRate AS RateB,
       DATENAME(dw, a.CurrXDate) AS DayA,
       DATENAME(dw, b.CurrXDate) AS DayB
FROM   CTE a
OUTER APPLY (SELECT TOP 1 *
             FROM   CTE b
             WHERE  CurrFrom  = a.CurrFrom
             AND    CurrTo    = a.CurrTo
             AND    CurrXDate < a.CurrXDate
             AND    CurrXRate IS NOT NULL
             ORDER BY b.CurrXDate DESC) b
WHERE  a.CurrXDate >= '2015-03-01'
ORDER BY CurrXDate, CurrFrom, CurrTo

Open in new window

0
 
dsackerContract ERP Admin/ConsultantCommented:
I fixed one bug above (changed ORDER BY a.CurrXDate DESC to ORDER BY b.CurrXDate DESC).

Also, if you just happen to have a rate for a prior Saturday, and a.CurrXDate is on a Sunday, you'll pick up that Saturday's rate. Is that okay?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Scarlett72, do you still need help with this question?
0
 
Scarlett72Author Commented:
Hi Scott and dsacker, thank you both for replying, Scott's solution worked best for me.  
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.