Resolución ejercicio final (24) - Eliminar artículos descatalogados.

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

En esta entrada resolveremos el punto 21:
Eliminar artículos descatalogados.

Se eliminarán todos los artículos que tengan el valor true en el campo Descatalogado.

Se realizará una consulta a una única tabla (Articulos)

El campo Descatalogado se ha creado como tipo BOOL, se constara que realmente se almacena como un valor de tipo TINYINT.

Comprobamos que artículos están descatalogados.

select id_articulo, nombre, descatalogado

from articulos

where descatalogado = true;

delete from articulos

where descatalogado = true;

Comprobamos nuevamente los artículos descatalogados.

select id_articulo, nombre, descatalogado

from articulos

where descatalogado = true;

Como se puede ver ahora en la base de datos ya no existen artículos descatalogados. Se muestra un EMPTY SET. 

NOTA: Una cuestión a tener en cuenta, es que las bases de datos no eliminan realmente los datos que se borran, de tal manera que eliminar datos de una Base de Datos, no implica una reducción del tamaño del fichero en disco.

Concretamente en MySQL existe un concepto conocido como residuo, que es la cantidad de espacio que se está usando que podría ser reducido de la Base de Datos.

Por ejemplo con phpMyAdmin, al seleccionar la Base de Datos se muestra un listado con las tablas y el residuo que se puede depurar.

Según la imagen anterior en la tabla artículos se pueden depurar 64 Bytes.

Para recuperar este espacio, seleccionar la tabla artículos, acceder a la opción Operaciones y seleccionar la opción "Optimizar la tabla" (Se realizar la operación equivalente a compactar en Microsoft Access)

Resolución ejercicio final (17) - Ver stock de un producto.

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

En esta entrada resolveremos el punto 14:
Ver stock de un producto.

Para obtener el stock de un producto, según hemos organizado la Base de Datos, será preciso sumar todas las compras y restar todas las ventas.

Para ello se realizará un join a tres tablas.

select articulos.id_articulo, nombre,
 sum(lin_ac.cantidad) as compras, sum(lin_fv.cantidad) as ventas
 , (sum(lin_ac.cantidad) -  sum(lin_fv.cantidad)) as stock
 from articulos left join lin_ac
 on articulos.id_articulo = lin_ac.id_articulo
 left join lin_fv
 on articulos.id_articulo = lin_fv.id_articulo
 where articulos.id_articulo = 8
 group by id_articulo;

El resultado es:

En el caso de que el artículo no exista el resultado es un Empty Set:

Como ejemplo se muestra el resultado de la consulta de stock del artículo 12345, del cual no hay ni compras ni ventas. Lo cual se puede considerar correcto.

Sin embargo si el artículo existe, en la columna que no se encuentran datos, el resultado será NULL y, como es sabido las operaciones con valores NULL dan como resultado NULL.

Como ejemplo se muestra el resultado de la consulta de stock del artículo 323, del cual  hay compras pero no ventas.

NOTA: Si el artículo existe pero no tiene ni compras ni ventas los tres valores serán NULL.

Para evitar este problema, será preciso usar la expresión condicional CASE que forma parte del estándar SQL92.

Como solución se plantea la posibilidad de modificar el cálculo de la columna stock, mediante dos instrucciones CASE que transformen el valor NULL en un valor numérico cero.

select articulos.id_articulo, nombre,

sum(lin_ac.cantidad) as compras,

sum(lin_fv.cantidad) as ventas,

(case when sum(lin_ac.cantidad) is null then 0 else sum(lin_ac.cantidad) end -

case when sum(lin_fv.cantidad) is null then 0 else sum(lin_fv.cantidad) end) as stock

from articulos left join lin_ac

on articulos.id_articulo = lin_ac.id_articulo

left join lin_fv

on articulos.id_articulo = lin_fv.id_articulo

where articulos.id_articulo = 323

group by id_articulo;

En este caso a pesar de que el valor de la suma de las ventas es NULL, como se transforma en un cero mediante la sentencia CASE el cálculo del stock funciona correctamente.

---

Otra posible solución es utilizar la tabla ubicación, en la cual actualizaremos el stock para cada compra y cada venta realizada.

select nombre, sum(stock) as total_stock
from articulos inner join ubicacion
on articulos.id_articulo = ubicacion.id_articulo
where ubicacion.id_articulo = 8
group by ubicacion.id_articulo
having total_stock > 0;

Resolución ejercicio final (16) - Ver totales de cliente entre fechas.

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

En esta entrada resolveremos el punto 13:
Ver totales de cliente entre fechas.

Lo que se desea realmente es obtener la suma de todas las facturas de cada cliente, como en el punto anterior, pero añadiendo un filtrado entre fechas.

Para obtener el total en el listado es preciso realizar una consulta en la que además se sumen todas las líneas de cada factura agrupándola por cliente, tras haber sido filtradas por fechas.

Para ello se realizará un join a cuatro tablas.

Pero antes es preciso multiplicar el precio neto de cada artículo, por la cantidad de unidades, y por aplicarle el IVA correspondiente.

select clientes.id_cliente, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total
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-11-22" and "2012-11-22"
group by clientes.id_cliente;

Para poder filtrar las facturas por fechas antes de proceder a la agrupación se ha añadido la penúltima línea:

En esta línea se añade una sentencia WHERE que filtra fechas mediante el operador BETWEEN, en este caso concreto para obtener las facturas de día 22 de Diciembre de 2012.

En caso de desear el listado de facturas entre dos fechas distintas, es suficiente con modificarlas.

where cab_fv.fecha between "2012-11-22" and "2012-11-22"

Como se había explicado, la sentencia WHERE filtra líneas, no grupos, por lo tanto aparecerán todos los grupos, pero estos se realizarán sólo sobre las líneas que permita WHERE. 

NOTA: Si al modificar la fechas del operador BETWEEN se indica la primera fecha superior a la segunda, se obtendrá como resultado un empty Set.

Obsérvese la diferencia al intentar obtener las facturas del mes de Noviembre.

En primer lugar filtraremos del primer día al último.

where cab_fv.fecha between "2012-11-01" and "2012-11-30"

El resultado es:

En segundo lugar filtraremos del último día al primero.

where cab_fv.fecha between "2012-11-30" and "2012-11-01"

El resultado es, en contra de lo esperado, un EMPTY SET: