none
Actualizar dos tablas en base a campos datetime usando Jobs RRS feed

  • Pregunta

  • tengo dos tablas las cuales quiero actualizar en base a una Fecha de Inicio y una Fecha Final.

    Tabla 1

    ID	NUMERO  	ACTIVO	FCH_INICIO	FCH_FIN	           NUM_SUPL
    1	508309	         0   12/7/19 7:00 AM	11/7/19 8:00 AM	   427592
    2	508309	         0   12/5/19 8:00 AM	12/5/19 9:00 AM	   427592
    3	508309	         1   12/5/19 10:00 AM	12/5/19 12:00 PM   427592
    6	508309	         0   12/5/19 11:00 AM	12/5/19 12:00 PM   427592
    7	508309	         0   12/5/19 1:00 PM	12/5/19 2:00 PM	   427592
    8	508309	         0   12/5/19 3:00 PM	12/5/19 4:00 PM	   427592
    9	508309	         0   12/3/19 3:00 PM	12/3/19 4:00 PM	   427592
    10	605271	         0   12/3/19 7:00 AM	12/3/19 8:00 AM	   439870
    


    Tabla 2

    ID	NUMERO	ACTIVO	AUSENTE
    1	376650	  1	       0
    2	587995	  1	       0
    3	605271	  1	       0
    4	439870	  1	       0
    5	428240	  1	       0
    6	527863	  1	       0
    7	433839	  0	       1
    8	427592	  1	       0
    9	579193	  1	       0
    10	374981	  1	       0
    11	515024	  1	       0
    12	508309	  1	       0
    

    Numero 1: mis usuarios registran la fecha y quiero que si la fecha y hora es del siguiente dia o del mismo dia pero una hora distinta, mi idea es hacer un job que se ejecute cada hora y si la fecha y hora es igual de la tabla1 usando getdate() que el campo ACTIVO de la tabla1 pase a 1 y en la tabla2 en base al campo NUMERO que esta entre ambos se me actualize el campo ACTIVO a 0 y AUSENTE a 1 de la tabla 2.

    Numero 2: Los usuarios tambien registran la fecha final, entonces quiero hacer lo contrario de arriba, otro job que se este ejecutando cada hora y vea si la fecha y hora final en que se esta ejecutando el job es iguala la del server usando getdate(), entonces de la tabla1 actualize el campo ACTIVO a 0 y en base al campo NUMERO entre ambas actualize la tabla2 el campo ACTIVO a 1 y AUSENTE a 0.

    Alguna idea de como realizar los dos puntos 1 y 2??

    para el punto 1 tengo esto en el job pero no funciona:

    Update Tabla1
    set ACTIVO = 1
     where ACTIVO = 0
      AND CONVERT(DATETIME, FCH_INICIO) = CONVERT(DATETIME, GETDATE())
        go
        update M
        set M.ACTIVO = 0
        from Tabla2 as M 
        inner join Tabla1 as A on A.NUMERO_GE = M.NUMERO 
        where A.ACTIVO = 1 AND CONVERT(DATETIME, A.FCH_INICIO) = CONVERT(DATETIME, GETDATE())
        go

    y para el punto 2 tengo:

    Update Tabla1
    set ACTIVO = 0
     where ACTIVO = 1
      AND CONVERT(DATE, FCH_FIN) = CONVERT(DATE, GETDATE())
        and DATEDIFF(Second, GETDATE(),FCH_FIN) < 3
        go

    Alguna ayuda para resolver esto??

    Gracias de antemano.

    jueves, 5 de diciembre de 2019 17:53

