Replace null value with previous value - SQL Server 2008 R2

Question

Will post this question again with full code. Last try I didn't write it all which resulted in answers that I could not use.

I have below query and want to replace the latest NULL-value with previous value for that currency. Sometimes there are many null-values on the same date and sometimes there is only one.

I guess I have to do something with the left join on cteB? Any ideas? See result and desired result below query

    With cte as (
    SELECT
        PositionDate, 
        c.Currency, 
        DepositLclCcy
    FROM 
        [Static].[tbl_DateTable] dt

    CROSS JOIN (Values ('DKK'), ('EUR'), ('SEK')) as c (Currency)

    Left join
    (
    SELECT
        BalanceDate,
        Currency,
       'DepositLclCcy' = Sum(Case when Activity = 'Deposit' then BalanceCcy else 0 END)
    FROM 
        [Position].[vw_InternalBank]
    Group By
        BalanceDate,
        Currency
    ) ib
    on dt.PositionDate = ib.BalanceDate
        and c.Currency = ib.Currency
    Where
        WeekDate = 'Yes') 

    Select 
        *
    From cte cteA

    Left join
    ( Select ... from Cte ) as cteB
   on .....     

   Order by
        cteA.PositionDate desc,
        cteA.Currency

Current Result

PositionDate    Currency        DepositLclCcy
2017-04-11      SEK               1
2017-04-11      DKK               3
2017-04-11      EUR               7
2017-04-10      SEK               NULL 
2017-04-10      DKK               3
2017-04-10      EUR               5
2017-04-07      SEK               5 
2017-04-07      DKK               3
2017-04-07      EUR               5

Desired Result

PositionDate    Currency        DepositLclCcy
2017-04-11      SEK               1
2017-04-11      DKK               3
2017-04-11      EUR               7
2017-04-10      SEK               5 
2017-04-10      DKK               3
2017-04-10      EUR               5
2017-04-07      SEK               5 
2017-04-07      DKK               3
2017-04-07      EUR               5

SqlZim · Accepted Answer · 2017-04-17 15:00:46Z

1

using outer apply() to get the previous value for DepositLclCcy, and replacing null values using coalesce().

with cte as (
  select 
      PositionDate
    , c.Currency
    , DepositLclCcy
  from [Static].[tbl_DateTable] dt
    cross join (values ('DKK') , ('EUR') , ('SEK')) as c(Currency)
    left join (
      select 
          BalanceDate
        , Currency
        , DepositLclCcy = Sum(case when Activity = 'Deposit' then BalanceCcy else 0 end)
      from [Position].[vw_InternalBank]
      group by BalanceDate, Currency
      ) ib
        on dt.PositionDate = ib.BalanceDate
        and c.Currency = ib.Currency
  where WeekDate = 'Yes'
)
select 
    cte.PositionDate
  , cte.Currency 
  , DepositLclCcy = coalesce(cte.DepositLclCcy,x.DepositLclCcy)
from cte 
  outer apply (
    select top 1 i.DepositLclCcy
    from cte as i
    where i.PositionDate < cte.PositionDate
      and i.Currency = cte.Currency
    order by i.PositionDate desc
   ) as x

Skipping the initial left join and using outer apply() there instead:

with cte as (
  select 
      dt.PositionDate
    , c.Currency
    , ib.DepositLclCcy
  from [Static].[tbl_DateTable] dt
    cross join (values ('DKK'), ('EUR'), ('SEK')) as c(Currency)
    outer apply (
      select top 1 
          DepositLclCcy = sum(BalanceCcy)
      from [Position].[vw_InternalBank] as i
      where i.Activity = 'Deposit'
        and i.Currency = c.Currency
        and i.BalanceDate <= dt.PositionDate
      group by i.BalanceDate, i.Currency
      order by i.BalanceDate desc
      ) as ib
  where dt.WeekDate = 'Yes'
)
select * 
from cte

edited Apr 17, 2017 at 15:00

answered Apr 17, 2017 at 12:14

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Haggan Over a year ago

Thanks! It work perfect when I send the "CTE"-data into a temp-table and then use your outer apply query. However, when pointing at CTE, the query is very slow and seems to just be looping around. Query-time never ends. Is there anything I can modify / check / do in the query/views/tables to make it work better?

SqlZim Over a year ago

@Haggan Updated with a different version that might perform better. If you still have performance issues with it, share your execution plans using Paste The Plan @ brentozar.com here are the instructions: How to Use Paste the Plan.

Collectives™ on Stack Overflow

Replace null value with previous value - SQL Server 2008 R2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related