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
For more clarification excel link that doing accurate calculation as I am trying to do with above query.
CodePudding user response：
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)
;
CodePudding user response：
As the row you are calculating uses the result from it's formula for the last row, it's not operating over the result set, but it's being dynamically created row by row, so the lag function won't work here. What you can do is use a cursor to populate that column:
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),
Open_HA decimal(18,3), -- I've added the column to the temp variable
Close_HA AS (O H L C)/4
)
declare @sr int, @close_ha decimal(18,3), @O decimal(18,3)
declare @last_close_ha decimal(18,3), @last_open_ha decimal(18,3)
declare cur cursor local FOR
select Sr, Close_HA, O
from @StockIndicator
order by Sr asc
for update of Open_HA
open cur
fetch next from cur into @sr, @close_ha, @o
while @@fetch_status = 0
BEGIN
set @last_open_ha = case @Sr
when 1 then @O
else (@last_open_ha @last_close_ha)/2
end
set @last_close_ha = @close_ha
update @StockIndicator set
Open_HA = @last_open_ha
where Sr=@Sr
fetch next from cur into @sr, @close_ha, @o
end
close cur
deallocate cur
select *
from @StockIndicator
You can see it working on DB Fiddle here.