Fórum Separar campos quando utilizar UNION no FIREBIRD #618304

29/06/2022

0

SQL Banco de Dados SELECT IBExpert Firebird

Estou utilizando o FireBird 2.5 e preciso de ajuda, eu tenho que construir uma consulta SQL para retornar o total de "Contas a Receber" e o total de "Contas a Pagar" em aberto do banco de dados, totalizar por mês independente do período exibindo
saldo por mês e totais ao final do resultado. Tentei utilizar o "Union" e ele me deu os dados certos. porém não estou conseguindo separar as colunas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select
        case extract (month from cr.data_emissao)
          when 1 then ''''''''Janeiro''''''''
          when 2 then ''''''''Fevereiro''''''''
          when 3 then ''''''''Março''''''''
          when 4 then ''''''''Abril''''''''
          when 5 then ''''''''Maio''''''''
          when 6 then ''''''''Junho''''''''
          when 7 then ''''''''Julho''''''''
          when 8 then ''''''''Agosto''''''''
          when 9 then ''''''''Setembro''''''''
          when 10 then ''''''''Outubro''''''''
          when 11 then ''''''''Novembro''''''''
          when 12 then ''''''''Dezembro''''''''
        end "Mês", sum(cr.valor_aberto)"Total a Receber"
 
    from contas_receber cr
    where cr.status = ''''''''A''''''''
    group by extract (month from cr.data_emissao)
 
 
union
 
select
        case extract (month from cp.data_emissao)
          when 1 then ''''''''Janeiro''''''''
          when 2 then ''''''''Fevereiro''''''''
          when 3 then ''''''''Março''''''''
          when 4 then ''''''''Abril''''''''
          when 5 then ''''''''Maio''''''''
          when 6 then ''''''''Junho''''''''
          when 7 then ''''''''Julho''''''''
          when 8 then ''''''''Agosto''''''''
          when 9 then ''''''''Setembro''''''''
          when 10 then ''''''''Outubro''''''''
          when 11 then ''''''''Novembro''''''''
          when 12 then ''''''''Dezembro''''''''
        end, sum(cp.valor_aberto)"Total a Pagar"
 
    from contas_pagar cp
    where cp.status = ''''''''A''''''''
    group by extract (month from cp.data_emissao)


ai ele está me devolvendo da seguinte maneira:

Mês Total a Receber

Abril 9.121,63
Agosto 12.975,03
Dezembro 13.521,29
Dezembro 47112,38
Fevereiro 143646,86
Fevereiro 198236,3
Janeiro 69601,48
Janeiro 116815,26
Julho 11001,71
Junho 11687,77
Maio 8548,66
Março 98584,61
Março 149984,69
Novembro 12158,52
Novembro 29484,31
Outubro 73015,03
Setembro 2987,66
Setembro 11862
Renato Akira

Renato Akira

Responder

Posts

29/06/2022

Emerson Nascimento

veja se isto atende
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select
    'RECEBER' Tipo,
    sum(case when extract(month from cr.data_emissao) = 1 then cr.valor_aberto else 0 end) Janeiro,
    sum(case when extract(month from cr.data_emissao) = 2 then cr.valor_aberto else 0 end) Fevereiro,
    sum(case when extract(month from cr.data_emissao) = 3 then cr.valor_aberto else 0 end) Marco,
    sum(case when extract(month from cr.data_emissao) = 4 then cr.valor_aberto else 0 end) Abril,
    sum(case when extract(month from cr.data_emissao) = 5 then cr.valor_aberto else 0 end) Maio,
    sum(case when extract(month from cr.data_emissao) = 6 then cr.valor_aberto else 0 end) Junho,
    sum(case when extract(month from cr.data_emissao) = 7 then cr.valor_aberto else 0 end) Julho,
    sum(case when extract(month from cr.data_emissao) = 8 then cr.valor_aberto else 0 end) Agosto,
    sum(case when extract(month from cr.data_emissao) = 9 then cr.valor_aberto else 0 end) Setembro,
    sum(case when extract(month from cr.data_emissao) = 10 then cr.valor_aberto else 0 end) Outubro,
    sum(case when extract(month from cr.data_emissao) = 11 then cr.valor_aberto else 0 end) Novembro,
    sum(case when extract(month from cr.data_emissao) = 12 then cr.valor_aberto else 0 end) Dezembro
from
    contas_receber cr
where
    cr.status = 'A'
  
union
  
