Skip to content
Advertisement

Get previous value of current row in select

I am calculating value based on value in previous row. I used LAG for getting value from previous row and it works well for first two but not for next rows. Let me elaborate my scenario if row is first then I am taking Open_HA same as O column. But for next rows I am taking previous row Open_HA and adding it to previous row Close_HA to calculate the next row Open_HA and dividing it by 2 and so on. What I have done is below.

DECLARE @StockIndicator AS TABLE
(
Sr INT IDENTITY,
StartDate DATE,
O decimal(18,3),
H decimal(18,3),    
L decimal(18,3),    
C decimal(18,3),
Close_HA AS (O+H+L+C)/4
)
                                                                                                   
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-15',  93.25   ,93.30  ,93.30  ,93.25 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-16',  98.55   ,98.55  ,98.55  ,98.55 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-17',  100.98  ,99.99  ,100.98 ,99.99 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-18',  102.05  ,102.05 ,102.05 ,102.05)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-19',  103.00  ,103.90 ,103.90 ,103.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-20',  104.08  ,104.23 ,104.23 ,104.08)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-21',  104.90  ,104.60 ,105.00 ,104.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-22',  104.60  ,104.60 ,104.60 ,104.60)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-23',  105.90  ,105.90 ,105.90 ,105.90)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-24',  104.40  ,104.40 ,105.00 ,103.51)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-25',  105.18  ,105.18 ,105.18 ,105.18)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-26',  103.00  ,102.60 ,103.52 ,102.60)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-27',  100.00  ,100.00 ,100.00 ,100.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-28',  99.40   ,98.95  ,99.78  ,98.95 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-29',  99.00   ,99.00  ,99.00  ,99.00 )
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-09-30',  100.01  ,100.90 ,101.00 ,100.01)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-10-01',  102.00  ,102.70 ,102.70 ,102.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-10-02',  102.70  ,102.00 ,102.70 ,102.00)
INSERT INTO @StockIndicator(StartDate,O,H,L,C)VALUES('2020-10-03',  103.30  ,103.30 ,103.30 ,103.30)


SELECT Sr,
       O,
       H,
       L,
       C,
       CASE
            WHEN OuterDetail.Sr > 1 THEN ((LAG(OuterDetail.Open_HA) OVER (ORDER BY OuterDetail.Sr)) + (LAG(OuterDetail.[Close_HA]) OVER (ORDER BY OuterDetail.Sr))) / 2
            ELSE OuterDetail.Open_HA
       END AS Open_HA,
       [Close_HA]
FROM (SELECT Sr,
             O,
             H,
             L,
             C,
             CASE WHEN Detail.Sr = 1 THEN Detail.O ELSE 0 END AS Open_HA,
             [Close_HA]
      FROM (SELECT Sr, O, H, L, C, [Close_HA] FROM @StockIndicator) Detail ) OuterDetail;

Expected result should be like below image

enter image description here

For more clarification excel link that doing accurate calculation as I am trying to do with above query.

Advertisement

Answer

This can be done with recursion to perform the non-trivial running calculation related to the prior row.

The fiddle for SQL Server

First we calculate Close_HA in the first CTE term, then we recursively use that result to calculate Open_HA.

If you’ve already calculated Close_HA (it’s already in the table), we can skip the first CTE term.

The SQL:

WITH cte AS (
        SELECT *
             , (O + H + L + C)/4 AS Close_HA
          FROM test
     )
   , calc AS (
        SELECT t1.*, C AS Open_HA FROM cte AS t1 WHERE Sr = 1 UNION ALL
        SELECT t1.*
             , CAST((t2.Close_HA + t2.Open_HA) / 2.0 AS DECIMAL(8,2))
          FROM cte  AS t1
          JOIN calc AS t2
            ON t1.Sr = t2.Sr + 1
     )
SELECT * FROM calc ORDER BY Sr
;

The result:

