Write value from actual date in each row per productid-CodePudding

How do I get the value of the current day per ProductID to be written to each row of the product via SQL? Ideally, without joining the same table again, because it is a very large amount of data.

Source Data (simplified):

productid   date         value
1           10.08.2022   4
1           11.08.2022   2        
1           12.08.2022   3
1           13.08.2022   2
1           14.08.2022   1
2           10.08.2022   5
2           11.08.2022   2        
2           12.08.2022   4
2           13.08.2022   1
2           14.08.2022   6

Output should be (actual date 12.08.2022):

productid   date         value    value_act_date
1           10.08.2022   4        3
1           11.08.2022   2        3
1           12.08.2022   3        3
1           13.08.2022   2        3
1           14.08.2022   1        3
2           10.08.2022   5        4
2           11.08.2022   2        4
2           12.08.2022   4        4
2           13.08.2022   1        4
2           14.08.2022   6        4

Thanks!

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (pid, datum, value) as
  2    (select 1, date '2022-08-10', 4 from dual union all
  3     select 1, date '2022-08-12', 3 from dual union all
  4     select 1, date '2022-08-13', 2 from dual union all
  5     --
  6     select 2, date '2022-08-11', 2 from dual union all
  7     select 2, date '2022-08-12', 4 from dual union all
  8     select 2, date '2022-08-14', 6 from dual
  9    )

Query begins here:

 10  select pid, datum, value,
 11    max(case when datum = trunc(sysdate) then value end)
 12      over (partition by pid order by null) todays_value
 13  from test
 14  order by pid, datum;

       PID DATUM           VALUE TODAYS_VALUE
---------- ---------- ---------- ------------
         1 10.08.2022          4            3
         1 12.08.2022          3            3
         1 13.08.2022          2            3
         2 11.08.2022          2            4
         2 12.08.2022          4            4
         2 14.08.2022          6            4

6 rows selected.

SQL>