I have to make a query with MySql. I commented: I have a table with the entries and departures records. What I need is to know the amount of minutes that each person was outside, considering that in the field "Event" of Inputs / Exits in the table is stored with "1", Each Output is stored in the Field "Value" with " 0 "and Each Entry is stored in the" Value "field with" 1 ".
When taking an entry (Value = 1), look in the table for the immediate previous record of the same person that corresponds to an Output (Value = 0) and calculate the number of minutes difference between the date / time of entry and date / time of departure. Someone could help me?
I have made a query but I find it very slow and it hangs, since I have hundreds of thousands of records in the table.
SELECT d.id, d.dni, (select t.fecha from datos t where t.Evento=1 and t.Valor=0 and t.dni=d.dni and t.id<d.id order by t.id desc Limit 1) as Fecha_Salida,
d.fecha as Fecha_Entrada, TIMESTAMPDIFF(MINUTE, (select t.fecha from datos t where t.Evento=1 and t.Valor=0 and t.dni=d.dni and t.id<d.id order by t.id desc Limit 1), d.fecha) as MINUTOS
FROM datos d
where d.Evento=1 and d.valor=1 and month(d.fecha)=month(curdate())
order by d.id desc;