Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE testdata (
    date date,
    column1 int,
    column2 int,
    column3 int
);

INSERT INTO testdata VALUES
('2017-01-01', 10, 10, 20),
('2017-01-02', 10, 10, 20),
('2017-01-03', 10, 10, 20),
('2017-01-04', 10, 10, 20),
('2017-01-05', 10, 10, 20),
('2017-01-06', 10, 10, 20);

SELECT * FROM testdata;
6 rows affected



date
column1
column2
column3


2017-01-01
10
10
20

2017-01-02
10
10
20

2017-01-03
10
10
20

2017-01-04
10
10
20

2017-01-05
10
10
20

2017-01-06
10
10
20


SELECT 
    "date",
    closing - day_value as opening,
    column1,
    column2,
    column3, 
    closing
FROM (
    SELECT 
         *, 
         column1 + column2 + column3 as day_value, 
         SUM(column1 + column2 + column3) OVER (ORDER BY "date") AS closing
    FROM testdata
) s


date
opening
column1
column2
column3
closing


2017-01-01
0
10
10
20
40

2017-01-02
40
10
10
20
80

2017-01-03
80
10
10
20
120

2017-01-04
120
10
10
20
160

2017-01-05
160
10
10
20
200

2017-01-06
200
10
10
20
240


SELECT * FROM (
    SELECT 
        "date",
        closing - day_value as opening, 
        column1,
        column2,
        column3, 
        closing
    FROM (
        SELECT 
            *, 
            column1 + column2 + column3 as day_value, 
            SUM(column1 + column2 + column3) OVER (ORDER BY "date") AS closing
        FROM testdata
    ) s
) s
WHERE "date" BETWEEN '2017-01-03' AND '2017-01-06'


date
opening
column1
column2
column3
closing


2017-01-03
80
10
10
20
120

2017-01-04
120
10
10
20
160

2017-01-05
160
10
10
20
200

2017-01-06
200
10
10
20
240

date	column1	column2	column3
2017-01-01	10	10	20
2017-01-02	10	10	20
2017-01-03	10	10	20
2017-01-04	10	10	20
2017-01-05	10	10	20
2017-01-06	10	10	20