select
    'PAGAR' Tipo,
    sum(case when extract(month from cp.data_emissao) = 1 then cp.valor_aberto else 0 end) Janeiro,
    sum(case when extract(month from cp.data_emissao) = 2 then cp.valor_aberto else 0 end) Fevereiro,
    sum(case when extract(month from cp.data_emissao) = 3 then cp.valor_aberto else 0 end) Marco,
    sum(case when extract(month from cp.data_emissao) = 4 then cp.valor_aberto else 0 end) Abril,
    sum(case when extract(month from cp.data_emissao) = 5 then cp.valor_aberto else 0 end) Maio,
    sum(case when extract(month from cp.data_emissao) = 6 then cp.valor_aberto else 0 end) Junho,
    sum(case when extract(month from cp.data_emissao) = 7 then cp.valor_aberto else 0 end) Julho,
    sum(case when extract(month from cp.data_emissao) = 8 then cp.valor_aberto else 0 end) Agosto,
    sum(case when extract(month from cp.data_emissao) = 9 then cp.valor_aberto else 0 end) Setembro,
    sum(case when extract(month from cp.data_emissao) = 10 then cp.valor_aberto else 0 end) Outubro,
    sum(case when extract(month from cp.data_emissao) = 11 then cp.valor_aberto else 0 end) Novembro,
    sum(case when extract(month from cp.data_emissao) = 12 then cp.valor_aberto else 0 end) Dezembro
from
    contas_pagar cp
where
    cp.status = 'A'

Responder

Gostei + 0

29/06/2022

Renato Akira


Então, isso é quase o resultado que tô precisando. Tem como fazer aparecer 3 colunas?
Meses | Valor a Receber | Valor a Pagar
Responder

Gostei + 0

29/06/2022

Renato Akira


Então, isso é quase o resultado que tô precisando. Tem como fazer aparecer 3 colunas?
Meses | Valor a Receber | Valor a Pagar
Porque eu precisarei utilizar o execute blocks para retornar o resultado da seguinte forma:
https://prnt.sc/VsoCvnGFSkl1
Responder

Gostei + 0

30/06/2022

Emerson Nascimento

em 3 colunas? claro!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
select
    mes "Mês",
    sum(receber) "Total a Receber",
    sum(pagar) "Total a Pagar"
from (
    select
        case extract (month from cr.data_emissao)
            when 1 then 'Janeiro'
            when 2 then 'Fevereiro'
            when 3 then 'Março'
            when 4 then 'Abril'
            when 5 then 'Maio'
            when 6 then 'Junho'
            when 7 then 'Julho'
            when 8 then 'Agosto'
            when 9 then 'Setembro'
            when 10 then 'Outubro'
            when 11 then 'Novembro'
            when 12 then 'Dezembro'
        end mes,
        sum(cr.valor_aberto) receber, -- aqui acumula o valor a receber
        0.0 pagar -- valor a pagar deve ser zero porque aqui usamos a tabela de contas a receber
    from
        contas_receber cr
    where
        cr.status = 'A'
    group by
        extract (month from cr.data_emissao)
 
    union
 
    select
        case extract (month from cp.data_emissao)
            when 1 then 'Janeiro'
            when 2 then 'Fevereiro'
            when 3 then 'Março'
            when 4 then 'Abril'
            when 5 then 'Maio'
            when 6 then 'Junho'
            when 7 then 'Julho'
            when 8 then 'Agosto'
            when 9 then 'Setembro'
            when 10 then 'Outubro'
            when 11 then 'Novembro'
            when 12 then 'Dezembro'
        end mes,
        0.0 receber -- valor a receber deve ser zero porque aqui usamos a tabela de contas a pagar
        sum(cp.valor_aberto) pagar -- aqui acumula o valor a pagar
    from
        contas_pagar cp
    where
        cp.status = 'A'
    group by
        extract (month from cp.data_emissao)
) t
group by
    t.mes
Responder

Gostei + 0

30/06/2022

Renato Akira

Entendi como ficaria nesse caso, mas como ficaria se eu tivesse que trabalhar com execute blocks e colocar a ordem dos meses em ordem cronológica?
Para alcançar o seguinte resultado: https://prnt.sc/9Up1rQI2VdyD

Tentei fazer da seguinte forma, mas ainda estou travado com o problema das colunas.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
execute block
 
returns ("Mês" varchar(15), r_valorCR numeric(15,2),r_valorCP numeric(15,2), r_saldo numeric (15,2))
 
as
declare variable vRecebe numeric (15,2);
declare variable vRecebido numeric (15,2);
declare variable vPaga numeric(15,2);
declare variable meses varchar(15);
begin
    for
        select extract (month from cr.data_emissao),sum(cr.valor_aberto)vRecebe
 
        from contas_receber cr
        where cr.status = 'A'
        group by extract (month from cr.data_emissao)
 
union all
 
        select extract (month from cp.data_emissao),sum(cp.valor_aberto)vPaga
 
        from contas_pagar cp
        where cp.status = 'A'
        group by extract (month from cp.data_emissao)
        into "Mês", r_valorCR
 
    do
    begin
      if ("Mês" = '1') then
          "Mês" = 'Janeiro';
   
      if ("Mês" = '2') then
          "Mês" = 'Fevereiro';
 
      if ("Mês" = '3') then
          "Mês" = 'Março';
 
      if ("Mês" = '4') then
          "Mês" = 'Abril';
 
      if ("Mês" = '5') then
          "Mês" = 'Maio';
 
      if ("Mês" = '6') then
          "Mês" = 'Junho';
 
      if ("Mês" = '7') then
          "Mês" = 'Julho';
 
      if ("Mês" = '8') then
          "Mês" = 'Agosto';
 
      if ("Mês" = '9') then
          "Mês" = 'Setembro';
 
      if ("Mês" = '10') then
          "Mês" = 'Outubro';
 
      if ("Mês" = '11') then
          "Mês" = 'Novembro';
 
      if ("Mês" = '12') then
          "Mês" = 'Dezembro';
    suspend;
    end
 
  end


Ai na consulta SQL ta me retornando dessa maneira: https://prnt.sc/Q2Tv28crThFT

Ler Mais...

Responder

Gostei + 0

30/06/2022

Renato Akira


E preciso evitar o uso de subselect, utilizar apenas union all
Responder

Gostei + 0

30/06/2022

Emerson Nascimento

evita-se o uso de subselects quando há muitos registros envolvidos.
no teu caso serão no máximo 24 registros (12 registros em cada seleção)
pra ordenar basta você incluir o mês propriamente dito:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
select
    mes "Mês",
    sum(receber) "Total a Receber",
    sum(pagar) "Total a Pagar"
from (
    select
        extract (month from cr.data_emissao) ordem,
        case extract (month from cr.data_emissao)
            when 1 then 'Janeiro'
            when 2 then 'Fevereiro'
            when 3 then 'Março'
            when 4 then 'Abril'
            when 5 then 'Maio'
            when 6 then 'Junho'
            when 7 then 'Julho'
            when 8 then 'Agosto'
            when 9 then 'Setembro'
            when 10 then 'Outubro'
            when 11 then 'Novembro'
            when 12 then 'Dezembro'
        end mes,
        sum(cr.valor_aberto) receber, -- aqui acumula o valor a receber
        0.0 pagar -- valor a pagar deve ser zero porque aqui usamos a tabela de contas a receber
    from
        contas_receber cr
    where
        cr.status = 'A'
    group by
        extract (month from cr.data_emissao)
  
    union
  
    select
        extract (month from cp.data_emissao) ordem,
        case extract (month from cp.data_emissao)
            when 1 then 'Janeiro'
            when 2 then 'Fevereiro'
            when 3 then 'Março'
            when 4 then 'Abril'
            when 5 then 'Maio'
            when 6 then 'Junho'
            when 7 then 'Julho'
            when 8 then 'Agosto'
            when 9 then 'Setembro'
            when 10 then 'Outubro'
            when 11 then 'Novembro'
            when 12 then 'Dezembro'
        end mes,
        0.0 receber -- valor a receber deve ser zero porque aqui usamos a tabela de contas a pagar
        sum(cp.valor_aberto) pagar -- aqui acumula o valor a pagar
    from
        contas_pagar cp
    where
        cp.status = 'A'
    group by
        extract (month from cp.data_emissao)
) t
group by
    t.ordem, t.mes
order by
    t.ordem

Responder

Gostei + 0

30/06/2022

Renato Akira

mas caso fosse necessário utilizar apenas o Union se o subselect, teria como representar ele no execute blocks ? Atualmente estou desenvolvendo dessa forma

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
execute block
 
returns ("Mês" varchar(15), r_valorCR numeric(15,2),r_valorCP numeric(15,2), r_saldo numeric (15,2), r_tipo varchar(10))
 
as
declare variable vRecebe numeric (15,2);
declare variable vRecebido numeric (15,2);
declare variable vPaga numeric(15,2);
declare variable meses varchar(15);
begin
    for
        select extract (month from cr.data_emissao), sum(cr.valor_aberto)vRecebe, 'CR'"TIPO"
 
        from contas_receber cr
        where cr.status = 'A'
        group by extract (month from cr.data_emissao)
 
