Add up the value of each row and apply in the next row

I have Table A.

Table A
| Date        | Tenor    | CCY | Rate |
| --------    | -------- | --- | ---  |
| 17-Jun-2023 | 3 Month  | USD | 500  |
| 17-Jun-2023 | 4 Month  | USD | 300  |
| 17-Jun-2023 | 5 Month  | USD | 700  |
| 17-Jun-2023 | 6 Month  | USD | 400  |

I want to add up each row value in the rate column and put the result in the next row and then insert it into Table B. For example we add up the rates of 500 and 300 and put the result in Table B, which is 800.

Table B (expected result)
| Date        | Tenor    | CCY | Rate  |
| --------    | -------- | --- | ---   |
| 17-Jun-2023 | 3        | USD | 500   |
| 17-Jun-2023 | 4        | USD | 800   |
| 17-Jun-2023 | 5        | USD | 1000  |
| 17-Jun-2023 | 6        | USD | 1100  |

Can you help with the query? I'm using SQL Server.

Solution

We can use LAG with PARTITION and ORDER.

As mentioned in comments above, the exact way how to sort and group is unclear. But I assume you will be able to modify this if necessary.

According to your sample data, it seems to me you want to set PARTITION BY date to "reset" the rate when the date changes. And you want to ORDER BY date, tenor.

So the complete query to fetch the data you want to insert will be:

SELECT
  Date, Tenor, CCY, Rate,
  Rate + 
  LAG(Rate,1,0) 
    OVER (
     PARTITION BY date 
     ORDER BY Date, Tenor) AS RateToInsert
FROM A;

Note: The column Rate in this query will not be part of the insert command, I just added it to better check if the RateToInsert will be calculated correctly.

This will be the entire insert command based on above query:

INSERT INTO B 
  (Date, Tenor, CCY, Rate)
SELECT
  Date, Tenor, CCY,
  Rate + 
  LAG(Rate,1,0) 
    OVER (
     PARTITION BY date 
     ORDER BY Date, Tenor)
FROM A;

See this sample fiddle.

It is based on your sample data, but extended by rows having other dates to show how it works. You can see there first the result of the query and then the table B with the inserted rows.