· 8 years ago · Apr 04, 2018, 01:38 AM
1SELECT SUBSTRING(dt_vencimento,1,7) as anomes, SUM(vl_valor) as valor_total
2FROM escola.tb_despesas_administrativas
3where YEAR(dt_vencimento) =2018
4GROUP BY anomes;
5
6SELECT SUBSTRING(dt_vencimento,1,7) as anomes, SUM(vl_boleto) as valor_total
7FROM escola.tb_pedido
8where YEAR(dt_vencimento) =2018
9GROUP BY anomes;
10
11SELECT SUBSTRING(dt_vencimento,1,7) as anomes, SUM(vl_parcela) as valor_total
12FROM escola.tb_carne_matricula
13where YEAR(dt_vencimento) =2018
14GROUP BY anomes;
15
16anomes |valor_totalDebito(tb_1) |valor_totalCredito (tb_2) |
17 --------|------------------------|--------------------------|
18 2018-02 |142.30 |123.23 |
19 2018-03 |776.05 |423.11 |
20 2018-04 |251.05 |443.21 |
21 2018-05 |251.05 |112.33 |
22 2018-06 |251.05 |242.22 |
23 2018-07 |232.30 |121.34 |
24 2018-08 |42.30 |332.22 |
25 2018-09 |42.30 |111.32 |
26 2018-10 |42.30 |543.33 |
27 2018-11 |42.30 |443.22 |
28 2018-12 |42.30 |342.56 |
29 --------|------------------------|--------------------------|
30
31CREATE TABLE `tb_carne_matricula` (
32 `cd_carne_matricula` int(11) NOT NULL AUTO_INCREMENT,
33 `nu_parcela` int(11) DEFAULT NULL,
34 `nu_parcelatotal` int(11) DEFAULT NULL,
35 `vl_parcela` varchar(45) DEFAULT NULL,
36 `cd_matricula` int(11) NOT NULL,
37 `bo_situacao_pagamento` tinyint(4) DEFAULT NULL,
38 `dt_vencimento` date DEFAULT NULL,
39 PRIMARY KEY (`cd_carne_matricula`),
40 KEY `fk_tb_carne_matricula_tb_matricula1_idx` (`cd_matricula`),
41 CONSTRAINT `fk_tb_carne_matricula_tb_matricula1` FOREIGN KEY (`cd_matricula`) REFERENCES `tb_matricula` (`cd_matricula`) ON DELETE NO ACTION ON UPDATE NO ACTION
42) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=latin1;
43
44INSERT INTO `tb_carne_matricula` VALUES (86,1,3,'108.33333333333',64,0,'2018-03-11'),(87,2,3,'108.33333333333',64,1,'2018-03-11'),(88,3,3,'108.33333333333',64,1,'2018-03-11'),(101,1,13,'42.307692307692',66,0,'2018-02-09'),(102,2,13,'42.307692307692',66,0,'2018-03-09'),(103,3,13,'42.307692307692',66,0,'2018-04-09'),(104,4,13,'42.307692307692',66,0,'2018-05-09'),(105,5,13,'42.307692307692',66,0,'2018-06-09'),(106,6,13,'42.307692307692',66,0,'2018-07-09'),(107,7,13,'42.307692307692',66,0,'2018-08-09'),(108,8,13,'42.307692307692',66,0,'2018-09-09'),(109,9,13,'42.307692307692',66,0,'2018-10-09'),(110,10,13,'42.307692307692',66,0,'2018-11-09'),(111,11,13,'42.307692307692',66,0,'2018-12-09'),(112,12,13,'42.307692307692',66,0,'2019-01-09'),(113,13,13,'42.307692307692',66,0,'2019-02-09'),(114,1,2,'100',67,1,'2018-02-15'),(115,2,2,'100',67,0,'2018-03-15'),(116,1,5,'50',68,0,'2018-03-11'),(117,2,5,'50',68,0,'2018-04-11'),(118,3,5,'50',68,0,'2018-05-11'),(119,4,5,'50',68,0,'2018-06-11'),(120,5,5,'50',68,0,'2018-07-11'),(121,1,5,'100',69,1,'2018-03-15'),(122,2,5,'100',69,0,'2018-04-15'),(123,3,5,'100',69,0,'2018-05-15'),(124,4,5,'100',69,0,'2018-06-15'),(125,5,5,'100',69,0,'2018-07-15'),(126,1,5,'40',70,1,'2018-03-15'),(127,2,5,'40',70,0,'2018-04-15'),(128,3,5,'40',70,0,'2018-05-15'),(129,4,5,'40',70,0,'2018-06-15'),(130,5,5,'40',70,0,'2018-07-15'),(131,1,1,'100',71,0,'2018-03-11'),(132,1,4,'18.75',72,0,'2018-03-25'),(133,2,4,'18.75',72,0,'2018-04-25'),(134,3,4,'18.75',72,0,'2018-05-25'),(135,4,4,'18.75',72,0,'2018-06-25');
45
46DROP TABLE IF EXISTS `tb_despesas_administrativas`;
47CREATE TABLE `tb_despesas_administrativas` (
48 `cd_despesas_administrativas` int(11) NOT NULL AUTO_INCREMENT,
49 `dt_competencia` date DEFAULT NULL,
50 `dt_vencimento` date DEFAULT NULL,
51 `vl_valor` decimal(10,2) DEFAULT NULL,
52 `nu_documento` text,
53 `nu_repetir` int(11) DEFAULT NULL,
54 `ds_despesas` text,
55 `bo_situacao_pagamento` tinyint(4) DEFAULT NULL,
56 `dt_pagamento` date DEFAULT NULL,
57 `vl_juros` decimal(10,2) DEFAULT NULL,
58 `vl_multa` decimal(10,2) DEFAULT NULL,
59 `vl_pago` decimal(10,2) DEFAULT NULL,
60 `repetir_numero` int(11) DEFAULT NULL,
61 `cd_categoria_despesas` int(11) NOT NULL,
62 PRIMARY KEY (`cd_despesas_administrativas`),
63 KEY `fk_tb_despesas_administrativas_tb_categoria_despesas1_idx` (`cd_categoria_despesas`),
64 CONSTRAINT `fk_tb_despesas_administrativas_tb_categoria_despesas1` FOREIGN KEY (`cd_categoria_despesas`) REFERENCES `tb_categoria_despesas` (`cd_categoria_despesas`) ON DELETE NO ACTION ON UPDATE NO ACTION
65) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=latin1;
66
67INSERT INTO `tb_despesas_administrativas` VALUES (85,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,1,1),(86,'2018-01-01','2018-01-01',520.00,'123',12,'Teste Alterar',0,NULL,0.00,0.00,521.00,NULL,1),(87,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',0,'2018-01-01',0.00,0.00,520.00,1,1),(88,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,4,1),(89,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,5,1),(90,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,6,1),(91,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,7,1),(92,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,8,1),(93,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,9,1),(94,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,10,1),(95,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,11,1),(96,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,'2018-01-01',0.00,0.00,520.00,12,1),(97,'2018-01-01','2018-01-01',520.00,'123',12,'Teste',1,NULL,0.00,0.00,520.00,12,1),(98,'2018-02-01','2018-03-22',350.00,'133',1,'Pagamento da mensalidade do sistema',0,'2018-02-01',0.00,0.00,350.00,1,3),(100,'2018-01-08','2018-02-01',200.00,'0',3,'CELG DISTRIBUIÇÃO',1,'2018-02-10',0.00,0.00,200.00,1,2),(101,'2018-01-08','2018-02-01',200.00,'0',3,'energia',0,'2018-02-10',0.00,0.00,0.00,3,2),(102,'2018-03-17','2018-04-11',250.00,'23',1,'Teste',1,'2018-03-11',0.00,0.00,250.00,1,1),(103,'2018-04-17','2018-04-17',275.50,'12',1,'Despesas teste competencia',0,'2018-03-17',0.00,0.00,0.00,1,2),(104,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,1,1),(105,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,2,1),(106,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',1,'2018-03-20',0.00,0.00,250.00,1,1),(107,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,4,1),(108,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,5,1),(109,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,6,1),(110,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,7,1),(111,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,8,1),(112,'2018-03-20','2018-03-25',250.00,'123',9,'Pagar Sharles',0,'2018-03-20',0.00,0.00,0.00,9,1),(113,'2018-04-01','2018-04-12',300.00,'SN',1,'Energia',0,'2018-04-01',0.00,0.00,0.00,1,2),(114,'2018-04-01','2018-04-15',100.00,'12',1,'Despesa teste',0,'2018-04-01',0.00,0.00,0.00,1,4),(115,'2018-04-01','2018-04-12',1000.00,'1',1,'Outra',0,'2018-04-01',0.00,0.00,0.00,1,4);
68
69CREATE TABLE `tb_pedido` (
70 `cd_pedido` int(11) NOT NULL AUTO_INCREMENT,
71 `vl_boleto` decimal(10,2) DEFAULT NULL,
72 `dt_referente` date DEFAULT NULL,
73 `vl_desconto` decimal(10,2) DEFAULT NULL,
74 `dt_gerado` date DEFAULT NULL,
75 `situacao` tinyint(4) DEFAULT NULL,
76 `nu_documento` text,
77 `bo_envio_remessa` tinyint(4) DEFAULT NULL,
78 `bo_pedido_automatico` tinyint(4) DEFAULT NULL,
79 `bo_impresso` tinyint(4) DEFAULT NULL,
80 `dt_vencimento` date DEFAULT NULL,
81 `dt_pagamento` date DEFAULT NULL,
82 `bo_multa` tinyint(4) DEFAULT NULL,
83 `bo_matricula` tinyint(4) DEFAULT NULL,
84 `bo_cobrar_multa` tinyint(4) DEFAULT NULL,
85 `bo_cobrar_juros` tinyint(4) DEFAULT NULL,
86 `bo_dar_desconto` tinyint(4) DEFAULT NULL,
87 `vl_boleto_reajustado` decimal(10,2) DEFAULT NULL,
88 `vl_boleto_pago_no_banco` decimal(10,2) DEFAULT NULL,
89 `vl_taxa_cobrado_pelo_banco` decimal(10,2) DEFAULT NULL,
90 `cd_usuario` int(11) NOT NULL,
91 `cd_aluno` int(11) NOT NULL,
92 PRIMARY KEY (`cd_pedido`),
93 KEY `fk_tb_pedido_tb_usuario1_idx` (`cd_usuario`),
94 KEY `fk_reference_5` (`cd_aluno`),
95 CONSTRAINT `fk_reference_5` FOREIGN KEY (`cd_aluno`) REFERENCES `tb_aluno` (`cd_aluno`),
96 CONSTRAINT `fk_tb_pedido_tb_usuario1` FOREIGN KEY (`cd_usuario`) REFERENCES `tb_usuario` (`cd_usuario`) ON DELETE NO ACTION ON UPDATE NO ACTION
97) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;
98
99INSERT INTO `tb_pedido` VALUES (39,153.55,'2018-03-15',0.00,'2018-03-20',1,'',0,0,0,'2018-03-20','2018-03-20',1,0,1,1,1,153.55,153.55,0.00,1,19),(40,138.25,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-20','2018-03-20',1,0,1,1,1,138.25,0.00,0.00,1,59),(41,114.75,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,114.75,0.00,0.00,1,77),(42,153.00,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,153.00,0.00,0.00,1,79),(43,137.70,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,137.70,0.00,0.00,1,80),(44,76.50,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,76.50,0.00,0.00,1,81),(45,153.00,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-09','2018-03-20',1,0,1,1,1,153.00,0.00,0.00,1,82),(46,38.36,'2018-03-15',0.00,'2018-03-20',0,'',0,0,0,'2018-03-20','2018-03-20',1,0,1,1,1,38.25,0.00,0.00,1,83),(47,127.50,'2018-01-01',0.00,'2018-04-01',1,'',0,0,0,'2018-01-09','2018-04-01',0,0,0,0,1,127.50,127.50,0.00,1,80);