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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.