Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create schema erp;
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)
  );
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             numeric  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         numeric NOT NULL,
    iva_amount         numeric NOT NULL,
    tot_amount         numeric 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_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no)
 
  );
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);

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

table erp.tb_customer limit 2;


cust_no
cust_name
cust_cif
last_updated_by
last_update_date


C0001
PIENSOS MARTIN
A12345678
SYSTEM
2022-05-07

C0002
AGRICULTURA VIVES
A66666666
SYSTEM
2022-05-07


table erp.tb_invoice limit 2;


co_code
invoice_id
invoice_no
cust_no
site_id
payed
net_amount
iva_amount
tot_amount
last_updated_by
last_update_date


AB1
200
F0000C000400200
C0004
6
Y
25984.36
2802.34
28786.7
SYSTEM
2022-05-07

AB1
201
F0000C000400201
C0004
6
N
5134.2
490.09
5624.29
SYSTEM
2022-05-07


table erp.tb_quarter
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
;


quarter
year
cust_no
iva_percent
iva_amount


2
2022
C0004
10.78
2802.34

2
2022
C0004
9.55
490.09

2
2022
C0004
13.84
3120.85

2
2022
C0004
11.69
3712.75

2
2022
C0004
10.74
1811.27

2
2022
C0004
10.03
1582.37

2
2022
C0004
14.17
3045.65

2
2022
C0004
9.31
2000.61

2
2022
C0004
14.15
2365.57

2
2022
C0004
11.78
2150.09

2
2022
C0004
13.48
2595.01

2
2022
C0004
18.16
1491.36

2
2022
C0005
21.00
2560.62

2
2022
C0005
21.00
533.42


CREATE MATERIALIZED VIEW mymatview AS
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
;
14 rows affected

select * from mymatview;


quarter
year
cust_no
iva_percent
iva_amount


2
2022
C0004
10.78
2802.34

2
2022
C0004
9.55
490.09

2
2022
C0004
13.84
3120.85

2
2022
C0004
11.69
3712.75

2
2022
C0004
10.74
1811.27

2
2022
C0004
10.03
1582.37

2
2022
C0004
14.17
3045.65

2
2022
C0004
9.31
2000.61

2
2022
C0004
14.15
2365.57

2
2022
C0004
11.78
2150.09

2
2022
C0004
13.48
2595.01

2
2022
C0004
18.16
1491.36

2
2022
C0005
21.00
2560.62

2
2022
C0005
21.00
533.42
cust_no	cust_name	cust_cif	last_updated_by	last_update_date
C0001	PIENSOS MARTIN	A12345678	SYSTEM	2022-05-07
C0002	AGRICULTURA VIVES	A66666666	SYSTEM	2022-05-07
co_code	invoice_id	invoice_no	cust_no	site_id	payed	net_amount	iva_amount	tot_amount	last_updated_by	last_update_date
AB1	200	F0000C000400200	C0004	6	Y	25984.36	2802.34	28786.7	SYSTEM	2022-05-07
AB1	201	F0000C000400201	C0004	6	N	5134.2	490.09	5624.29	SYSTEM	2022-05-07
quarter	year	cust_no	iva_percent	iva_amount
2	2022	C0004	10.78	2802.34
2	2022	C0004	9.55	490.09
2	2022	C0004	13.84	3120.85
2	2022	C0004	11.69	3712.75
2	2022	C0004	10.74	1811.27
2	2022	C0004	10.03	1582.37
2	2022	C0004	14.17	3045.65
2	2022	C0004	9.31	2000.61
2	2022	C0004	14.15	2365.57
2	2022	C0004	11.78	2150.09
2	2022	C0004	13.48	2595.01
2	2022	C0004	18.16	1491.36
2	2022	C0005	21.00	2560.62
2	2022	C0005	21.00	533.42