MySQL - Best Selling Cars Report Using View

Question

MySQL - Best Selling Cars Report Using View

Navigation

#1 by (0 votes)

0

An idea of how I can create a View by creating a report in decreasing order with the most rented cars in a certain period of time . Showing in this report, the vehicle license plate, the total mileage rounded during the reported period of time, the amount of leases carried out during the time period and the current vehicle mileage ??

Structure of the above tables:

CREATE TABLE tbl_carros (
  ID_CARROS int(11) NOT NULL AUTO_INCREMENT,
  PLACA varchar(100) NOT NULL,
  MODELO varchar(100) NOT NULL,
  ANO varchar(100) NOT NULL,
  COR varchar(100) NOT NULL,
  QUILOM varchar(100) DEFAULT NULL,
  SITUACAO varchar(100) NOT NULL,
  DESCRICAO varchar(100) DEFAULT NULL,
  TBL_CLASSES_DO_CARRO_ID_CLASSES int(11) NOT NULL,
  PRIMARY KEY (ID_CARROS,TBL_CLASSES_DO_CARRO_ID_CLASSES),
  KEY fk_TBL_CARROS_TBL_CLASSES_DO_CARRO1_idx (TBL_CLASSES_DO_CARRO_ID_CLASSES),
  CONSTRAINT fk_TBL_CARROS_TBL_CLASSES_DO_CARRO1 FOREIGN KEY (TBL_CLASSES_DO_CARRO_ID_CLASSES) REFERENCES tbl_classes_do_carro (ID_CLASSES) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE tbl_reservas (
  MULTA int(11) DEFAULT NULL,
  VALOR_TOTAL int(11) NOT NULL,
  SITUACAO varchar(100) DEFAULT NULL,
  DATA_RETORNO date NOT NULL,
  DATA_LOCACAO date NOT NULL,
  QUILOM_RODADOS int(11) DEFAULT NULL,
  QUANT_DIARIA varchar(45) NOT NULL,
  TBL_CLIENTES_ID_CLIENTES int(11) NOT NULL,
  TBL_FILIAIS_ID_FILIAIS int(11) NOT NULL,
  TBL_CARROS_ID_CARROS int(11) NOT NULL, PRIMARY KEY (TBL_CLIENTES_ID_CLIENTES,TBL_FILIAIS_ID_FILIAIS,TBL_CARROS_ID_CARROS),
  KEY fk_TBL_RESERVAS_TBL_CLIENTES_idx (TBL_CLIENTES_ID_CLIENTES),
  KEY fk_TBL_RESERVAS_TBL_FILIAIS1_idx (TBL_FILIAIS_ID_FILIAIS),
  KEY fk_TBL_RESERVAS_TBL_CARROS1_idx (TBL_CARROS_ID_CARROS),
  CONSTRAINT fk_TBL_RESERVAS_TBL_CARROS1 FOREIGN KEY (TBL_CARROS_ID_CARROS) REFERENCES tbl_carros (ID_CARROS) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_TBL_RESERVAS_TBL_CLIENTES FOREIGN KEY (TBL_CLIENTES_ID_CLIENTES) REFERENCES tbl_clientes (ID_CLIENTES) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_TBL_RESERVAS_TBL_FILIAIS1 FOREIGN KEY (TBL_FILIAIS_ID_FILIAIS) REFERENCES tbl_filiais (ID_FILIAIS) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql

asked by anonymous 06.06.2018 / 02:25

1 answer

Can not Resolve Symbol in AndroidStudio library Variable never reaches zero using randi

score 0 · Accepted Answer

Below is the select that you can use to generate your View.

Note: Remember to change the values of: lowestData and: highestData for the range you want.

SELECT 
    c.PLACA as Placa, SUM(r.QUILOM_RODADOS) as KMRodados,
    COUNT(1) as QTDLocacoesPeriodo, ANY_VALUE(c.QUILOM) as KMAtual
FROM
tbl_carros c
INNER JOIN tbl_reservas r ON r.TBL_CARROS_ID_CARROS = c.ID_CARROS
WHERE 
r.DATA_LOCACAO between ':menorData' and ':maiorData'
GROUP BY Placa
ORDER BY KMRodados DESC;