I have a table T like this:

ID|DESC1_ID | DESC2_ID | TS     | CUM_MONTH_PREV_YEAR| CUM_MONTH_THIS_YEAR|ID2
--------------------------------------------------------------------------|-
1 |1        |1         |31.01.22|                    | 220                |1
----------------------------------------------------------------------------
2 |1        |2         |31.01.22|                    | 500                |1
---------------------------------------------------------------------------
3 |1        |3         |31.01.22|                    | 22                 |1
----------------------------------------------------------------------------
4 |2        |1         |31.01.22|                    | 50                 |1
---------------------------------------------------------------------------
5 |1        |1         |01.02.23|                    | 230                |2
----------------------------------------------------------------------------
6 |1        |2         |01.02.23|                    | 300                |2
---------------------------------------------------------------------------
7 |1        |3         |01.02.23|                    | 32                 |2
----------------------------------------------------------------------------
8 |2        |1         |01.02.23|                    | 30                 |2 

I wish to update the field CUM_MONTH_PREV_YEAR for all entries where id2 is 2. The value should be a previous year value. It could happen, that the date(TS) is not exactly from 1 year before. I have a query below which does what I want.

UPDATE T t1  
    SET CUM_MONTH_PREV_YEAR = (SELECT NVL(CUM_MONTH_THIS_YEAR , 0)
                                FROM T t2
                              WHERE t2.TS = (SELECT MAX(TS) 
                                             FROM T
                                             WHERE TS BETWEEN  ADD_MONTHS( t1.TS - 7, -12) AND ADD_MONTHS( t1.TS, -12)
                                             AND DESC1_ID = t1.DESC1_ID
                                             AND DESC2_ID = t1.DESC2_ID )
                            AND t2.DESC1_ID = t.DESC1_ID
                            AND t2.DESC2_ID = t.DESC2_ID )  
WHERE ID2 = 2 ;

I wanted to ask if there is a better way to reach the goal.

UPDATE:

As everything is simplified it happened only by the coincidence that ID2 for previous year is 1 and for this year is 2. In the reality there are much more values.

CodePudding user response：

Looking at your data, I think you are looking for this:

UPDATE table_name A
SET CUM_MONTH_PREV_YEAR = (SELECT B.CUM_MONTH_THIS_YEAR
                           FROM table_name B
                           WHERE A.id2 - 1 = B.id2
                             AND A.DESC1_ID = B.DESC1_ID
                             AND A.DESC2_ID = B.DESC2_ID);
                             
 SELECT *
 FROM table_name;    

CodePudding user response：

Try this

UPDATE A_TBL t
SET t.CUM_MONTH_PREV_YEAR = (  Select PREV_CUM  
                               From ( SELECT      t.DESC1_ID, t.DESC2_ID,  EXTRACT(Year From TS) "YR", MAX(t.CUM_MONTH_THIS_YEAR) "PREV_CUM"
                                      FROM        A_TBL t
                                      WHERE       EXTRACT(Year From t.TS) = (Select MAX(EXTRACT(Year From TS)) From A_TBL WHERE DESC1_ID = t.DESC1_ID And DESC2_ID = t.DESC2_ID And ID2 != 2) And
                                                  EXTRACT(Year From t.TS) < (Select MAX(EXTRACT(Year From TS)) From A_TBL WHERE ID2 = 2)
                                      GROUP BY    t.DESC1_ID, t.DESC2_ID, EXTRACT(Year From t.TS) 
                                    )
                              Where DESC1_ID = t.DESC1_ID And DESC2_ID = t.DESC2_ID  )
WHERE t.ID2 = 2;

All that you need is the innermost query which gives you the data from last year present that is less than year of rows with ID2 = 2 (ones beeing updated). This means that, in this case, it will take the rows from 2022.

SELECT      t.DESC1_ID, t.DESC2_ID,  EXTRACT(Year From TS) "YR", MAX(t.CUM_MONTH_THIS_YEAR) "PREV_CUM"
FROM        A_TBL t
WHERE       EXTRACT(Year From t.TS) = (Select MAX(EXTRACT(Year From TS)) From A_TBL WHERE DESC1_ID = t.DESC1_ID And DESC2_ID = t.DESC2_ID And ID2 != 2) And
            EXTRACT(Year From t.TS) < (Select MAX(EXTRACT(Year From TS)) From A_TBL WHERE ID2 = 2)
GROUP BY    t.DESC1_ID, t.DESC2_ID, EXTRACT(Year From t.TS)

  DESC1_ID   DESC2_ID         YR   PREV_CUM
---------- ---------- ---------- ----------
         2          1       2022         50 
         1          2       2022        500 
         1          3       2022         22 
         1          1       2022        220

... if ID = 1 row has the column TS value lets say '13.02.1911' - and there is no later data - the data will be fetched anyway.

  DESC1_ID   DESC2_ID         YR   PREV_CUM
---------- ---------- ---------- ----------
         2          1       2022         50 
         1          2       2022        500 
         1          3       2022         22 
         1          1       1911        220

The result after the update is:

Select * From A_TBL;