# Thread: Calculate current row cell value with previous row cell value

1. ## Calculate current row cell value with previous row cell value

Hi All,

Happy Thanksgiving!!

Basically I am trying to calculate moving average which considers current row value with previous row value but unable to succeed. Hence reaching out here to you experts.

Here's the db<>fiddle tried so far which has table, data and query.

I have data as below:
Code:
```+-----------+-------+------------+--------+
| Serial_No | Stock | Close_Date | PRICE  |
+-----------+-------+------------+--------+
|         1 | XYZ   | 01-01-2021 |    315 |
|         2 | XYZ   | 02-01-2021 |  311.1 |
|         3 | XYZ   | 03-01-2021 |  308.1 |
|         4 | XYZ   | 04-01-2021 |    310 |
|         5 | XYZ   | 05-01-2021 |  308.7 |
|         6 | XYZ   | 06-01-2021 |  311.2 |
|         7 | XYZ   | 07-01-2021 | 313.75 |
|         8 | XYZ   | 08-01-2021 |  314.7 |
|         9 | XYZ   | 09-01-2021 | 308.05 |
|        10 | ABC   | 01-01-2021 |     14 |
|        11 | ABC   | 02-01-2021 |     12 |
|        12 | ABC   | 03-01-2021 |     11 |
|        13 | ABC   | 04-01-2021 |      8 |
|        14 | ABC   | 05-01-2021 |      9 |
|        15 | ABC   | 06-01-2021 |      7 |
|        16 | ABC   | 07-01-2021 |      5 |
|        17 | ABC   | 08-01-2021 |     10 |
+-----------+-------+------------+--------+```
Below is the output I am expecting:
Code:
```+-----------+-------+----------+--------+---------+-------+
| SERIAL_NO | STOCK |  CDATE   | PRICE  | MOV_AVG | FLAG  |
+-----------+-------+----------+--------+---------+-------+
|         1 | XYZ   | 1-Jan-21 |    315 |         |       |
|         2 | XYZ   | 2-Jan-21 |  311.1 |         |       |
|         3 | XYZ   | 3-Jan-21 |  308.1 |         |       |
|         4 | XYZ   | 4-Jan-21 |    310 |         |       |
|         5 | XYZ   | 5-Jan-21 |  308.7 |  310.58 | Red   |
|         6 | XYZ   | 6-Jan-21 |  311.2 |  310.89 | Green |
|         7 | XYZ   | 7-Jan-21 | 313.75 |  312.32 | Green |
|         8 | XYZ   | 8-Jan-21 |  314.7 |  313.51 | Green |
|         9 | XYZ   | 9-Jan-21 | 308.05 |  310.78 | Red   |
|        10 | ABC   | 1-Jan-21 |     14 |         |       |
|        11 | ABC   | 2-Jan-21 |     12 |         |       |
|        12 | ABC   | 3-Jan-21 |     11 |         |       |
|        13 | ABC   | 4-Jan-21 |      8 |         |       |
|        14 | ABC   | 5-Jan-21 |      9 |   10.80 | Red   |
|        15 | ABC   | 6-Jan-21 |      7 |    8.90 | Red   |
|        16 | ABC   | 7-Jan-21 |      5 |    6.95 | Red   |
|        17 | ABC   | 8-Jan-21 |     10 |    8.48 | Green |
+-----------+-------+----------+--------+---------+-------+```
Calculation and formula:
Here row 5 moving average is calculated as average price of row 1 to 5
whereas for row 6 onwards, it must consider value of row 5 MOV_AVG and also row 6 PRICE value.
Below two screenshots shows the formula from excel:
To calculate 5 days MOVING AVERAGE till row 5
soavg.png

Formula to calculate moving average from row 6:
soavgcalc.png

Formula to calculate new column FLAG:
If PRICE is greater than MOV_AVG value then Green else Red.

I was able to achieve calculation till row 5 but not sure how to do it from row 6 onwards. Below is the code tried:
Code:
```with
rd_fixed as (select SERIAL_NO,STOCK,CDATE,PRICE from rawdata)
,pred as (
SELECT
SERIAL_NO,STOCK,CDATE,PRICE
FROM   rd_fixed
MATCH_RECOGNIZE (
PARTITION BY STOCK
ORDER BY CDATE
MEASURES
COUNT(*) AS VALUE_MATCH,
CLASSIFIER() AS PATTERN
ALL ROWS PER MATCH
PATTERN (
down+  | up+  | other
)
DEFINE
down AS PREV(price) > price,
up AS  PREV(price) < price
)
)
,moving_average as (
select
pred.*
,round(
avg(PRICE)
over(
partition by STOCK
order by CDATE
range between 5 preceding and current row
)
,2) as "MOV_AVG"
,count(*)
over(
partition by STOCK
order by CDATE
range between 5 preceding and current row
) as cnt5
from pred
)
select
ma.*
,case when cnt5 = 5 then round("MOV_AVG",2) end as only_5_MOV_AVG
from moving_average ma```
Forum not allowing to attach excel spreadsheet that includes formula. Hence tried to describe the problem in detail. If there is a way to attach excel please let me know.

Appreciate any help.

Thanks,
Richa

2. Hi All,

Please ignore this post. Before this OP was approved, I found answer. Thank you.

Thanks,
Richa