| Sql |
select replace(d.valore_n, '"','') as codice_ateco, replace(c.valore_n, '"','') as ragione_sociale ,'P.IVA:'||c.partita_iva||'' as partita_iva_desc, prima.operatingRevenues, seconda.profitOrLossForTheYear, terza.totalPayrollAndRelatedCosts, quarta.totalValueOfProduction, prima.partita_iva, substring(seconda.yearEndDate from 2 for 10) AS yearenddate ,
case when z.zona is null then 'ND'
else
replace(z.zona, '"', '') end as zona
from
(select id, valore_n, campo, partita_iva from public.v_company_deaggr where campo='name' group by 1,2,3, 4) c
left join
(select id,valore_n, campo, partita_iva from public.v_company_deaggr where campo='activityCode' group by 1,2,3,4)d on d.partita_iva=c.partita_iva
left join
(select valore_n as zona, vat_number as partita_iva from public.v_settlement_deaggr where campo='zona' group by 1,2) z on c.partita_iva=z.partita_iva
left join
(SELECT a.id, a.partita_iva, a.nome, a.campo, a.valore_n as operatingRevenues, b.valore_n as yearEndDate from
(SELECT rowid1, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='operatingRevenues') a
left join
(SELECT rowid, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='yearEndDate') b on a.rowid1=b.rowid
group by 1,2,3,4,5,6) prima on prima.partita_iva=c.partita_iva
left join
(SELECT a.id, a.partita_iva, a.nome, a.campo, a.valore_n as profitOrLossForTheYear, b.valore_n as yearEndDate from
(SELECT rowid1, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='profitOrLossForTheYear') a
left join
(SELECT rowid, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='yearEndDate') b on a.rowid1=b.rowid
group by 1,2,3,4,5,6) seconda on prima.id= seconda.id and prima.partita_iva=seconda.partita_iva and seconda.yearEndDate= prima.yearEndDate
left join
(SELECT a.id, a.partita_iva, a.nome, a.campo, a.valore_n as totalPayrollAndRelatedCosts, b.valore_n as yearEndDate from
(SELECT rowid1, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='totalPayrollAndRelatedCosts') a
left join
(SELECT rowid, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='yearEndDate') b on a.rowid1=b.rowid
group by 1,2,3,4,5,6) terza on prima.id= terza.id and prima.partita_iva=terza.partita_iva and terza.yearEndDate= prima.yearEndDate
left join
(SELECT a.id, a.partita_iva, a.nome, a.campo, a.valore_n as totalValueOfProduction, b.valore_n as yearEndDate from
(SELECT rowid1, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='totalValueOfProduction') a
left join
(SELECT rowid, id, vat_number as partita_iva, nome, campo, valore_n from v_financial_deaggr a where campo='yearEndDate') b on a.rowid1=b.rowid
group by 1,2,3,4,5,6) quarta on prima.id= quarta.id and prima.partita_iva=quarta.partita_iva and quarta.yearEndDate= prima.yearEndDate
|