Respuestas

  • Hola MannyDevs:

    Si no había tenido en cuenta, que no puede ser igual a hora fin.

    SET LANGUAGE 'us_english'
    DECLARE @VAR DATETIME = '2019-12-06 09:14';
    
    declare @table table (FCH_INICIO DATETIME, FCH_FIN DATETIME)
    SET LANGUAGE 'us_english'
    INSERT INTO @table (FCH_INICIO, FCH_FIN)
    VALUES
    ('2019-12-06 07:00:00.000','2019-11-06 08:00:00.000'),
    ('2019-12-06 08:00:00.000','2019-12-06 09:00:00.000'),
    ('2019-12-06 09:00:00.000','2019-12-06 10:00:00.000'),
    ('2019-12-06 10:00:00.000','2019-12-06 11:00:00.000'),
    ('2019-12-06 11:00:00.000','2019-12-06 12:00:00.000'),
    ('2019-12-06 12:00:00.000','2019-12-06 13:00:00.000'),
    ('2019-12-06 13:00:00.000','2019-12-06 14:00:00.000'),
    ('2019-12-06 14:00:00.000','2019-12-06 15:00:00.000');
    
    Select t.FCH_INICIO, t.FCH_FIN 
    from @table t WHERE CAST(FCH_INICIO AS DATE) = CAST (@VAR AS DATE) and DATEPART(HOUR,@VAR) >= DATEPART(HOUR,FCH_INICIO) and DATEPART(HOUR,@VAR) <= DATEPART(HOUR,FCH_FIN) AND DATEPART(HOUR,@VAR) != DATEPART(HOUR,FCH_FIN)

    Nota: Como ves he cambiado getdate() , por @var, para establecer las nueve y cuarto de la mañana sin cambiar mi hora del sistema.
    • Editado Javi Fernández F sábado, 7 de diciembre de 2019 6:14 Añadir nota
    • Marcado como respuesta MannyDevs lunes, 9 de diciembre de 2019 18:01
    sábado, 7 de diciembre de 2019 6:13

