用以前的值-SQL Server 2008 R2替换为空值

Question

将使用完整的代码再次发布此问题。上次尝试，我没有写所有这些结果，这是我无法使用的答案。

我的查询低于查询，并希望用该货币的先前值替换最新的空价值。有时在同一日期有许多无效价值，有时只有一个。

我想我必须对CTEB上的左键加入？有任何想法吗？请参阅下方的结果和所需结果

    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

当前结果

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

所需结果

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

Answer 1

使用outer apply()获取DepositLclCcy的先前值，并使用coalesce()替换null值。

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

---

跳过初始左联接并使用outer apply()而改为使用：

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