+------+------------+--------+--------+--------+--------+------------+---------+
| Sr   | Date       | O      | H      | L      | C      | Close_HA   | Open_HA |
+------+------------+--------+--------+--------+--------+------------+---------+
|    1 | 2020-09-15 |  93.25 |  93.30 |  93.30 |  93.25 |  93.275000 |   93.25 |
|    2 | 2020-09-16 |  98.55 |  98.55 |  98.55 |  98.55 |  98.550000 |   93.26 |
|    3 | 2020-09-17 | 100.98 |  99.99 | 100.98 |  99.99 | 100.485000 |   95.91 |
|    4 | 2020-09-18 | 102.05 | 102.05 | 102.05 | 102.05 | 102.050000 |   98.20 |
|    5 | 2020-09-19 | 103.00 | 103.90 | 103.90 | 103.00 | 103.450000 |  100.13 |
|    6 | 2020-09-20 | 104.08 | 104.23 | 104.23 | 104.08 | 104.155000 |  101.79 |
|    7 | 2020-09-21 | 104.90 | 104.60 | 105.00 | 104.00 | 104.625000 |  102.97 |
|    8 | 2020-09-22 | 104.60 | 104.60 | 104.60 | 104.60 | 104.600000 |  103.80 |
|    9 | 2020-09-23 | 105.90 | 105.90 | 105.90 | 105.90 | 105.900000 |  104.20 |
|   10 | 2020-09-24 | 104.40 | 104.40 | 105.00 | 103.51 | 104.327500 |  105.05 |
|   11 | 2020-09-25 | 105.18 | 105.18 | 105.18 | 105.18 | 105.180000 |  104.69 |
|   12 | 2020-09-26 | 103.00 | 102.60 | 103.52 | 102.60 | 102.930000 |  104.94 |
|   13 | 2020-09-27 | 100.00 | 100.00 | 100.00 | 100.00 | 100.000000 |  103.94 |
|   14 | 2020-09-28 |  99.40 |  98.95 |  99.78 |  98.95 |  99.270000 |  101.97 |
|   15 | 2020-09-29 |  99.00 |  99.00 |  99.00 |  99.00 |  99.000000 |  100.62 |
|   16 | 2020-09-30 | 100.01 | 100.90 | 101.00 | 100.01 | 100.480000 |   99.81 |
|   17 | 2020-10-01 | 102.00 | 102.70 | 102.70 | 102.00 | 102.350000 |  100.15 |
|   18 | 2020-10-02 | 102.70 | 102.00 | 102.70 | 102.00 | 102.350000 |  101.25 |
|   19 | 2020-10-03 | 103.30 | 103.30 | 103.30 | 103.30 | 103.300000 |  101.80 |
+------+------------+--------+--------+--------+--------+------------+---------+

Setup for the test case:

CREATE TABLE test (
    Sr     int
  , Date   Date
  , O      DECIMAL(8,2)
  , H      DECIMAL(8,2)
  , L      DECIMAL(8,2)
  , C      DECIMAL(8,2)
);

INSERT INTO test VALUES
  ( 1,'2020-09-15', 93.25, 93.30, 93.30, 93.25)
, ( 2,'2020-09-16', 98.55, 98.55, 98.55, 98.55)
, ( 3,'2020-09-17',100.98, 99.99,100.98, 99.99)
, ( 4,'2020-09-18',102.05,102.05,102.05,102.05)
, ( 5,'2020-09-19',103.00,103.90,103.90,103.00)
, ( 6,'2020-09-20',104.08,104.23,104.23,104.08)
, ( 7,'2020-09-21',104.90,104.60,105.00,104.00)
, ( 8,'2020-09-22',104.60,104.60,104.60,104.60)
, ( 9,'2020-09-23',105.90,105.90,105.90,105.90)
, (10,'2020-09-24',104.40,104.40,105.00,103.51)
, (11,'2020-09-25',105.18,105.18,105.18,105.18)
, (12,'2020-09-26',103.00,102.60,103.52,102.60)
, (13,'2020-09-27',100.00,100.00,100.00,100.00)
, (14,'2020-09-28', 99.40, 98.95, 99.78, 98.95)
, (15,'2020-09-29', 99.00, 99.00, 99.00, 99.00)
, (16,'2020-09-30',100.01,100.90,101.00,100.01)
, (17,'2020-10-01',102.00,102.70,102.70,102.00)
, (18,'2020-10-02',102.70,102.00,102.70,102.00)
, (19,'2020-10-03',103.30,103.30,103.30,103.30)
;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement