1
The image below shows 3 tables, united by Excel.
The first is called customer, the second representative and the third is called channel.
But I would like to join them in a single select in sql, and I have no idea how to do that. Could someone show a basic example of at least the first select of each of the tables so I can replicate in the others?
SELECT FROM FIRST TABLE
select
(select 1 from dual) as ordem,
(select '' from dual) as DESCRICAO,
(select 2017 from dual) as CLIENTE,
(select 2018 from dual) as CLIENTE,
(select 2019 from dual) as CLIENTE,
(select 2020 from dual) as CLIENTE
FROM DUAL
UNION
select
(select 2 from dual) as ordem,
(select 'VENDAS ACUMULADA' from dual) as DESCRICAO,
sum((case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot else 0 end)) AS "2017",
sum((case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot else 0 end)) AS "2018",
sum((case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot else 0 end)) AS "2019",
sum((case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot else 0 end)) AS "2020"
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1
union
select
(select 3 from dual) as ordem,
(select 'DESC MEDIO %' from dual) as DESCRICAO,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2) AS DESC_2017,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2) AS DESC_2018,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2) AS DESC_2019,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2) AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1
union
select
(select 4 from dual) as ordem,
(select 'DESC ADICIONAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,3551,1) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,3551,1) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,3551,1) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,3551,1) AS DESC_2020
from dual
union
select
(select 5 from dual) as ordem,
(select 'DESC MEDIO TOTAL %' from dual) as DESCRICAO,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2017,3551,1) AS DESC_2017,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2018,3551,1)AS DESC_2018,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2019,3551,1) AS DESC_2019,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2020,3551,1)
AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1
union
SELECT
(select 6 from dual) as ordem,
(select 'INVESTIMENTO R$' from dual) descricao,
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2017 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2017",
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2018 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2018",
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2019 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2019",
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2020 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2020"
FROM AD_INVEST X
WHERE
X.CODVEND = 10304 AND
X.CODPARCMATRIZ = 3551
GROUP BY 1
union
SELECT
(select 7 from dual) as ordem,
(select 'INVESTIMENTO %' from dual) descricao,
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2017 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2017 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2017",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2018 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2018 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2018",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2019 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2019 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2019",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2020 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2020 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2020"
FROM AD_INVEST X
WHERE X.CODVEND = 10304
AND X.CODPARCMATRIZ = 3551
GROUP BY 1
union
SELECT
(select 8 from dual) as ordem,
(select 'MARGEM $' from dual) descricao,
FC_PERS_BUSCA_MGVLR(2017, 3551, 1) as "2017",
FC_PERS_BUSCA_MGVLR(2018, 3551, 1) as "2018",
FC_PERS_BUSCA_MGVLR(2019, 3551, 1) as "2019",
FC_PERS_BUSCA_MGVLR(2020, 3551, 1) as "2020"
FROM DUAL
union
select
(select 9 from dual) as ordem,
(select 'MARGEM %' from dual) as DESCRICAO,
ROUND(FC_PERS_BUSCA_MGVLR(2017, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot else 0 end))*100,2) AS "2017",
ROUND(FC_PERS_BUSCA_MGVLR(2018, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot else 0 end))*100,2) AS "2018",
ROUND(FC_PERS_BUSCA_MGVLR(2019, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot else 0 end))*100,2) AS "2019",
ROUND(FC_PERS_BUSCA_MGVLR(2020, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot else 0 end))*100,2) AS "2020"
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1
SELECT FROM THE SECOND TABLE
select
(select 1 from dual) as ordem,
(select '' from dual) as DESCRICAO,
(select 2017 from dual) as REPRESENTANTE,
(select 2018 from dual) as REPRESENTANTE,
(select 2019 from dual) as REPRESENTANTE,
(select 2020 from dual) as REPRESENTANTE
FROM DUAL
union
SELECT
(select 2 from dual) as ordem,
(select 'VENDAS ACUMULADA' from dual) as DESCRICAO,
sum((case when to_char(inv.dtentsai, 'YYYY') = 2017 then inv.vendas else 0 end)) as "2017",
sum((case when to_char(inv.dtentsai, 'YYYY') = 2018 then inv.vendas else 0 end)) as "2018",
sum((case when to_char(inv.dtentsai, 'YYYY') = 2019 then inv.vendas else 0 end)) as "2019",
sum((case when to_char(inv.dtentsai, 'YYYY') = 2020 then inv.vendas else 0 end)) as "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
group by 1
union
select
(select 3 from dual) as ordem,
(select 'DESC MEDIO %' from dual) as DESCRICAO,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2) AS DESC_2017,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2) AS DESC_2018,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2) AS DESC_2019,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2) AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codvend = 10304
group by 1
UNION
select
(select 4 from dual) as ordem,
(select 'DESC ADICIONAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,10304,2) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,10304,2) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,10304,2) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,10304,2) AS DESC_2020
from dual
union
select
(select 5 from dual) as ordem,
(select 'DESC MEDIO TOTAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2) AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codvend = 10304
group by 1
union
SELECT
(select 6 from dual) as ordem,
(select 'INVESTIMENTO R$' from dual) descricao,
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2017 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2017",
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2018 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2018",
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2019 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2019",
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2020 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
GROUP BY 1
UNION
SELECT
(select 7 from dual) as ordem,
(select 'INVESTIMENTO %' from dual) descricao,
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2017 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2017 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2017",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2018 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2018 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2018",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2019 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2019 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2019",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2020 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2020 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
GROUP BY 1
union
SELECT
(select 8 from dual) as ordem,
(select 'MARGEM $' from dual) descricao,
FC_PERS_BUSCA_MGVLR (2017, 10304, 2) as "2017",
FC_PERS_BUSCA_MGVLR (2018, 10304, 2) as "2018",
FC_PERS_BUSCA_MGVLR (2019, 10304, 2) as "2019",
FC_PERS_BUSCA_MGVLR (2020, 10304, 2) as "2020"
FROM DUAL
union
SELECT
(select 9 from dual) as ordem,
(select 'MARGEM %' from dual) as DESCRICAO,
ROUND(FC_PERS_BUSCA_MGVLR (2017, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2017 then inv.vendas else 0 end))*100,2) as "2017",
ROUND(FC_PERS_BUSCA_MGVLR (2018, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2018 then inv.vendas else 0 end))*100,2) as "2018",
ROUND(FC_PERS_BUSCA_MGVLR (2019, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2019 then inv.vendas else 0 end))*100,2) as "2019",
ROUND(FC_PERS_BUSCA_MGVLR (2020, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2020 then inv.vendas else 0 end))*100,2) as "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
group by 1
SELECT FROM THE THIRD TABLE
select
(select 1 from dual) as ordem,
(select '' from dual) as DESCRICAO,
(select 2017 from dual) as CANAL,
(select 2018 from dual) as CANAL,
(select 2019 from dual) as CANAL,
(select 2020 from dual) as CANAL
FROM DUAL
UNION
select
(select 2 from dual) as ordem,
(select 'VENDAS ACUMULADA' from dual) as DESCRICAO,
sum((case when to_char(y.dtentsai,'YYYY')=2017 then y.vendas else 0 end)) as v2017,
sum((case when to_char(y.dtentsai,'YYYY')=2018 then y.vendas else 0 end)) as v2018,
sum((case when to_char(y.dtentsai,'YYYY')=2019 then y.vendas else 0 end)) as v2019,
sum((case when to_char(y.dtentsai,'YYYY')=2020 then y.vendas else 0 end)) as v2020
from ad_invest y
where y.codvend = 10304
and y.codtipparc = 10101
group by 1
union
SELECT
(select 3 from dual) as ordem,
(select 'DESC MEDIO %' from dual) as DESCRICAO,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.prtab end),1)))*100,2) d2017,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.prtab end),1)))*100,2) d2018,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.prtab end),1)))*100,2) d2019,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.prtab end),1)))*100,2) d2020
FROM AD_DADOSMRG_EX X
WHERE
X.TIPMOV IN ('V','D') AND X.GRUPO IN ('VENDAS') AND
X.CODVEND = 10304 AND
X.CODTIPPARC = 10101
group by 1
UNION
select
(select 4 from dual) as ordem,
(select 'DESC ADICIONAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,3551,3) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,3551,3) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,3551,3) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,3551,3) AS DESC_2020
from dual
union
SELECT
(select 5 from dual) as ordem,
(select 'DESC MEDIO TOTAL %' from dual) as DESCRICAO,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2017,3551,3) d2017,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2018,3551,3) d2018,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2019,3551,3) d2019,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2020,3551,3) d2020
FROM AD_DADOSMRG_EX X
WHERE
X.TIPMOV IN ('V','D') AND X.GRUPO IN ('VENDAS') AND
X.CODVEND = 10304 AND
X.CODTIPPARC = 10101
group by 1
union
SELECT
(select 6 from dual) as ordem,
(select 'INVESTIMENTO R$' from dual) descricao,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2017 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2017,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2018 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2018,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2019 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2019,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2020 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2020
FROM
AD_INVEST INV
WHERE
inv.codvend=10304 and
inv.codtipparc = 10101
GROUP BY 1
union
select
(select 7 from dual) as ordem,
(select 'INVESTIMENTO %' from dual) as DESCRICAO,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2017 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2017 then inv.vendas end)),1)*100,2) as v2017,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2018 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2018 then inv.vendas end)),1)*100,2) as v2018,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2019 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2019 then inv.vendas end)),1)*100,2) as v2019,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2020 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2020 then inv.vendas end)),1)*100,2) as v2020
from ad_invest inv
where inv.codvend = 10304
and inv.codtipparc = 10101
group by 1
union
SELECT
(select 8 from dual) as ordem,
(select 'MARGEM R$' from dual) descricao,
FC_PERS_BUSCA_MGVLR(2017, 3551, 3) as "2017",
FC_PERS_BUSCA_MGVLR(2018, 3551, 3) as "2018",
FC_PERS_BUSCA_MGVLR(2019, 3551, 3) as "2019",
FC_PERS_BUSCA_MGVLR(2020, 3551, 3) as "2020"
FROM DUAL
union
select
(select 9 from dual) as ordem,
(select 'MARGEM %' from dual) as DESCRICAO,
round(FC_PERS_BUSCA_MGVLR(2017, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2017 then inv.vendas end)),1)*100,2) as v2017,
round(FC_PERS_BUSCA_MGVLR(2018, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2018 then inv.vendas end)),1)*100,2) as v2018,
round(FC_PERS_BUSCA_MGVLR(2019, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2019 then inv.vendas end)),1)*100,2) as v2019,
round(FC_PERS_BUSCA_MGVLR(2020, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2020 then inv.vendas end)),1)*100,2) as v2020
from ad_invest inv
where inv.codvend = 10304
and inv.codtipparc = 10101
group by 1
Making Union of 3 is not good ?
– Isac
That’s the question, how to do?
– Rodrigo Macaúbas
How do you plan to implement this ? some front-end , report generator ? The cheeses may have confused me but I think it could be simpler , the basic question is "pivotear" per year and type (cleinte etc) but the solution depends a little on how it will be implemented.
– Motta
It is because this report was done through an Excel, and then now it will be generated directly on the system and then exported to Excel. So I had to put abstract columns there so that they made sense to "print"
– Rodrigo Macaúbas
"directly generated in the system" is half vacant , but a temporary table could already be loaded in the requested format, with Oracle searching for WITH and GTT https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:524736300346304074
– Motta