Get last non null value from column

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  April 14, 2015 at 1:51 pm

  #316643

  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

  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

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  April 14, 2015 at 2:42 pm

  #1790959

  on side note, in the test data the nulls are actually zeros ... sorry about any confusion.

• cphite

  SSCarpal Tunnel

  Points: 4278

  More actions

  April 14, 2015 at 3:20 pm

  #1790965

  Marcus Farrugia (4/14/2015)

  ---

  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

  Your logic was not wrong... you just have to account for the rates being zero and not NULL:

  select

  a.CurrencyID,

  a.CurrXDate,

  datename(dw, a.CurrXDate) as DOW,

  a.CurrFrom,

  a.CurrTo,

  nullif(a.CurrXRate, 0) as OrigCurrXRate,

  coalesce(b.CurrXRate, a.CurrXRate, 0) as CurrXRate

  from

  #CurrXChange a

  outer apply

  (

  select top 1

  *

  from

  #CurrXChange

  where

  CurrFrom = a.CurrFrom

  and

  CurrTo = a.CurrTo

  and

  CurrXDate < a.CurrXDate

  and

  nullif(a.CurrXRate, 0) is null

  ) b

  order by

  a.CurrFrom,

  a.CurrTo,

  a.CurrXDate

  Note that as written, this isn't going to be limited to Saturday and Sunday - for each CurrFrom and CurrTo, wherever it finds a zero (or NULL) it's going to pull the prior non-zero (non-NULL) value. So for example if there is no data on a Wednesday, it's going to find Tuesday's value.

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  April 17, 2015 at 9:25 am

  #1791725

  Thank you cphite, worked like a charm.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 17, 2015 at 9:29 am

  #1791728

  Please note that the query in the apply from cphites excellent solution is using TOP 1 but it also needs to have an order by so you know which row is going to be returned.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• cphite

  SSCarpal Tunnel

  Points: 4278

  More actions

  April 17, 2015 at 1:35 pm

  #1791776

  Sean Lange (4/17/2015)

  ---

  Please note that the query in the apply from cphites excellent solution is using TOP 1 but it also needs to have an order by so you know which row is going to be returned.

  DOH!

  Yeah, I forgot to... er, I mean, I was um... just testing the OP to see if he'd catch that... Yeah, that's it!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 17, 2015 at 2:12 pm

  #1791781

  cphite (4/17/2015)

  ---

  Sean Lange (4/17/2015)

  ---

  Please note that the query in the apply from cphites excellent solution is using TOP 1 but it also needs to have an order by so you know which row is going to be returned.

  DOH!

  Yeah, I forgot to... er, I mean, I was um... just testing the OP to see if he'd catch that... Yeah, that's it!

  It is an easy thing to miss. No biggie and an easy fix too. 😀

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Dwain Camps

  SSC Guru

  Points: 86948

  More actions

  April 20, 2015 at 6:56 pm

  #1792268

  Too bad you're not in SQL 2012. It is not often that Itzik Ben-Gan writes an article with a name so close to your thread's title.

  The Last non NULL Puzzle

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  April 21, 2015 at 5:09 am

  #1792342

  Shouldn't it be

  and

  --nullif(a.CurrXRate, 0) is null

  nullif(CurrXRate, 0) is not null

  order by CurrXDate desc

  ?

  And when selecting only one value from APPLY you may rewrite APPLY as subquery as well.

  select

  a.CurrencyID,

  a.CurrXDate,

  datename(dw, a.CurrXDate) as DOW,

  a.CurrFrom,

  a.CurrTo,

  nullif(a.CurrXRate, 0) as OrigCurrXRate,

  coalesce(

  nullif(a.CurrXRate, 0),

  (select top 1 CurrXRate

  from

  #CurrXChange

  where

  CurrFrom = a.CurrFrom

  and

  CurrTo = a.CurrTo

  and

  CurrXDate < a.CurrXDate

  and

  nullif(CurrXRate, 0) is not null

  order by CurrXDate desc

  ),

  0

  ) as CurrXRate

  from

  #CurrXChange a

  order by

  a.CurrFrom,

  a.CurrTo,

  a.CurrXDate