consulta7_jumparound

tbl_ventas_ciudad_semestre.sql

DROP TEMPORARY TABLE IF EXISTS tbl_ventas_ciudad_semestre;
CREATE TEMPORARY TABLE tbl_ventas_ciudad_semestre
	/* Busca el empleado con mejor y peor ventas */
	SELECT 
		customer.salesRepEmployeeNumber, 
		CONCAT(employee.firstName, " ", employee.lastName) as Nombre_Empleado,
		office.city,
		SUM(volumen_venta_por_cliente) as venta_por_empleado , 
		employee.officeCode ,
		orden_year,
		semestre,
		periodo
	FROM
		(
			SELECT 
				orden.customerNumber, 
				SUM(vol_orden.volumen_venta_orden) as volumen_venta_por_cliente, 
				YEAR(orden.orderDate) AS orden_year, 
				CEIL(MONTH(orden.orderDate) / 6) AS `semestre`,
				CONCAT(YEAR(orden.orderDate), '-', CEIL(MONTH(orden.orderDate) / 6)) AS periodo
			FROM
				(
					SELECT 
						ordendetail.orderNumber, 
						sum(quantityOrdered) as volumen_venta_orden 
					FROM 
						ordendetail
					GROUP BY 
						ordendetail.orderNumber
				) vol_orden
			LEFT JOIN 
				orden ON orden.orderNumber = vol_orden.orderNumber
			WHERE 
				orden.status = "Shipped"
			GROUP BY 
				orden.customerNumber
		) vol_customer_orders
	LEFT JOIN 
		customer ON customer.customerNumber = vol_customer_orders.customerNumber
    LEFT JOIN 
		employee ON employee.employeeNumber = customer.salesRepEmployeeNumber
	LEFT JOIN
		office on office.officeCode = employee.officeCode
	GROUP BY 
		customer.salesRepEmployeeNumber
	ORDER BY periodo, city, venta_por_empleado;

tbl_ventas_ciudad_semestre_conteo.sql

DROP TEMPORARY TABLE IF EXISTS tbl_ventas_ciudad_semestre_conteo;
CREATE TEMPORARY TABLE tbl_ventas_ciudad_semestre_conteo
SELECT city, periodo, count(city) AS total_registros FROM tbl_ventas_ciudad_semestre
	GROUP BY periodo, city;