LAG function with current row null and previous entry present-CodePudding

I have used LAG function however unable to get records in which current row is null but previous row is present .

WITH DATA AS
(
Select 
COL_A ,
COL_B ,
COL_C ,
LAG(COL_C) OVER (PARTITION BY COL_B ORDER BY COL_A) PREV_COL_C 
from TABLE_COL
 )
 Select * from data where COL_A = '18-AUG-2022'

How to achieve the output with different ways? Below is my data

I am trying to achieve below output

CodePudding user response：

If you always have a single date and it's previous calendar date, then the task is much simplet and you may use conditional aggregation to distribute values between columns:

with function something_that_provides_a_date
/*May be replaced with binf variable or constant*/
return date
as
  pragma udf;
begin
  return date '2022-08-18';
end;

select /* gather_plan_statistics*/
  something_that_provides_a_date() as col_a,
  col_b,
  max(case col_a
    when something_that_provides_a_date()
    then col_c
  end) as curr_col_c,
  max(case col_a
    when something_that_provides_a_date() - 1
    then col_c
  end) as prev_col_c
from input_tab
where col_a in (something_that_provides_a_date(), something_that_provides_a_date() - 1)
group by col_b

COL_A	COL_B	CURR_COL_C	PREV_COL_C
18-AUG-22	AA	400	100
18-AUG-22	BB	500	200
18-AUG-22	DD	null	300
18-AUG-22	СС	600	null

db<>fiddle here

CodePudding user response：

If I understand correctly, you want one row for each value of COL_B, with CURR_COLL_C set to the value of COLL_C for the current date (or null if it has no value for the current date), and PREV_COL_C set to the previous value of COLL_C (or null if COLL_C is not set for any date before the current date). If so, this query should work:

select col_a, col_b, cur_coll_c, prev_coll_c 
from (
  select 
    trunc(sysdate) col_a, 
    col_b_values.col_b col_b,
    curr.coll_c cur_coll_c,
    prev.coll_c prev_coll_c,
    row_number() over (partition by prev.col_b order by prev.col_a desc) rn
  from data curr
  join (select distinct col_b from data) as col_b_values
  left join data as curr 
    on curr.col_a = trunc(sysdate) and curr.col_b = col_b_values.col_b
  left join data as prev
    on prev.col_a < trunc(sysdate) and prev.col_b = col_b_values.col_b
)
where rn = 1
order by col_b;