select para montar fluxo de caixa

[Amparo]

ola amigos

bom dia

BD mariaDB

tenho duas tabelas praticamente idêntica, contas_pagar e contas_receber com os seguinte campos:

dtvc DATA (data de vencimento)
dtpg DATA (data de pagamento)
tipo char(1) onde P=pagar R=receber
total (decimal,14,2)

preciso montar um select para fazer um fluxo de caixa, no layout cfe abaixo:

Código: Selecionar todos

                               Acumulado                   Acumulado            Saldo        Saldo
Data             a Receber     a Receber       a Pagar       a Pagar              Dia    Acumulado
------------------------------------------------------------------------------------------------------------------------
01/01-S b             0,00          0,00     20.393,32     20.393,32       -20.393,32   -20.393,32
02/01-Dom             0,00          0,00          0,00     20.393,32             0,00   -20.393,32
------------------------------------------------------------------------------------------------------------------------
03/01-Seg        44.776,47     44.776,47      9.709,51     30.102,83        35.066,95     14.673,64
04/01-Ter         9.675,12     54.451,58      9.880,64     39.983,47          -205,53     14.468,11
05/01-Qua        29.625,73     84.077,31     11.083,27     51.066,74        18.542,45     33.010,56
...
...
...

as colunas acumulado a receber, acumulado a pagar, saldo do dia e saldo acumulado não existe nas tabelas

tentei valarias formas mais onde cheguei mais perto foi um select assim:

SELECT dtvc, TOTAL, TIPO FROM CONTAS_RECEBER WHERE dtvc BETWEEN '2022-01-01' AND '2022-01-09' AND dtpg IS NULL
UNION
SELECT dtvc, TOTAL, TIPO FROM CONTAS_PAGAR WHERE dtvc BETWEEN '2022-01-01' AND '2022-01-09' AND dtpg IS NULL
ORDER BY dtvc,TIPO;

onde obtenho o seguinte resultado:

2022-01-01 2500.29 P
2022-01-01 17893.45 P
2022-01-03 1350.12 P
2022-01-03 5140.21 P
2022-01-03 1521.55 R
2022-01-03 4250.45 R
2022-01-03 850.12 R
2022-01-04 2500.15 P
2022-01-04 350.87 R
2022-01-04 3278.12 R

e assim por diante, agora precisava que a consulta saísse da forma do exemplo acima, com as colunas acumulado e saldos, alguém teria ideia de como montar esta consulta?

abraços

Amparo

[Amparo]

ola amigos

bom dia

peguei um exemplo e adaptei, mas esta dando erro:

Erro SQL (1054): Unknown column 'EntradaAcum' in 'field list'

SELECT DISTINCT CP.Vencimento, (SELECT If(Sum(total)Is Null,0,Sum(total)) FROM contas_receber AS Tb1 WHERE Tb1.Vencimento=CP.Vencimento) AS Entrada, (SELECT If(Sum(total)Is Null,0,Sum(total)) FROM contas_receber AS Tb1 WHERE Tb1.Vencimento<=CP.Vencimento) AS EntradaAcum, (SELECT If(Sum(total)Is Null,0,Sum(total)) FROM contas_pagar AS Tb1 WHERE Tb1.Vencimento=CP.Vencimento) AS Saida, (SELECT If(Sum(total)Is Null,0,Sum(total)) FROM contas_pagar AS Tb1 WHERE Tb1.Vencimento<=CP.Vencimento) AS SaidaAcum, EntradaAcum-SaidaAcum AS Saldo
FROM (SELECT Vencimento FROM contas_receber
UNION SELECT Vencimento FROM contas_pagar) AS CP
ORDER BY CP.Vencimento;


abraços
Amparo

[JoséQuintas]

Tá parecendo que colocou cada select como sendo um campo, se funcionasse traria só uma linha.

Imagino que deveria fazer um SELECT encima das datas relacionadas com c.receber e c.pagar.

Algo neste estilo

Código: Selecionar todos

SELECT tbdata.data, COALESCE( SUM( receber.valor ), 0 ) AS ENTRADA, COALESCE( SUM( pagar.valor ), 0 ) AS SAIDA
FROM 
   ( SELECT das datas ) AS tbdata
INNER JOIN pagar ON tb.data = pagar.data
INNER JOIN receber ON tb.data = receber.data
GROUP BY data
ORDER BY data

E ainda teria que fazer um select composto das datas pra eliminar datas repetidas.

[alxsts]

Olá!

Para este caso, acho que seria bem indicado o uso de múltiplos CTEs (Common Table Expressions)

Código: Selecionar todos

