I want to get the value of the previous row, current row, and add them. If there's no previous row then I'll just set previous to 0. Here's what I tried.
SET @previous = CASE WHEN (SELECT MAX(value) FROM table WHERE dateTime < '2018-07-01') IS NULL
THEN 0
ELSE (SELECT MIN(value) FROM table WHERE dateTime BETWEEN '2018-07-01' AND '2018-07-30')
END;
SELECT @previous AS Previous,
@previous:=MAX(value) AS Current,
(@previous + MAX(value)) AS Joined
FROM table
WHERE dateTime BETWEEN '2018-07-01' AND '2018-07-30'
GROUP BY dateTime;
Here's the table content:
dateTime | value
2018-07-01 | 1
2018-07-01 | 1.2
2018-07-02 | 1.7
2018-07-03 | 2.1
2018-07-05 | 2.6
2018-07-05 | 3
2018-07-06 | 3.5
Unfortunately setting @previous:=MAX(value) does not change @previous value for the next row, if it's 0 in initialization, it will remain 0. Sample Result:
dateTime | Previous | Current | Joined
2018-07-01 | 0 | 1.2 | 1.2
2018-07-02 | 0 | 1.7 | 1.7
2018-07-03 | 0 | 2.1 | 2.1
2018-07-05 | 0 | 4 | 4
2018-07-06 | 0 | 4 | 4
Expected result
dateTime | Previous | Current | Joined
2018-07-01 | 0 | 1.2 | 1.2
2018-07-02 | 1.2 | 1.7 | 2.9
2018-07-03 | 1.7 | 2.1 | 3.8
2018-07-05 | 2.1 | 3 | 5.1
2018-07-06 | 3 | 3.5 | 6.5
Hope you can help me!