Oracle SQL Calculate growth value from ONE column-CodePudding

I would like to calculate

INCOME_growth = INCOME_YTD - Income_prv_Month

I tried lag function, self join I couldn't get it to work

Example (starting point) table_employee_info:

| Date        |Employee_NO|INCOME_YTD  |Date_ID|
---           |---        |---         |---    |
| 1/31/2022   |1002       |50          |202230
| 2/28/2022   |1045       |80          |202260
| 3/31/2022   |F104       |40          |202290
| 1/31/2022   |1002       |30          |202230
| 2/28/2022   |1045       |90          |202260
| 3/31/2022   |F104       |100         |202290
| 1/31/2022   |1002       |65          |202230
| 2/28/2022   |1045       |60          |202260
| 3/31/2022   |F104       |200         |202290

The desired result will look like this: first, we will need to order by Employee_NO then Date. second, calculate new column Income_prv_Month. third, calculate another column INCOME_growth.

| Date        |Employee_NO|INCOME_YTD  |Income_prv_Month|INCOME_growth|Date_ID|
---           |---        |---         |---             |---         |---   
| 1/31/2022   |1002       |50          |0               |50          |202230
| 1/31/2022   |1002       |30          |50              |-20         |202230
| 1/31/2022   |1002       |65          |30              |30          |202230
| 2/28/2022   |1045       |80          |0               |80          |202260
| 2/28/2022   |1045       |90          |80              |10          |202260
| 2/28/2022   |1045       |60          |90              |-30         |202260
| 3/31/2022   |F104       |40          |0               |40          |202290  
| 3/31/2022   |F104       |100         |40              |60          |202290
| 3/31/2022   |F104       |200         |100             |100         |202290

I will be grateful for any tips or help

Thanks in advance

CodePudding user response：

I think you can try to use LAG window function but the real problem is there might not be a column that can represent your order of expected results as I comment, if there is a column we can replace as my below query [column which represnt your expect order] part

SELECT t1.*,
       LAG(INCOME_YTD,1,0) OVER(PARTITION BY Employee_NO ORDER BY date_ID ) Income_prv_Month,
       INCOME_YTD - LAG(INCOME_YTD,1,0) OVER(PARTITION BY Employee_NO ORDER BY date_ID ) INCOME_growth
FROM T t1