union all
 
        select extract (month from cp.data_emissao),sum(cp.valor_aberto)vPaga, 'CP'"TIPO"
 
        from contas_pagar cp
        where cp.status = 'A'
        group by extract (month from cp.data_emissao)
        order by 1
        into "Mês", r_valorCR, r_tipo
 
    do
    begin
 
      if ("Mês" = '1') then
          "Mês" = 'Janeiro';
   
      if ("Mês" = '2') then
          "Mês" = 'Fevereiro';
 
      if ("Mês" = '3') then
          "Mês" = 'Março';
 
      if ("Mês" = '4') then
          "Mês" = 'Abril';
 
      if ("Mês" = '5') then
          "Mês" = 'Maio';
 
      if ("Mês" = '6') then
          "Mês" = 'Junho';
 
      if ("Mês" = '7') then
          "Mês" = 'Julho';
 
      if ("Mês" = '8') then
          "Mês" = 'Agosto';
 
      if ("Mês" = '9') then
          "Mês" = 'Setembro';
 
      if ("Mês" = '10') then
          "Mês" = 'Outubro';
 
      if ("Mês" = '11') then
          "Mês" = 'Novembro';
 
      if ("Mês" = '12') then
          "Mês" = 'Dezembro';
 
    suspend;
 
    end
end

Ler Mais...



A consulta está retornando dessa forma: https://prnt.sc/Ncsy0DqXyxIs
Responder

Gostei + 0

30/06/2022

Renato Akira

Outra dúvida, como eu faria para criar uma nova linha abaixo de tudo com nome "Totais" no lugar do nome do Mês e nos campos seguintes, a somatória de todos os meses e depois a diferença?
Responder

Gostei + 0

30/06/2022

Emerson Nascimento

veja se assim funciona
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
execute block
  
returns ("Mês" varchar(15), r_valorCR numeric(15,2), r_valorCP numeric(15,2), r_saldo numeric (15,2))
as
declare numeromes int = 1;
declare total_cr numeric(15,2) = 0;
declare total_cp numeric(15,2) = 0;
declare total_sld numeric(15,2) = 0;
begin
 
    while (numeromes <= 12)  do
    begin
        "Mês" = case numeromes
                    when 01 then 'Janeiro'
                    when 02 then 'Fevereiro'
                    when 03 then 'Março'
                    when 04 then 'Abril'
                    when 05 then 'Maio'
                    when 06 then 'Junho'
                    when 07 then 'Julho'
                    when 08 then 'Agosto'
                    when 09 then 'Setembro'
                    when 10 then 'Outubro'
                    when 11 then 'Novembro'
                    when 12 then 'Dezembro'
                end;
 
        -- estude a possibilidade de criar um campo pra guardar o mes
        -- e criar um indice por esse campo.
        -- assim a condicao seria "cr.status = 'A' and mes = :numeromes"
        -- desta forma de pesquisa seria mais rapida por que o indice
        -- seria usado na pesquisa (com o extract() nao ha uso de indice)
        select coalesce(sum(cr.valor_aberto),0)
        from contas_receber cr
        where cr.status = 'A' and extract(month from cr.data_emissao) = :numeromes
        into r_valorCR;
  
        -- estude a possibilidade de criar um campo pra guardar o mes
        -- e criar um indice por esse campo.
        -- assim a condicao seria "cp.status = 'A' and mes = :numeromes"
        -- desta forma de pesquisa seria muito mais rapida por que o indice
        -- seria usado na pesquisa (com o extract() nao ha uso de indice)
        select colaesce(sum(cp.valor_aberto),0)
        from contas_pagar cp
        where cp.status = 'A' and extract(month from cp.data_emissao) = :numeromes
        into r_valorCP;
 
        if (r_valorCR is null) then
            r_valorCR = 0;
 
        if (r_valorCP is null) then
            r_valorCP = 0;
  
        r_saldo := (r_valorCR - r_valorCP);
 
        -- totalizadores
        total_cr = total_cr + r_valorCR;
        total_cp = total_cp + r_valorCP;
        total_sld = total_sld + r_saldo;
 
        numeromes = numeromes + 1;
 
        suspend;
  
    end
 
    -- apresenta os totais
    "Mês" = 'Totais';
    r_valorCR = total_cr;
    r_valorCP = total_cp;
    r_saldo = total_sld;
 
    suspend;
 
end
Responder

Gostei + 0

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar