Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create schema import;
CREATE TABLE IF NOT EXISTS import.dre
(
    idmov integer,
    companynumber integer,
    idproduct integer,
    dtdate date,
    nrtank integer,
    dailybalance numeric,
    startbalance numeric,
    endbalance numeric
);
--Populated with the following query (10 first lines only):

INSERT INTO import.dre values 
(1, 104, 10, '30/09/2023', 5, 0, NULL, 7600),
(2, 104, 10, '01/10/2023', 5,-1089.42, NULL, NULL),
(3, 104, 10, '02/10/2023', 5,-404.62, NULL, NULL),
(4, 104, 10, '03/10/2023', 5,-470.57, NULL, NULL),
(5, 104, 10, '04/10/2023', 5,-604.97, NULL, NULL),
(6, 104, 10, '05/10/2023', 5,10381.23, NULL, NULL),
(7, 104, 10, '06/10/2023', 5,-593.54, NULL, NULL),
(8, 104, 10, '07/10/2023', 5,-713.27, NULL, NULL),
(9, 104, 10, '08/10/2023', 5,-995.01, NULL, NULL),
(10, 104, 10, '09/10/2023', 5,-736.25, NULL, NULL)
returning *;

CREATE SCHEMA



CREATE TABLE




idmov
companynumber
idproduct
dtdate
nrtank
dailybalance
startbalance
endbalance


1
104
10
2023-09-30
5
0
null
7600

2
104
10
2023-10-01
5
-1089.42
null
null

3
104
10
2023-10-02
5
-404.62
null
null

4
104
10
2023-10-03
5
-470.57
null
null

5
104
10
2023-10-04
5
-604.97
null
null

6
104
10
2023-10-05
5
10381.23
null
null

7
104
10
2023-10-06
5
-593.54
null
null

8
104
10
2023-10-07
5
-713.27
null
null

9
104
10
2023-10-08
5
-995.01
null
null

10
104
10
2023-10-09
5
-736.25
null
null



INSERT 0 10


/*
I have the task to update this table follwing this pattern: 
1st: startbalance must be coalesce(endbalance,0) from the last day, 
grouped by companynumber, idproduct, nrtank. 
So, using those 10 lines, at day 01/10/2023 startbalance should be 7600 and so on.

2nd: endbalance must be the startbalance of the day plus dailybalance. 
So, at those 10 lines, at day 01/10/2023 end balance 
should be 7600 + (- 1089.42) and so on.

The Final result must look something like this 
1, 104, 10, '30/09/2023', 5, 0, NULL, 7600
2, 104, 10, '01/10/2023', 5,-1089.42, 7600, 6510,58 -- 7600-1089.42
3, 104, 10, '02/10/2023', 5,-404.62, 6510,58, 6105,96 -- 6510.58-404.62
4, 104, 10, '03/10/2023', 5,-470.57, 6105.96, 5635,39
5, 104, 10, '04/10/2023', 5,-604.97, 5635,39, 5030,42
6, 104, 10, '05/10/2023', 5,10381.23, 5030,42, 15411,65
7, 104, 10, '06/10/2023', 5,-593.54, 15411,65, 14818,11
8, 104, 10, '07/10/2023', 5,-713.27, 14818,11, 14104,84
9, 104, 10, '08/10/2023', 5,-995.01, 14104,84, 13109,83
10, 104, 10, '09/10/2023', 5,-736.25, 13109,83, 12373,58
*/
--cleaner, still makes sense
with cte as (
    select ctid
          ,first_value(endbalance)over w1
           + sum(dailybalance)over w1
           - dailybalance as startbalance
          ,first_value(endbalance)over w1
           + sum(dailybalance)over w1 as endbalance
    from import.dre
    window w1 as (partition by companynumber, idproduct, nrtank
                  order by dtdate) )
update import.dre as this
set startbalance=cte.startbalance,
    endbalance  =cte.endbalance
from cte where this.ctid=cte.ctid
returning this.*;


idmov
companynumber
idproduct
dtdate
nrtank
dailybalance
startbalance
endbalance


1
104
10
2023-09-30
5
0
7600
7600

2
104
10
2023-10-01
5
-1089.42
7600.00
6510.58

3
104
10
2023-10-02
5
-404.62
6510.58
6105.96

4
104
10
2023-10-03
5
-470.57
6105.96
5635.39

5
104
10
2023-10-04
5
-604.97
5635.39
5030.42

6
104
10
2023-10-05
5
10381.23
5030.42
15411.65

7
104
10
2023-10-06
5
-593.54
15411.65
14818.11

8
104
10
2023-10-07
5
-713.27
14818.11
14104.84

9
104
10
2023-10-08
5
-995.01
14104.84
13109.83

10
104
10
2023-10-09
5
-736.25
13109.83
12373.58



UPDATE 10


--puts a null in the first startbalance, which requires a `case`
with cte as (
    select ctid
          ,case when row_number()over w1>1 
                then first_value(endbalance)over w1
           end
           + sum(dailybalance)over w1
           - dailybalance as startbalance
          ,first_value(endbalance)over w1
           + sum(dailybalance)over w1 as endbalance
    from import.dre
    window w1 as (partition by companynumber, idproduct, nrtank
                  order by dtdate) )
update import.dre as this
set startbalance=cte.startbalance,
    endbalance  =cte.endbalance
from cte where this.ctid=cte.ctid
returning this.*;


idmov
companynumber
idproduct
dtdate
nrtank
dailybalance
startbalance
endbalance


1
104
10
2023-09-30
5
0
null
7600

2
104
10
2023-10-01
5
-1089.42
7600.00
6510.58

3
104
10
2023-10-02
5
-404.62
6510.58
6105.96

4
104
10
2023-10-03
5
-470.57
6105.96
5635.39

5
104
10
2023-10-04
5
-604.97
5635.39
5030.42

6
104
10
2023-10-05
5
10381.23
5030.42
15411.65

7
104
10
2023-10-06
5
-593.54
15411.65
14818.11

8
104
10
2023-10-07
5
-713.27
14818.11
14104.84

9
104
10
2023-10-08
5
-995.01
14104.84
13109.83

10
104
10
2023-10-09
5
-736.25
13109.83
12373.58



UPDATE 10
idmov	companynumber	idproduct	dtdate	nrtank	dailybalance	startbalance	endbalance
1	104	10	2023-09-30	5	0	null	7600
2	104	10	2023-10-01	5	-1089.42	null	null
3	104	10	2023-10-02	5	-404.62	null	null
4	104	10	2023-10-03	5	-470.57	null	null
5	104	10	2023-10-04	5	-604.97	null	null
6	104	10	2023-10-05	5	10381.23	null	null
7	104	10	2023-10-06	5	-593.54	null	null
8	104	10	2023-10-07	5	-713.27	null	null
9	104	10	2023-10-08	5	-995.01	null	null
10	104	10	2023-10-09	5	-736.25	null	null