How to get the average life of the records?

Question

How to get the average life of the records?

Navigation

#1 by (0 votes)

-1

I want to take the average life of all the records that exist for example if an employer withdraws

Head protection - APRO - Helmet - Steel pro - Complete wing - N / A - 5 - 30-10-2018 ,

and then the next day remove the same product the average 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 throws me the following:

The calculation of the average life of the element has the purpose of knowing what is the useful life of a product delivered from the warehouse, it is calculated based on the number of times a worker renews or requests a specific product: For example, if a worker removes a pair of winery gloves on 10/30/2018 and then removes the same item on 10/31/2018, the average life of the item is 1 day. But this data can not be based on only one worker, but on the total number of workers in our database.

What I hope 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 |

mysql sql php

asked by Juan Perez 31.10.2018 в 16:20

source

1 answer

Sum of two fields Do not throw me the expected result when I use the inner join

score 0 · Answer 1

To get what you want, you need two fields: fecha_ingreso in the inventory table and fecha_salida in the delivery table. In the query, you make a comparative of the two dates, and thus obtain the days that the product was in the database:

SELECT de.codigo_inventario, AVG(DATEDIFF(de.fecha_salida, in.fecha_entrada))
  FROM inventario in
 INNER JOIN detale_entregas de
      ON in.codigo_inventario = de.codigo_inventario
 GROUP BY de.codigo_inventario