PostgreSQL year, quarter calculator

Question

Given a year and a quarter, a procedure should INSERT values into table tb_quarter, which is empty and have this structure:

CREATE TABLE erp.tb_quarter    (
    quarter            INT NOT NULL,
    year               INT NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    iva_percent        INT NOT NULL,
    amount             REAL NOT NULL,
    CONSTRAINT pk_quarter_year PRIMARY KEY (quarter,year),
    CONSTRAINT fk_cust_no FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no)
  );


CREATE TABLE erp.tb_invoice (
    co_code            CHARACTER(3) NOT NULL,
    invoice_id         INT NOT NULL,
    invoice_no         CHARACTER VARYING(15)  NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    site_id            INT NOT NULL,
    payed              CHARACTER(1) NOT NULL DEFAULT 'N',
    net_amount         REAL NOT NULL,
    iva_amount         REAL NOT NULL,
    tot_amount         REAL NOT NULL,
    last_updated_by    CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date   DATE NOT NULL,
    CONSTRAINT pk_invoice PRIMARY KEY (invoice_id),
    CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code),
    CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no),
    CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id)  
  );

CREATE TABLE erp.tb_customer    (
    cust_no          CHARACTER(5) NOT NULL,
    cust_name        CHARACTER VARYING(50) NOT NULL,
    cust_cif         CHARACTER VARYING(150) NOT NULL,
    last_updated_by  CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date DATE NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (cust_no)
  );

So far, I've got this code, but it's not clear for me how to structure the body of the procedure. i think a cursor could help but don't know how to put all togheter:

CREATE PROCEDURE pr_calc_quarter( year INT , quarter INT ) AS $$
BEGIN
FOR 
    SELECT
        EXTRACT(QUARTER FROM i.last_update_date) AS trimestre,
        EXTRACT(year FROM i.last_update_date)  AS año,
        c.cust_no,
        round((i.iva_amount * 100 / net_amount)::numeric,0) AS iva_percent,
        SUM(i.iva_amount) AS amount
    FROM erp.tb_customer c JOIN erp.tb_invoice i ON
        i.cust_no = c. cust_no
    GROUP BY iva_percent, c.cust_no, i.last_update_date
    ORDER BY c.cust_no, iva_percent, amount
    LOOP
        INSERT INTO erp.tb_quarter
        VALUES (year, quarter, iva_percent, amount);
    END LOOP;  
 END;
 $$ LANGUAGE plpgsql;  
 CALL erp.pr_calc_quarter(2022,2)

Finally, some data to test it:

INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0001','PIENSOS MARTIN','A12345678','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0002','AGRICULTURA VIVES','A66666666','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0003','CULTIVOS MARAVILLA','A55555555','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0004','ASOCIADOS PEREZ','A23126743','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0005','TECNICOS AVA','B34211233','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0006','AGR AGRI','B78788999','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0007','AGRIMARCOS','B98766562','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0008','CULTIVANDO ALEGRIA','B12333123','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0009','MARCOS LIMPIEZA','A87727711','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0010','VIAJES MUNDO','A00099982','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0011','SEMILLAS MARIA','B98121222','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0012','PIENSOS JAIME','B12337788','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0013','GESTORIA LUIS','B18999001','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0014','CONSULTORES ORC','B17772211','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0015','APR T','A00123333','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0016','AGR SANT JOAN ','B81232171','SYSTEM',current_date);
INSERT INTO erp.tb_customer  (cust_no, cust_name, cust_cif, last_updated_by, last_update_date) VALUES ('C0017','DISFRUTA SEMILLA','A12341999','SYSTEM',current_date);

INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',200,'F0000C000400200','C0004',6,'Y',25984.36,2802.34,28786.7,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',201,'F0000C000400201','C0004',6,'N',5134.2,490.09,5624.29,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',202,'F0000C000400202','C0004',6,'Y',22554.69,3120.85,25675.54,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',203,'F0000C000400203','C0004',6,'Y',31766.97,3712.75,35479.72,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',204,'F0000C000400204','C0004',6,'Y',16863.86,1811.27,18675.13,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',205,'F0000C000400205','C0004',6,'Y',15772.42,1582.37,17354.79,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',206,'F0000C000400206','C0004',6,'Y',21486.14,3045.65,24531.79,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',207,'F0000C000400207','C0004',6,'Y',21496.86,2000.61,23497.47,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',208,'F0000C000400208','C0004',6,'N',16715.91,2365.57,19081.48,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',209,'F0000C000400209','C0004',6,'N',18246.24,2150.09,20396.33,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',210,'F0000C000400210','C0004',6,'Y',19250.2,2595.01,21845.21,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AB1',211,'F0000C000400211','C0004',6,'Y',8213.28,1491.36,9704.64,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AX3',212,'F0000C000500212','C0005',7,'N',12193.41,2560.62,14754.03,'SYSTEM',current_date);
INSERT INTO erp.tb_invoice (CO_CODE,INVOICE_ID,INVOICE_NO,CUST_NO,SITE_ID,PAYED,NET_AMOUNT,IVA_AMOUNT,TOT_AMOUNT,LAST_UPDATED_BY,LAST_UPDATE_DATE) VALUES ('AX3',213,'F0000C000500213','C0005',7,'N',2540.08,533.42,3073.5,'SYSTEM',current_date);

Where is your SELECT statement that should calculate the results? https://www.postgresql.org/docs/current/tutorial-agg.html — Frank Heikens, May 06 '22 at 19:35

score 0 · Answer 1 · answered May 07 '22 at 09:37

https://www.postgresql.org/docs/current/datatype-numeric.html
please refer to data type (real, double precision) description column: variable-precision, inexact!

The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required.

So use numeric datatype to store info that require exact.

demo

you can make it via materialized view or view. You don't need to create another table quarter, because all the info can be queried through join customer and invice table.

SELECT
    EXTRACT(QUARTER FROM i.last_update_date) AS quarter,
    EXTRACT(year FROM i.last_update_date) AS year,
    c.cust_no,
    round(((i.iva_amount * 100) / net_amount), 2) AS iva_percent,
    i.iva_amount
FROM
    erp.tb_customer c
    JOIN erp.tb_invoice i ON i.cust_no = c. cust_no;

also just use text data type for storing text characters: Any downsides of using data type "text" for storing strings?

Hello Mark. Thannks for your help. I need to insert the rows in the table because it's going to be a regular procedure. I understand the select and from statement can be nested somehow with a INSERT INTO, right? How would it work? I'm not discussing about which approach is better, I'm sure yours is more effective, let's just say in order to learn fundamentals they are asking me to do it this way :) Could you help me please? — Jose Antonio Piedehierro Arias, May 07 '22 at 21:59

PostgreSQL year, quarter calculator

1 Answers1