Complex bank type extract MySql how to solve

Question

Complex bank type extract MySql how to solve

Navigation

#1 by (2 votes)

3

I have a VIEW table that I need to extract an extract from it, I've already been able to help with stackoverflow here for an extract.

The problem is that now I need to filter this extract a little more, separating it for a given ID, but when using the WHERE id = emp the result of the statement 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' the 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 demo link filter attempt sqlfiddle

Expected result with query:

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

asked by anonymous 10.09.2015 / 06:00

1 answer

How do I select multiple fields in the same query? How to put Transition in Form Option

score 2 · Accepted Answer

I think the problem is that your subquery also fetches unprocessed vw_extract data by idemp, so the subquery is adding up everyone's values when it comes to determining the balance.

Adding this filter appears 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)