Todas las respuestas

  • Hola MannyDevs:

    Ideas muchas, pero creo que lo estás enfocando mal.

    No tiene sentido decirle al sql server que trabaje una vez cada hora cambiando datos de dos tablas, para poder hacer una select limpia sobre la tabla 2, que supongo es la que necesitas, para cruzar con empleados, y así saber cada hora, quien estuvo currando.

    Imáginate que la empresa crece y tienes 100 millones de empleados, a turnos. Tus jobs tendrán que machacar al servidor con esas consultas, para que el jefe de Recursos humanos, una vez al mes consulte esa info.

    No es mucho más lógico, que las entradas y salidas, se registren como se registran y que sea esa select la que calcule en tiempo de ejecución esa información.

    Para poder calcular esto, tal cual lo has pedido

    Si el día es mayor, castea las dos fechas a date y así puedes comparar

    cast(fch_inicio as date)> cast(getdate() as date)

    Si el día es el mismo pero una hora distinta: (que significa distinta)

    3 508309 1 12/5/19 10:00 AM 12/5/19 12:00 PM 427592

    A las 11 ese job, lo pondrá como desactivado?

    Supongo que si tienes hora de entrada y hora de salida, tendrás que comparar si la hora actual esta en el rango de esas horas.

    or
    (
    cast(fch_inicio as date() = cast(getdate() as date)
       and 
    datepart(hour,getdate()) between datepart(hour,fch_inicio) and datepart(hour,coalesce(fch_fin,fch_inicio))
    )

    Me he curado en salud, y si la hora de fin no existe, porque no se ha registrado, obtengo la misma hora de salida de la fecha de entrada.

    viernes, 6 de diciembre de 2019 7:32
  • Hola MannyDevs:

    Ideas muchas, pero creo que lo estás enfocando mal.

    No tiene sentido decirle al sql server que trabaje una vez cada hora cambiando datos de dos tablas, para poder hacer una select limpia sobre la tabla 2, que supongo es la que necesitas, para cruzar con empleados, y así saber cada hora, quien estuvo currando.

    Imáginate que la empresa crece y tienes 100 millones de empleados, a turnos. Tus jobs tendrán que machacar al servidor con esas consultas, para que el jefe de Recursos humanos, una vez al mes consulte esa info.

    No es mucho más lógico, que las entradas y salidas, se registren como se registran y que sea esa select la que calcule en tiempo de ejecución esa información.

    Para poder calcular esto, tal cual lo has pedido

    Si el día es mayor, castea las dos fechas a date y así puedes comparar

    cast(fch_inicio as date)> cast(getdate() as date)

    Si el día es el mismo pero una hora distinta: (que significa distinta)

    3 508309 1 12/5/19 10:00 AM 12/5/19 12:00 PM 427592

    A las 11 ese job, lo pondrá como desactivado?

    Supongo que si tienes hora de entrada y hora de salida, tendrás que comparar si la hora actual esta en el rango de esas horas.

    or
    (
    cast(fch_inicio as date() = cast(getdate() as date)
       and 
    datepart(hour,getdate()) between datepart(hour,fch_inicio) and datepart(hour,coalesce(fch_fin,fch_inicio))
    )

    Me he curado en salud, y si la hora de fin no existe, porque no se ha registrado, obtengo la misma hora de salida de la fecha de entrada.

    Buen dia Javi

    a tu duda Si el día es el mismo pero una hora distinta: (que significa distinta)  , mis usuarios pueden poner hora de inicio de 7 AM asta 7 PM a eso me feriero.

    a tu otra pregunta A las 11 ese job, lo pondrá como desactivado? no a las 11 ahi tiene que hasta las 12, a las 11 correra el job pero si no tiene nada a esa hora entonces no hara nada, pero si a las 12 porque esa es la hora final.

    Ahora bien probando como dices en tu ejemplo no funcniona del todo o algo estoy haciendo mal, por ejemplo:

    tengo esto: 

    FCH_INICIO	        FCH_FIN
    2019-12-06 07:00:00.000	2019-11-06 08:00:00.000
    2019-12-06 08:00:00.000	2019-12-06 09:00:00.000
    2019-12-06 09:00:00.000	2019-12-06 10:00:00.000
    2019-12-06 10:00:00.000	2019-12-06 11:00:00.000
    2019-12-06 11:00:00.000	2019-12-06 12:00:00.000
    2019-12-06 12:00:00.000	2019-12-06 13:00:00.000
    2019-12-06 13:00:00.000	2019-12-06 14:00:00.000
    2019-12-06 14:00:00.000	2019-12-06 15:00:00.000

    cuando corro este query me trae dos lineas:

    select * from Tabla1 
           where 
           CONVERT(DATE, FCH_INICIO) = CONVERT(DATE, GETDATE()) and DATEPART(HOUR,GETDATE()) between DATEPART(HOUR,FCH_INICIO) and DATEPART(HOUR,FCH_FIN)
           go

    esto es lo que me resulta:

    FCH_INICIO	        FCH_FIN
    2019-12-06 08:00:00.000	2019-12-06 09:00:00.000
    2019-12-06 09:00:00.000	2019-12-06 10:00:00.000

    siendo que solo deberia de traerme los de las 9:00 a 10:00 ya que lo estoy corriendo en ese reango, pero me tra en anterior tambien.

    Saludos.

    viernes, 6 de diciembre de 2019 15:44
  • Hola MannyDevs:

    Si no había tenido en cuenta, que no puede ser igual a hora fin.

    SET LANGUAGE 'us_english'
    DECLARE @VAR DATETIME = '2019-12-06 09:14';
    
    declare @table table (FCH_INICIO DATETIME, FCH_FIN DATETIME)
    SET LANGUAGE 'us_english'
    INSERT INTO @table (FCH_INICIO, FCH_FIN)
    VALUES
    ('2019-12-06 07:00:00.000','2019-11-06 08:00:00.000'),
    ('2019-12-06 08:00:00.000','2019-12-06 09:00:00.000'),
    ('2019-12-06 09:00:00.000','2019-12-06 10:00:00.000'),
    ('2019-12-06 10:00:00.000','2019-12-06 11:00:00.000'),
    ('2019-12-06 11:00:00.000','2019-12-06 12:00:00.000'),
    ('2019-12-06 12:00:00.000','2019-12-06 13:00:00.000'),
    ('2019-12-06 13:00:00.000','2019-12-06 14:00:00.000'),
    ('2019-12-06 14:00:00.000','2019-12-06 15:00:00.000');
    
    Select t.FCH_INICIO, t.FCH_FIN 
    from @table t WHERE CAST(FCH_INICIO AS DATE) = CAST (@VAR AS DATE) and DATEPART(HOUR,@VAR) >= DATEPART(HOUR,FCH_INICIO) and DATEPART(HOUR,@VAR) <= DATEPART(HOUR,FCH_FIN) AND DATEPART(HOUR,@VAR) != DATEPART(HOUR,FCH_FIN)

    Nota: Como ves he cambiado getdate() , por @var, para establecer las nueve y cuarto de la mañana sin cambiar mi hora del sistema.
    • Editado Javi Fernández F sábado, 7 de diciembre de 2019 6:14 Añadir nota
    • Marcado como respuesta MannyDevs lunes, 9 de diciembre de 2019 18:01
    sábado, 7 de diciembre de 2019 6:13