WITH Debitos AS (
...
),
Creditos AS (
...
),
Acumulados AS (
...
Saldos AS (
...
)
SELECT ... FROM debitos JOIN creditos ...
WHERE ...

[JoséQuintas]

Só se for pras datas, porque o resto é um único SELECT.
Mesmo assim, nem toda versão de SQL tem CTE.
Com certeza CTE é muito bom pra organizar o comando SQL e evitar erros, gostei do recurso logo que vi, mas na prática não pude usar porque não teria o recurso na maioria das bases de dados que uso, pra não dizer em nenhuma.

[alxsts]

Olá!

Só se for pras datas, porque o resto é um único SELECT.

Não vejo esta simplicidade toda que você enxerga. Como vai calcular a coluna "Saldo Acumulado" do relatório, se ela vai variando a cada dia? Se puder mostrar isto... na minha visão isto só poderá ser feito com um CTE recursivo ou uma stored procedure.

na prática não pude usar porque não teria o recurso na maioria das bases de dados que

Esta é a desvantagem de não usar a versão mais atualizada do BD. Aliás, o OP diz que usa MariaDB mas não informa a versão. CTE no MariaDB só a partir da versão 10. Nem sei se ele poderia usar...

[JoséQuintas]

Não vejo esta simplicidade toda que você enxerga. Como vai calcular a coluna "Saldo Acumulado" do relatório, se ela vai variando a cada dia? Se puder mostrar isto... na minha visão isto só poderá ser feito com um CTE recursivo ou uma stored procedure.

Só alterar o SELECT, ou pra não complicar demais, criar tabela temporária com esse resultado.

[Amparo]

OLA AMIGOS

Boa Tarde

com SELECT abaixo

Código: Selecionar todos

SELECT DISTINCT CP.Vencimento, 
(SELECT If(Sum(total)Is Null,0,Sum(total) ) FROM contas_receber AS Tb1 WHERE Tb1.Vencimento=CP.Vencimento) AS A_RECEBER,
(SELECT If(Sum(total)Is Null,0,Sum(total)) FROM contas_pagar AS Tb1 WHERE Tb1.Vencimento=CP.Vencimento)*-1 AS A_PAGAR,
((SELECT If(Sum(total)Is Null,0,Sum(total) ) FROM contas_receber AS Tb1 WHERE Tb1.Vencimento=CP.Vencimento) - 
(SELECT If(Sum(total)Is Null,0,Sum(total)) FROM contas_pagar AS Tb1 WHERE Tb1.Vencimento=CP.Vencimento) ) AS SALDO_DIA
FROM (SELECT Vencimento FROM contas_receber WHERE Vencimento BETWEEN '2022-01-01' AND '2022-01-09'
UNION SELECT Vencimento FROM contas_pagar) AS CP WHERE CP.Vencimento BETWEEN '2022-01-01' AND '2022-01-09'
ORDER BY CP.Vencimento;

consegui chegar a este resultado

Código: Selecionar todos

vencimento  A_RECEBER   A_PAGAR       SALDO_DIA
2022-01-01      0.0000   -20393.3167    -20393.3167
2022-01-03  44776.4683    -9709.5137     35066.9546
2022-01-04   9675.1150    -9880.6409      -205.5259
2022-01-05  29625.7250   -11083.2731     18542.4519
2022-01-06  16596.6800    -8852.5268      7744.1532
2022-01-07  77512.1900   -22409.2224     55102.9676
2022-01-08      0.0000   -20393.3436    -20393.3436

só falta agora a coluna ACUMULADO A RECEBER, ACUMULADO A PAGAR e SALDO ACUMULADO

ABRAÇO

[alxsts]

Olá!

Você pode melhorar a performance... veja:

A linha abaixo executa a função Sum(), que pode envolver milhares de registros. Depois de executada a função, o resultado é comparado com NULL. Se o resultado for diferente de NULL, a função Sum() é reexecutada. Caso contrário, a expressão retorna o valor zero.

Código: Selecionar todos

SELECT If(Sum(total)Is Null,0,Sum(total) ) FROM...

Trocando pela linha abaixo, a Sum() é executada apenas uma vez:

Código: Selecionar todos

SELECT COALESCE( SUM( total ), 0 ) FROM ...

[Amparo]

OLA AMIGOS

boa tarde
Alexandre, interessante! vou testar.

grato!

Amparo

[JoséQuintas]

Talvez isto:

Código: Selecionar todos

SELECT resumo.data, resumo.entrada, resumo.saida, SUM( IF( resumo.data <= ref.data, resumo.entrada, 0 ) - SUM( IF( resumo.data <= ref.data, resumo.saida, 0 )
FROM
( select das datas ) as ref
INNER JOIN
(
SELECT tbdata.data, COALESCE( SUM( receber.valor ), 0 ) AS ENTRADA, COALESCE( SUM( pagar.valor ), 0 ) AS SAIDA
FROM 
   ( SELECT das datas ) AS tbdata
INNER JOIN pagar ON tb.data = pagar.data
INNER JOIN receber ON tb.data = receber.data
GROUP BY data
ORDER BY data
) as resumo
ORDER BY data

A idéia é primeiro fazer o resumo por data, e depois só calcular o acumulado.

Como eu disse antes, talvez o CTE pro SELECT das datas.
Ou, se criar temporário, já usa o próprio temporário.