I have the following table:
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)
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 (forgive me if that doesn't look the most beautiful set of data you've seen today):
Select * from import.dre:
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
Is there some way to accomplish that? I'm trying (badly, I know) to use a for loop to update import.dre but I'm getting nowhere. If by any chance there's a better way to do it than for loop, I appreciate. I don't know how big import.dre can get.
I tried a lot of codes, but all of them updates the whole table (instead of calculating each row). The last function that i've created to debug is the following:
create or replace function teste()
returns void
language plpgsql
as
$$
declare
line record;
v_companynumber int;
v_idproduct int;
v_nrtank int;
v_dtdate date;
v_startbalance numeric;
v_endbalance numeric;
begin
for line in
select
*
from
import.dre
order by
companynumber, idproduct, nrtank desc, dtdate
loop
v_companynumber := line.companynumber;
v_idproduct := line.idproduct;
v_nrtank := line.nrtank;
v_dtdate := line.dtdate;
v_startbalance := lag(coalesce(line.endbalance,0), 1) over (
partition by line.companynumber, line.idproduct, line.nrtank order by line.dtdate);
v_endbalance := line.endbalance;
update import.dre
set startbalance = v_startbalance, endbalance = v_endbalance
where
companynumber = v_companynumber
and idproduct = v_idproduct
and nrtank = v_nrtank
and dtdate = v_dtdate;
end loop;
end;
This last query didn't assign nothing at startbalance. Tried another ones, but there is a lot of querys to put here (the best one that I got put 7600 on each day at tank 5 after but I don't remember how I got there)
You can do a CTE first to calculate the endbalance amd in the second steop you fill the startbalance.
WITH CTE AS (SELECT idmov,
companynumber,
idproduct,
dtdate,
nrtank,
dailybalance,
startbalance,
SUM(COALESCE(endbalance,0)+dailybalance)
OVER w1 AS endbalance
FROM import.dre
WINDOW w1 AS (PARTITION BY companynumber,idproduct,nrtank
ORDER BY dtdate))
SELECT idmov,
companynumber,
idproduct,
dtdate,
nrtank,
dailybalance,
LAG(endbalance)OVER w2 AS startbalance,
endbalance
FROM CTE
WINDOW w2 AS (PARTITION BY companynumber,idproduct,nrtank
ORDER BY dtdate);
| 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 | 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 |