Mysql extract from INPUT and OUTPUT tables

Question

I’m a beginner in Mysql and PHP, I’m building a financial management application for a charity, (volunteer work), and I can’t solve this problem:

Performing a Mysql query in the IN and OUT tables that returns them as a bank statement.

From now on my gratitude for your help. God Bless you!!

Follow the tables and expected result:

01 - ENTRY TABLE

CREATE TABLE IF NOT EXISTS `entradas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` date DEFAULT NULL,
  `mes_referencia` varchar(50) DEFAULT NULL,
  `conta` varchar(255) DEFAULT NULL,
  `doador` varchar(255) DEFAULT NULL,
  `valor_entrada` decimal(32,2) DEFAULT NULL,
  `n_documento` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2465 DEFAULT CHARSET=latin1;**

02 - EXIT TABLE

CREATE TABLE IF NOT EXISTS `saidas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` date DEFAULT NULL,
  `mes_referencia` varchar(50) DEFAULT NULL,
  `valor_saida` decimal(10,2) DEFAULT NULL,
  `conta` varchar(100) DEFAULT NULL,
  `favorecido` varchar(100) DEFAULT NULL,
  `cpf` varchar(30) DEFAULT NULL,
  `categoria` varchar(100) DEFAULT NULL,
  `numero_doc` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1079 DEFAULT CHARSET=latin1;

03 - RESULTS OF MY DREAMS (kkkk)

Answer 1

There are some problems in this model, it may be that it’s just a conceptual exercise, it wouldn’t use it in a real problem. Surely you will have problems with retroactive movements or chargebacks.

When you separate it into two tables and the expected result will be just one, you add unnecessary complexity, needing to use UNION in the syntax, and this worsens even more when you need to access the same data several times, as is also necessary to calculate balances.

Note that the structure of the records is the same, yet placed different names in the [donor/favored] columns, everything is about a person relative to the movement. This also happens with the value column, is Placed [input value / output value_value] and it is the same data, only value; Still has the use of the +- signal to indicate if it is an input or output, which also facilitates, if the values are flagged, just add all.

Using the presented structure, I made the following Query to extract the desired data:

select 
max(data) as data, 
null as documento, 
'Saldo Anterior' as conta,
null as pessoa,
0 as entrada,
0 as saida,
sum(valor) as saldo
from
(select data, valor from entradas where data < '2020-01-01'
union all
select data, valor from saidas where data < '2020-01-01') x

union all

select 
data, documento, conta,pessoa,entrada,saidas, entradas_anteriores - saidas_anteriores + entrada - saidas as saldo
from (
  select 
    id, data, documento, conta, pessoa, valor as entrada, 0     as saidas, (select coalesce(sum(x.valor),0) from entradas x where x.data < e.data) as entradas_anteriores, (select coalesce(sum(x.valor),0) from saidas   x where x.data < e.data) as saidas_anteriores  from entradas e where e.data >= '2020-01-01'

     union all
  select 
    id, data, documento, conta, pessoa, 0     as entrada, valor as saidas, (select coalesce(sum(x.valor),0) from entradas x where x.data < s.data) as entradas_anteriores, (select coalesce(sum(x.valor),0) from saidas   x where x.data < s.data) as saidas_anteriores  from saidas   s where s.data >= '2020-01-01'
) m

order by data;

You can see running on Sqlfiddle

Upshot:

Now, using the following structure:

CREATE TABLE IF NOT EXISTS `movimentos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` date DEFAULT NULL,
  `conta` varchar(255) DEFAULT NULL,
  `valor` decimal(32,2) DEFAULT NULL,
  `pessoa` varchar(255) DEFAULT NULL,
  `documento` varchar(300) DEFAULT NULL,
  `saldo_anterior` decimal(32,2) DEFAULT NULL,
  `saldo_atual` decimal(32,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2465 DEFAULT CHARSET=latin1;

With the following query I can have the same result:

select 
x.data,
null as documento,
'Saldo Anterior' as conta,
null as pessoa,
0 as valor,
x.saldo_atual
from movimentos x
where x.data < '2020-01-01'

union all

select 
m.data,
m.documento,
m.conta,
m.pessoa,
m.valor,
m.saldo_atual
from movimentos m
where m.data >= '2020-01-01'

Simpler not!?

Follows the Sqlfiddle

Upshot:

Remarks:

I would not put to display the previous balance as a result row, possibly it would be displayed at another location on the screen, as well as the total sum.

In this example there is still problem for the case of retroactive movement.