Resolución ejercicio final (25) - Generar modelo 347

RESOLUCIÓN DEL PUNTO 22 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 22:
Generar listado de Clientes y Proveedores para generar el modelo 347 (3005,06 €).

Se trata de generar un listado en el que se incluyan los clientes y proveedores que sobrepasen la cantidad facturada (IVA incluido) de 3005,06 € (lo que se corresponde, exactamente, con las antiguas 500000 pts).

Se realizará una consulta a siete tablas (Ivas, Articulos, lineas y cabeceras tanto de facturas de venta como de albaranes de compra, clientes y proveedores )

Dada la complejidad del listado, se puede facilitar el proceso dividiéndolo en dos partes. Por una parte se buscarán los datos necesarios para los clientes y en otra para los proveedores, que luego se unirán en un único listado mediante UNION.

Para obtener los clientes, la consulta se hará a cuatro tablas.

Se podrá aprovechar la solución del punto 13 (Ver totales de cliente entre fechas)

Para poder diferenciar entre clientes y proveedores se añadirá un campo literal, que permitirá diferenciar entre unos y otros una vez unidas las consultas.

select clientes.id_cliente as id, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 

"cliente" as tipo
from clientes left join cab_fv
on clientes.id_cliente = cab_fv.id_cliente
left join lin_fv
on cab_fv.id_fv = lin_fv.id_fv
inner join ivas
on lin_fv.iva = ivas.codigo_iva
where cab_fv.fecha between "2012-01-01" and "2012-12-31"

group by clientes.id_cliente;

Para obtener los proveedores, la consulta es similar a la anterior solo que cambiando los campos por los correspondientes a las tablas de proveedores y albaranes de compra.

select proveedores.id_proveedor as id, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 
"proveedor" as tipo
from proveedores left join cab_ac
on proveedores.id_proveedor = cab_ac.id_proveedor
left join lin_ac
on cab_ac.id_ac = lin_ac.id_ac
inner join ivas
on lin_ac.iva = ivas.codigo_iva
where cab_ac.fecha between "2012-01-01" and "2012-12-31"
group by proveedores.id_proveedor;

Una vez que se tienen las dos consultas solo es preciso unirlas mediante la cláusula UNION.

select clientes.id_cliente as id, nombre, nif,

round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 

"cliente" as tipo

from clientes left join cab_fv

on clientes.id_cliente = cab_fv.id_cliente

left join lin_fv

on cab_fv.id_fv = lin_fv.id_fv

inner join ivas

on lin_fv.iva = ivas.codigo_iva

where cab_fv.fecha between "2012-01-01" and "2012-12-31"

group by clientes.id_cliente

UNION

select proveedores.id_proveedor as id, nombre, nif,

round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 

"proveedor" as tipo

from proveedores left join cab_ac

on proveedores.id_proveedor = cab_ac.id_proveedor

left join lin_ac

on cab_ac.id_ac = lin_ac.id_ac

inner join ivas

on lin_ac.iva = ivas.codigo_iva

where cab_ac.fecha between "2012-01-01" and "2012-12-31"

group by proveedores.id_proveedor;

El resultado es una consulta de 23 líneas.

NOTA: Una posible solución para simplificar esta consulta y facilitar su reutilización es crear una vista, mediante CREATE VIEW, para obtener los clientes, otra para los proveedores y finalmente unir ambas en una sola consulta.

Creación de la vista de clientes:

create view clientes347
(id, nombre, nif, total, tipo)
as
select clientes.id_cliente, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2), 
"cliente"
from clientes left join cab_fv
on clientes.id_cliente = cab_fv.id_cliente
left join lin_fv
on cab_fv.id_fv = lin_fv.id_fv
inner join ivas
on lin_fv.iva = ivas.codigo_iva
where cab_fv.fecha between "2012-01-01" and "2012-12-31"
group by clientes.id_cliente;

Si se realiza un select de esta vista se obtiene como resultado el mismo que ejecutando la consulta completa:

select * from clientes347;

Creación de la vista de proveedores:

create view proveedores347
(id, nombre, nif, total, tipo)
as
select proveedores.id_proveedor, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2), 
"proveedor"
from proveedores left join cab_ac
on proveedores.id_proveedor = cab_ac.id_proveedor
left join lin_ac
on cab_ac.id_ac = lin_ac.id_ac
inner join ivas
on lin_ac.iva = ivas.codigo_iva
where cab_ac.fecha between "2012-01-01" and "2012-12-31"
group by proveedores.id_proveedor;

Si se realiza un select de esta vista se obtiene como resultado el mismo que ejecutando la consulta completa:

select * from proveedores347;

Y finalmente para obtener el listado completo, solo será preciso realizar la unión entre ambas vistas. Recordemos que SQL trata a las vistas como si fuesen tablas.

select * from clientes347
UNION
select * from proveedores347;

Resolución ejercicio final (9) - Crear un único listado que incluya los emails de Clientes y Proveedores.

RESOLUCIÓN DEL PUNTO 6 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 6:
Crear un único listado que incluya los emails de Clientes y Proveedores.

Al igual que al localizar los clientes sin email, para localizar los clientes con email será preciso comprobar dos condiciones.

Por un lado que el campo no es nulo.
select * from clientes where email is not NULL; 

Sin embargo con esta condición aparecerán en el listado los clientes a los que se les ha borrado el email o a los que se les ha inicializado el email a una cadena vacía.

Por lo tanto será preciso comprobar también aquellos campos que sean distintos de la cadena vacía.

select * from clientes where email <> "";

Para que la consulta conjunta funcione es preciso unirlas mediante el operador AND.

select nombre, email from clientes 
    where email is not NULL AND email <> "";

Pero el enunciado pide un único listado en el que se incluyan también los emails de los proveedores.

Para obtener un listado de emails de proveedores se usará la siguiente sentencia:


select nombre, email from proveedores
    where email is not NULL AND email <> "";

Para responder correctamente al enunciado, se deberá obtener una salida en la que se muestren los resultados de las dos consultas anteriores.

Para obtener ese resultado es posible unir las dos consultas mediante el operador UNION ALL o UNION (dependiendo de si deseamos que se muestren los datos repetidos o no.

NOTA: En este caso al querer obtener un listado de emails, no sería lógico obtener datos repetidos, por lo que usaremos el operador UNION.


select nombre, email from proveedores
    where email is not NULL AND email <> ""
UNION
select nombre, email from clientes
    where email is not NULL AND email <> "";

NOTA: En la imagen anterior se ven, en un recuadro en rojo los emails correspondientes a los proveedores, y en un recuadro verde parte de los emails correspondientes a los clientes.