I want to get the half-life of all the records that exist for example if an employer withdraws
Head protection - APRO - Helmet - Steel pro - Full brim - N/A - 5 - 10-30-2018 ,
and then the next day he withdraws the same product, the half-life of that product is 1 day .
I have the following query:
SELECT em.nombres, em.apellidos, re.numero_comprobante, cla.nombre AS 'clasificacion', pro.nombre_empresa AS 'proveedor', ti.nombre AS 'tipo', ma.nombre AS 'marca', mo.nombre AS 'modelo', nu.nombre AS 'talla', de.cantidad, re.f_ingreso
FROM detalle_registro_entrega AS de
INNER JOIN empleados AS em ON em.id = de.fk_id_empleados
INNER JOIN registro_entrega AS re ON re.id =de.fk_id_registro_entrega
INNER JOIN epp AS epp ON epp.id = de.fk_id_epp
INNER JOIN clasificacion AS cla ON cla.id = epp.fk_id_clasificacion
INNER JOIN proveedores AS pro ON pro.id = epp.fk_id_proveedores
INNER JOIN tipos AS ti ON ti.id = epp.fk_id_tipos
INNER JOIN marcas AS ma ON ma.id = epp.fk_id_marcas
INNER JOIN modelos AS mo ON mo.id = epp.fk_id_modelos
INNER JOIN numero_talla AS nu ON nu.id = epp.fk_id_numero_talla
The query returns the following:
The purpose of calculating the average life of the element is to be able to know the useful life of a product delivered from the warehouse, it is calculated based on the number of times that a worker renews or requests a specific product: For example, if A worker removes a pair of gloves from the warehouse on 10/30/2018 and then removes the same item on 10/31/2018. The half-life of the item is 1 day. But this data cannot be based on just one worker, but rather on the total number of workers found in our database.
What I expect is the following:
|clasificacion |proveedor|tipo |marca |modelo |talla|dia|
|-------------------------|---------|-----|---------|------------|-----|---|
|Protección para la cabeza|APRO |Casco|Steel pro|Ala completa|N/A | 1 |
|Protección para la cabeza|APRO |Casco|Steel pro|Ala completa|N/A | 3 |
|Protección para la cabeza|APRO |Casco|Steel pro|Ala completa|N/A | 8 |
To get what you want, you need two fields:
fecha_ingreso
in the inventory table andfecha_salida
in the delivery table. In the query, you make a comparison of the two dates, and thus you obtain the days that the product was in the database: