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.
x
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
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.
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);