ELSE LAG logic help!

Transact-SQL
1

Hey All,

I am hoping someone will be able to help I have the below query (Query1) that works fine. but I want to change the line

CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE 'TEST' end AS Assembly_WO

So that rather than returning 'TEST' if pegging_level_no = 0 it returns the Value from the row above in this new column, I am not sure this is possible? (I am new to SQL, and learning as I go)

Basically, I am trying to recreate the formula if it was in excel similar to =IF(D2=0,O2,AF1) this would be in cell AF2.

I have tried using the LAG Function but can not get it to work below is what I have been trying to use but does not work, tried adding a group by but struggling!

CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE LAG(Assembly_WO) OVER(PARTITION BY pegging_level_no) END AS Assembly_WO

Query1

SELECT
   plant_sys,
   so_tran_order_number,
   so_tran_line_number,
   pegging_level_no,
   demand_order_number,
   supply_type_desc,
   supply_material,
   allocated_qty,
   ext_cost,
   pegging_seq_2,
   Sum(allocated_qty)OVER (partition BY plant_sys, supply_material) AS Total_Qty, 
   CASE WHEN est_cost <> 0 and supply_type_desc ='Production order - PO' THEN (est_cost/allocated_qty)end AS WoPO_cost_Ea,
  -- CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE LAG(Assembly_WO) OVER(PARTITION BY pegging_level_no) END AS Assembly_WO 
   CASE WHEN pegging_level_no = 0 THEN (supply_order_num) ELSE 'TEST' end AS Assembly_WO
FROM
   og_surf_snop_dm.sop_plan_calc_cp_t 
   WHERE PRODUCT_COMPANY = 'OFS' AND PLANT_SYS = '0003'

ORDER BY
   pegging_seq_2 ASC,
   pegging_level_no ASC;