Mysql complex banking type statement how to solve

Asked

Viewed 780 times

3

I have a VIEW table that I need to extract an extract from it, already managed with help right here from the stackoverflow progress to an extract.

The problem is that now I need to filter this extract a little more, separating it by a certain ID, but when using the WHERE id=emp the extract result is no longer the same.

follow the demo link: sqlfiddle 

    CREATE TABLE IF NOT EXISTS `lc_movimento` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pago` int(11) DEFAULT NULL,
  `dia` int(11) DEFAULT NULL,
  `mes` int(11) DEFAULT NULL,
  `ano` int(11) DEFAULT NULL,
  `cat` int(11) DEFAULT NULL,
  `idemp` int(11) DEFAULT NULL,
  `usu_login` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  `descricao` longtext COLLATE latin1_general_ci,
  `obs` longtext COLLATE latin1_general_ci,
  `debito` double DEFAULT NULL,
  `credito` double NOT NULL,
  `dc` varchar(2) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat` (`cat`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3906 ;

INSERT INTO `lc_movimento` (`id`, `pago`, `dia`, `mes`, `ano`, `cat`, `idemp`, `usu_login`, `descricao`, `obs`, `debito`, `credito`, `dc`) VALUES
(41, 1, 11, 8, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-4/4', '', 10, 0, 'D'),
(39, 1, 11, 6, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-2/4', '', 20, 0, 'D'),
(40, 1, 10, 7, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-3/4', '', 30, 0, 'D'),
(33, 1, 16, 5, 2014, 149, 16, 'Adriano', 'WSUL 146511-1/5', '', 0, 1000, 'C'),
(34, 1, 16, 6, 2014, 149, 16, 'Adriano', 'WSUL 146511-2/5', '', 500, 0, 'D'),
(35, 1, 14, 7, 2014, 149, 16, 'Adriano', 'WSUL 146511-3/5', '', 500, 0, 'D'),
(36, 1, 13, 8, 2014, 149, 16, 'Adriano', 'WSUL 146511-4/5', '', 0, 200, 'C'),
(37, 1, 12, 9, 2014, 149, 16, 'Adriano', 'WSUL 146511-5/5', '', 300, 0, 'D'),
(38, 1, 12, 5, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-1/4', '', 0, 800, 'C'),
(28, 1, 2, 5, 2014, 149, 22, 'Adriano', 'MGNETRON 21629-3/3', '', 0, 700, 'C'),
(29, 1, 2, 5, 2014, 149, 22, 'Adriano', 'DELTACAPAS 15092-2/2', '', 100, 0, 'D'),
(30, 1, 14, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-1/3', '', 600, 0, 'D'),
(31, 1, 29, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-2/3', '', 50, 0, 'D'),
(32, 1, 13, 6, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-3/3', '', 0, 60, 'C'),
(42, 1, 2, 5, 2014, 149, 22, 'Adriano', 'RTO 3268-3/3', '', 20, 0, 'D'),
(43, 1, 2, 5, 2014, 149, 22, 'Adriano', 'ROUTE - ENERBRAX 21410-2/2', '', 100, 0, 'D'),
(44, 1, 2, 5, 2015, 149, 22, 'Adriano', 'SILVA MATTOS 23180-1/4', '', 0, 150, 'C'),
(45, 1, 2, 5, 2015, 149, 22, 'Adriano', 'WGK 16339-1/3', '', 100, 0, 'D'),
(46, 1, 2, 5, 2015, 149, 16, 'Adriano', 'ROYALCICLO 35755-1', '', 200, 0, 'D'),
(47, 1, 2, 5, 2015, 149, 22, 'Adriano', 'CAR CENTRAL 42755-1/1', '', 0, 500, 'C');

CREATE VIEW vw_extrato
AS SELECT idemp, concat(lc_movimento.ano,'/',lc_movimento.mes,'/',lc_movimento.dia) as data,
lc_movimento.debito+lc_movimento.credito as valor,
lc_movimento.dc AS tipo
FROM lc_movimento;

SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
    SUM(IF(tipo = 'D', valor, 0)) AS debito,
    SUM(IF(tipo = 'C', valor, 0)) AS credito,
    (SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
         WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >= DATE_FORMAT(L2.data,'%Y%m')
    ) AS saldo
FROM vw_extrato
GROUP BY MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data)

When I try to filter by idemp = '16' result is not correct. 

CREATE TABLE IF NOT EXISTS `lc_movimento` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pago` int(11) DEFAULT NULL,
  `dia` int(11) DEFAULT NULL,
  `mes` int(11) DEFAULT NULL,
  `ano` int(11) DEFAULT NULL,
  `cat` int(11) DEFAULT NULL,
  `idemp` int(11) DEFAULT NULL,
  `usu_login` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  `descricao` longtext COLLATE latin1_general_ci,
  `obs` longtext COLLATE latin1_general_ci,
  `debito` double DEFAULT NULL,
  `credito` double NOT NULL,
  `dc` varchar(2) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat` (`cat`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3906 ;

INSERT INTO `lc_movimento` (`id`, `pago`, `dia`, `mes`, `ano`, `cat`, `idemp`, `usu_login`, `descricao`, `obs`, `debito`, `credito`, `dc`) VALUES
(41, 1, 11, 8, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-4/4', '', 10, 0, 'D'),
(39, 1, 11, 6, 2014, 149, 22, 'Rafael', 'SILVA MATTOS 23340-2/4', '', 20, 0, 'D'),
(40, 1, 10, 7, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-3/4', '', 30, 0, 'D'),
(33, 1, 16, 5, 2014, 149, 16, 'Adriano', 'WSUL 146511-1/5', '', 0, 1000, 'C'),
(34, 1, 16, 6, 2014, 149, 16, 'Adriano', 'WSUL 146511-2/5', '', 500, 0, 'D'),
(35, 1, 14, 7, 2014, 149, 16, 'Adriano', 'WSUL 146511-3/5', '', 500, 0, 'D'),
(36, 1, 13, 8, 2014, 149, 16, 'Adriano', 'WSUL 146511-4/5', '', 0, 200, 'C'),
(37, 1, 12, 9, 2014, 149, 16, 'Adriano', 'WSUL 146511-5/5', '', 300, 0, 'D'),
(38, 1, 12, 5, 2014, 149, 22, 'Adriano', 'SILVA MATTOS 23340-1/4', '', 0, 800, 'C'),
(28, 1, 2, 5, 2014, 149, 22, 'Adriano', 'MGNETRON 21629-3/3', '', 0, 700, 'C'),
(29, 1, 2, 5, 2014, 149, 22, 'Adriano', 'DELTACAPAS 15092-2/2', '', 100, 0, 'D'),
(30, 1, 14, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-1/3', '', 600, 0, 'D'),
(31, 1, 29, 5, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-2/3', '', 50, 0, 'D'),
(32, 1, 13, 6, 2014, 149, 22, 'Adriano', 'DIA-FRAG 72382-3/3', '', 0, 60, 'C'),
(42, 1, 2, 5, 2014, 149, 22, 'Adriano', 'RTO 3268-3/3', '', 20, 0, 'D'),
(43, 1, 2, 5, 2014, 149, 22, 'Adriano', 'ROUTE - ENERBRAX 21410-2/2', '', 100, 0, 'D'),
(44, 1, 2, 5, 2015, 149, 22, 'Adriano', 'SILVA MATTOS 23180-1/4', '', 0, 150, 'C'),
(45, 1, 2, 5, 2015, 149, 22, 'Adriano', 'WGK 16339-1/3', '', 100, 0, 'D'),
(46, 1, 2, 5, 2015, 149, 16, 'Adriano', 'ROYALCICLO 35755-1', '', 200, 0, 'D'),
(47, 1, 2, 5, 2015, 149, 22, 'Adriano', 'CAR CENTRAL 42755-1/1', '', 0, 500, 'C');

CREATE VIEW vw_extrato
AS SELECT idemp, concat(lc_movimento.ano,'/',lc_movimento.mes,'/',lc_movimento.dia) as data,
lc_movimento.debito+lc_movimento.credito as valor,
lc_movimento.dc AS tipo
FROM lc_movimento;


SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
    SUM(IF(tipo = 'D', valor, 0)) AS debito,
    SUM(IF(tipo = 'C', valor, 0)) AS credito,
    (SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
         WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >= DATE_FORMAT(L2.data,'%Y%m')
    ) AS saldo
FROM vw_extrato WHERE idemp = '16'
GROUP BY idemp, MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data)

Follow the try filter demo link sqlfiddle

Expected result of the consultation:

idemp   data            debito   credito   saldo
16      16/05/2014      0        1000      1000
16      16/06/2014      500      0         500
16      14/07/2014      500      0         0
16      13/08/2014      0        200       200
16      12/09/2014      300      0         -100
16      02/05/2015      200      0         -300
php mysql sqlfiddle
  • 1

    I added the codes to the question, regardless of whether they are in the fiddle service. We recommend that you always publish the codes, and everything essential to the question, right here in the stack.

  • What is the result obtained (so that it can be compared with the expected)?

1 answer

2


I think the problem is that your sub-query also searches for vw_extract data without filtering by idemp, so the sub-query is adding up everyone’s values when determining the balance.

Adding this filter seems to be ok:

SELECT idemp, DATE_FORMAT(data,'%d/%m/%Y') AS data,
SUM(IF(tipo = 'D', valor, 0)) AS debito,
SUM(IF(tipo = 'C', valor, 0)) AS credito,
(SELECT SUM(IF(tipo = 'C', valor, -valor)) FROM vw_extrato AS L2
      WHERE DATE_FORMAT(vw_extrato.data,'%Y%m') >=
          DATE_FORMAT(L2.data,'%Y%m') and
          idemp = vw_extrato.idemp) AS saldo
FROM vw_extrato WHERE idemp = '16' 
GROUP BY idemp, MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data);

+-------+------------+--------+---------+-------+
| idemp | data       | debito | credito | saldo |
+-------+------------+--------+---------+-------+
|    16 | 16/05/2014 |      0 |    1000 |  1000 |
|    16 | 16/06/2014 |    500 |       0 |   500 |
|    16 | 14/07/2014 |    500 |       0 |     0 |
|    16 | 13/08/2014 |      0 |     200 |   200 |
|    16 | 12/09/2014 |    300 |       0 |  -100 |
|    16 | 02/05/2015 |    200 |       0 |  -300 |
+-------+------------+--------+---------+-------+
6 rows in set (0.00 sec)

  • Exactly, this is the expected result achieved.

Browser other questions tagged php mysql sqlfiddle

You are not signed in. Login or sign up in order to post.