TSQL example of lag window function and how to synthesize a lag window function

example.sql

-- lag (SQL Server 2012+)
select
   PricingDate
  ,Cusip
  ,MarketValue
  ,lag(MarketValue, 1) over (partition by Portfolio, Cusip order by PricingDate) as MarketValue_1DayAgo
  ,lag(MarketValue, 2) over (partition by Portfolio, Cusip order by PricingDate) as MarketValue_2DayAgo
  from Holdings
;

-- synthesize lag (SQL Server 2008-)
;with lags as (
  select
     rank() over (partition by Portfolio, Cusip order by PricingDate) as rnk
    ,Cusip
    ,Portfolio
    ,PricingDate
    ,MarketValue
    from Holdings
)
select
   h.PricingDate
  ,h.Cusip
  ,h.MarketValue
  ,d1.MarketValue as MarketValue_1DayAgo
  ,d2.MarketValue as MarketValue_2DayAgo
  from Holdings as h
  inner join lags as d0
    on h.Cusip = d0.Cusip
    and h.Portfolio = d0.Portfolio
    and h.PricingDate = d0.PricingDate
  left outer join lags as d1
    on d0.Cusip = d1.Cusip
    and d0.Portfolio = d1.Portfolio
    and d0.rnk = d1.rnk + 1
  left outer join lags as d2
    on d0.Cusip = d2.Cusip
    and d0.Portfolio = d2.Portfolio
    and d0.rnk = d2.rnk + 2
;