How to capture two data AND get lost time?

Question

How to capture two data AND get lost time?

Navigation

#1 by (0 votes)

0

Hi guys, I have a question a bit urgent and I will present the case so you can understand me try to be as clear as possible, I have a database connected to a PLC that gives me the speed of 3 machines with the time and the date of that speed, what I want to do is capture the time and date of each machine when the speed is 90 in that time in another table to finally make a subtraction of both hours and dates to know how long the machine lasted without activity, thank you very much I hope and you can help me

PD: The columns that I define with the "Velocity" value are those that can be seen in the image:

Recubridoras_PLC_K10_Vel_linea, Recubridoras_PLC_K11_Vel_linea And PLC_Vinilos_K12_Vel_Maq

sql sql-server

asked by jeifer jimenez 10.07.2018 в 17:10

source

1 answer

Help with android studio Entity Embedded In Morphia

score 0 · Accepted Answer

I hope the following code will be of your help:

    DECLARE @fecha datetime, @maquina varchar(5), /*no se que tipo de dato sea*/ 
@fechaInicial datetime, @fallo varchar(2) = 'OK';

/*--debes crear un cursor por cada maquina de la siguiente forma:
DECLARE disponibilidad_cursor CURSOR FOR   
SELECT DateTimeColumn, Recubridoras_PLC_K10_Vel_linea  
FROM tabla  
--si quieres tomar desde cierta fecha descomenta la siguiente linea:
--where DateTimeColumn >= '2017-07-01 08:00:02'
--o si te dan un rango de fechas descomenta la siguiente linea
--where DateTimeColumn between '2017-07-01 08:00:02' and '2017-08-01 08:00:02'
ORDER BY DateTimeColumn;  */
DECLARE disponibilidad_cursor CURSOR FOR   
select '2018-07-24 13:20:12.237', 81 union
select '2018-07-24 15:20:12.237', 95 union
select '2018-07-24 18:20:12.237', 90

OPEN disponibilidad_cursor  
FETCH NEXT FROM disponibilidad_cursor   
INTO @fecha, @maquina  
WHILE @@FETCH_STATUS = 0  
BEGIN  

if (@maquina > 90 or @maquina < 90)
begin
    if(@fallo = 'OK')
    begin
        set @fechaInicial = @fecha;
        set @fallo = 'KO';
    end
end
else
begin
    if(@fallo = 'KO')
    begin
        --en este punto ya tenemos la fecha inicial en la que fallo la maquina y la fecha en la volvio a funcionar
        print('La maquina falla durante: '+ CONVERT(varchar(10),DATEDIFF(year, @fechaInicial, @fecha))+' años, '+CONVERT(varchar(10),DATEDIFF(month, @fechaInicial, @fecha))+' meses, '
        +CONVERT(varchar(10),DATEDIFF(day, @fechaInicial, @fecha))+' dias, '+CONVERT(varchar(10),DATEDIFF(hour, @fechaInicial, @fecha))+' horas, '
        +CONVERT(varchar(10),DATEDIFF(minute, @fechaInicial, @fecha))+' minutos, '+CONVERT(varchar(10),DATEDIFF(second, @fechaInicial, @fecha)) + ' segundos.');
        --ahi ya tienes los datos, tu decides si los metes en una tabla temporal y los insertas o no se
        set @fallo = 'OK';
    end
end

    FETCH NEXT FROM disponibilidad_cursor   
    INTO @fecha, @maquina  
END   
CLOSE disponibilidad_cursor;  
DEALLOCATE disponibilidad_cursor;

What you must change is the cursor, I comment it to make the test and it works. Now something that you have to contemplate is the subject of the minutes, so you have it in mind. From what I see, I calculate that there were 5 hours of non-availability, but I'm getting 5 hours in minutes and seconds too, we'll have to see how to validate that.