Scarlett72
asked on
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:
Test data
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.CurrXDateTest 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
dsacker
Actually, rather than put NULL in your test data, you put '0'. Did you mean for those to be null?
ASKER
yes, I did put 0 more by accident because of the script i used to generate the test data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scarlett72, do you still need help with this question?
ASKER
Hi Scott and dsacker, thank you both for replying, Scott's solution worked best for me.
Thank you!
Thank you!