Cálculo de horas laborales

[[fjcg02]]

Buenas a todos,
he creado una función que cuenta las horas laborales entre dos fechas, descontando los días del fin de semana y los días festivos.
Para ello, hay que indicarle la fecha y hora de inicio y de fin, las horas de comienzo y fin del horario, y hay que tener en una tabla los festivos (festivos es el nombre de la tabla), para que pueda descontar esos días.
Es útil para por ejemplo medir las horas de cualquier servicio.

Espero que sea útil. Acepto sugerencias para su mejora.

Un saludo

Código SQL [-]
SET TERM ^ ;

CREATE OR ALTER PROCEDURE TIEMPOENTREFECHAS (
    f_inicio timestamp,
    f_fin timestamp,
    horainiciohorario time,
    horafinhorario time)
returns (
    tiempo float)
as
declare variable newf_inicio timestamp;
declare variable newf_fin timestamp;
declare variable sabadosydomingos integer;
declare variable festivos integer;
declare variable winicio timestamp;
declare variable wfin timestamp;
declare variable sabydom integer;
declare variable tiempow float;
declare variable horashorario float;
declare variable tiempodia1 float;
declare variable tiempodia2 float;
declare variable tiempoantesfestivos float;
declare variable tiempoendiasantesfestivos float;
begin
  /* Procedure Text */
    NEWF_INICIO= F_INICIO;
    NEWF_FIN= F_FIN;

   /* Adecuamos la fecha y hora de inicio respecto al fin del horario */
    if (CAST(newf_inicio AS TIME) > horafinhorario ) then
       NEWF_INICIO= CAST(NEWF_INICIO+1 AS DATE) + HORAINICIOHORARIO;

   /* Adecuamos la fecha y hora de inicio respecto al inicio del horario */
    IF (CAST(NEWF_INICIO AS TIME) < horaINICIOhorario ) then
       NEWF_INICIO= CAST(NEWF_INICIO AS DATE) + HORAINICIOHORARIO;
    /* Adecuamos la fecha y hora de inicio respecto a los fines de semana */
    if (EXTRACT(WEEKDAY FROM NEWF_INICIO) = 6) then     /* Sabados*/
       NEWF_INICIO= CAST(NEWF_INICIO+2 AS DATE)+ HORAINICIOHORARIO;
    if (EXTRACT(WEEKDAY FROM NEWF_INICIO) = 0) then /* Domingos*/
       NEWF_INICIO= CAST(NEWF_INICIO+1 AS DATE)+ HORAINICIOHORARIO;

   /* Adecuamos la fecha y hora de fin respecto al fin del horario */
    if (CAST(NEWF_FIN AS TIME)> horafinhorario )  then
       NEWF_FIN= CAST(NEWF_FIN AS DATE)+HORAFINHORARIO;
   /* Adecuamos la fecha y hora de fin respecto al inicio del horario */
    if ( CAST(NEWF_FIN AS TIME) < HORAINICIOhorario ) then
       NEWF_FIN= CAST(NEWF_FIN-1 AS DATE)+HORAFINHORARIO;
    /* Adecuamos la fecha y hora de fin respecto a los fines de semana */
    if (EXTRACT(WEEKDAY FROM NEWF_FIN) = 6) then /* Sabados */
       NEWF_FIN= CAST(NEWF_FIN-1 AS DATE)+ HORAFINHORARIO;
    if (EXTRACT(WEEKDAY FROM NEWF_FIN) = 0) then  /* Domingos*/
       NEWF_FIN= CAST(NEWF_FIN-2 AS DATE)+ HORAFINHORARIO;

     /* Calculamos los dias de fin de semana */
    SABADOSYDOMINGOS =((DATEDIFF(DAY, NEWF_INICIO, NEWF_FIN)+EXTRACT(WEEKDAY FROM NEWF_INICIO) )/7) *2;

    /* Calculamos todos los dias sin fines de semana . Hay que quitarle un día... */
    TIEMPOW = DATEDIFF( DAY, NEWF_INICIO, newf_fin) -1 - SABADOSYDOMINGOS;
    if (TIEMPOW < 0) then
      TIEMPOW=0;
    TIEMPOENDIASANTESFESTIVOS = TIEMPOW;
    TIEMPOANTESFESTIVOS = TIEMPOW * datediff(  MINUTE ,HORAINICIOHORARIO, HORAFINHORARIO) /60.00 + -- Dias transcurrido * horasDia
                          DATEDIFF( minute,  CAST(NEWF_INICIO AS TIME), horafinhorario)/60.00 +  -- horas de primer dia
                          DATEDIFF( minute,  horainiciohorario, CAST(NEWF_FIN AS TIME))/60.00 ; -- horas del ultimo dia
 
    /* Quitamos los festivos pero sin incluir los dias de inicio y fin , que tratamos luego */
    SELECT COUNT(1) FROM festivos 
      WHERE DIA BETWEEN CAST(:NEWF_INICIO AS DATE)+1 AND CAST(:newf_fin AS DATE)-1
      AND EXTRACT(weekday FROM DIA ) NOT IN (0,6) INTO :FESTIVOS;
    TIEMPOW= TIEMPOW - FESTIVOS;

    /* Calculamos el tiempo transcurrido en horas */
    if ( DATEDIFF( DAY, NEWF_INICIO, NEWF_FIN) = 0) then  -- si es el mismo dia, horafin - horainicio
       TIEMPO= DATEDIFF( minute,  CAST(NEWF_INICIO AS TIME), CAST(NEWF_FIN AS TIME)) / 60.00;
    ELSE
        TIEMPO= TIEMPOW * datediff(  MINUTE ,HORAINICIOHORARIO, HORAFINHORARIO) /60.00 + -- Dias transcurridos * horasDia
                          DATEDIFF( minute,  CAST(NEWF_INICIO AS TIME), horafinhorario)/60.00 +  -- horas de primer dia
                          DATEDIFF( minute,  horainiciohorario, CAST(NEWF_FIN AS TIME))/60.00 ; -- horas del ultimo dia

    -- si el primer dia es festivo y no es sabado ni domingo, restamos las horas
    FESTIVOS = 0;
    SELECT COUNT(1) from FESTIVOS
       where dia= cast(:NEWF_INICIO AS DATE)
       AND EXTRACT(weekday FROM DIA ) NOT IN (0,6) INTO :FESTIVOS;
    if (festivos =1) then
       TIEMPO=tiempo - DATEDIFF( minute, CAST(NEWF_INICIO AS TIME), HORAFINHORARIO)/60.00;
    -- si el ultimo día es festivo y no es sabado ni domingo , restamos las horas
    FESTIVOS = 0;
    SELECT COUNT(1) from FESTIVOS
       where dia= cast(:NEWF_FIN AS DATE)
       AND EXTRACT(weekday FROM DIA ) NOT IN (0,6) INTO :FESTIVOS;
    if (festivos =1) then
       TIEMPO=tiempo - DATEDIFF( minute, HORAINICIOHORARIO, CAST(NEWF_FIN AS TIME))/60.00;

    /* Para trazar cuando estoy desarrollando */
    HORASHORARIO = (datediff( MINUTE ,HORAINICIOHORARIO, HORAFINHORARIO))/60.00;
    tiempodia1 = DATEDIFF( minute, CAST(NEWF_INICIO AS TIME), HORAFINHORARIO)/60.00;
    TIEMPODIA2 = DATEDIFF( minute, HORAINICIOHORARIO, CAST(NEWF_FIN AS TIME))/60.00;
    WfIN = NEWF_FIN;
    WINICIO= NEWF_INICIO;
    SABYDOM= SABADOSYDOMINGOS;

TIEMPO= TIEMPO;
    if (TIEMPO < 0 or (TIEMPO IS NULL) ) then
      TIEMPO=0;

  suspend;
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON FESTIVOS TO PROCEDURE TIEMPOENTREFECHAS;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE TIEMPOENTREFECHAS TO SYSDBA;

[Ñuño Martínez]

Gracias compañero.

Es un buen pedazo de código.

[Casimiro Notevi]

¿Y los dias de fiesta, qué hacemos?


Gracias por el aporte

[ecfisa]

Muchas gracias fjcg02

Saludos

[[fjcg02]]

De nada chavales,

ya que me estoy valiendo de FB para sacar adelante unos marroncillos/trabajillos, y aunque me cuesta darle a la manivela ( no tengo ninguna herramienta para depurar los SP) pues al menos aporto algo y me quedo megasupersatisfecho.
Y así devuelvo a ClubDelphi y a FB la ayuda que me ofrecen desde hace muuuucho tiempo.

Creo que hay un gazapo, pero os dejo a vosotros para que lo destripeis.

Un saludo

[[fjcg02]]

Buenas noches amigos,
dándole una vuelta de tuerca a este procedimiento, he realizado una nueva versión. Ahora permite tener en cuenta diferentes horarios, y podríamos configurar cualquier combinación que seamos capaces de soportar.
Para ello, he cambiado el paradigma del cálculo.
Me gustaría que si alguien tiene un rato, pudiera hacer pruebas para comprobar su correcto
funcionamiento.

Os pongo en cuestión.
Tenemos que medir un servicio que tenemos contratado, y que debe resolvernos los problemas que le reportemos en un tiempo máximo de imaginemos X horas. Para medir cómo nos lo ejecutan, es decir, si cumplen lo estipulado, miramos los avisos cerrados por ejemplo en el mes, y si el tiempo transcurrido hasta cerrar el problema cumple el contrato, perfecto, si no, tendríamos que reclamar, pagar menos, o lo que sea.

Pero nuestra empresa tiene distintas oficinas, y cada una de ellas tiene un horario diferente; bueno, cada una de ellas no, todas tienen el mismo horario, y algunas sólo abren ciertos días a la semana, ya que donde se ubican, no es necesario tener todos los días abierto o es la misma persona la que atiende dos oficinas, una de lunes a miercoles y otra de jueves a viernes.
Pero esta circunstancia debemos tenerla en cuenta, ya que nuestro proveedor no podrá darnos el servicio en el tiempo acordado por no poder acceder a la oficina cerrada. Si aplicáramos el mismo horario
para todas las oficinas, le penalizaríamos, y claro, se quejará y con razón.

Este planteamiento también servirá para medir un servicio que ofrezcamos en nuestra empresa. Así
podremos discutir con nuestro cliente disponiendo de datos fiables.

Espero que hayais entendido la problemática.

Al tema:
En la versión anterior me hacía un lío, y al final era un engorro. En este caso, cambio totalmente
la perspectiva y la verdad, es que en pocas líneas queda solucionado elegantemente.

- necesitamos una tabla de horarios

Código SQL [-]
CREATE TABLE HORARIOS (
    ID      INTEGER NOT NULL,
    NOMBRE  VARCHAR(25)
);
INSERT INTO HORARIOS (ID, NOMBRE) VALUES (1, 'L_a_V');
INSERT INTO HORARIOS (ID, NOMBRE) VALUES (8, 'L_X_V');
INSERT INTO HORARIOS (ID, NOMBRE) VALUES (9, 'M_J');

- Necesitamos una tabla de detalle de los horarios. Para cada horario almacenaremos el día de la semana, el horario de ese día ( contempla mañana y tarde exclusivamente y las horas totales de cada día.
por ejemplo:
horario normal: de lunes a jueves de 8:00 a 17:00 y el viernes de 8:00 a 15:00
horario de lunes a miercoles de 8:00 a 13:30 y de 15:30 a 18:00 , jueves y viernes cerrado.
horario de jueves y viernes de 8:00 a 13:30 y de 15:30 a 18:00, lunes a viernes cerrado.
Si no abro a la tarde, pongo la misma hora que el fin de la mañana.
Para indicar los días que no abro, pongo la misma hora de inicio y de fin de apertura, y
las horas totales de apertura, a cero.

Código SQL [-]
CREATE TABLE DETALLE_HORARIOS (
    ID                INTEGER NOT NULL,
    IDHORARIO         INTEGER NOT NULL,  -> foreign key con la tabla horarios.ID
    DIASEMANA         VARCHAR(10) NOT NULL,
    HORARIO_INICIO_M  TIME NOT NULL,
    HORARIO_FIN_M     TIME NOT NULL,
    HORARIO_INICIO_T  TIME NOT NULL,
    HORARIO_FIN_T     TIME NOT NULL,
    HORAS_JORNADA     FLOAT NOT NULL
);
Sólo pongo un horario
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
         VALUES (1, 1, 'L', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
         VALUES (2, 1, 'M', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
         VALUES (3, 1, 'X', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (4, 1, 'J', '08:00:00', '14:00:00', '17:00:00', '20:00:00', 9);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (5, 1, 'V', '08:00:00', '14:00:00', '14:00:00', '14:00:00', 6);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (6, 1, 'S', '08:00:00', '08:00:00', '08:00:00', '08:00:00', 0);
INSERT INTO DETALLE_HORARIOS (ID, IDHORARIO, DIASEMANA, HORARIO_INICIO_M, HORARIO_FIN_M, HORARIO_INICIO_T, HORARIO_FIN_T, HORAS_JORNADA)
        VALUES (7, 1, 'D', '08:00:00', '08:00:00', '08:00:00', '08:00:00', 0);

- Necesitamos una tabla de festivos, ya que si es fiesta no podemos imputar las horas de ese día.
En ella incluiremos sólamente los días festivos del año. El día 1 de enero, el 6 de enero, ...

Código SQL [-]
CREATE TABLE FESTIVOS (
    DIA  DATE NOT NULL
);
Festivos del 2014
INSERT INTO FESTIVOS (DIA) VALUES ('2014-01-01');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-01-06');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-04-18');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-05-01');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-08-15');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-11-01');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-12-06');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-12-08');
INSERT INTO FESTIVOS (DIA) VALUES ('2014-12-25');

El truco del almendruco... es la store procedure con la que saco un registro por día desde que se abre el aviso hasta que se cierra. Esta tontada consigue que la lógica se simplifique de manera importante. Lo que hago simplemente es sacar un registro por cada día indicando además qué día de la semana es (lunes, martes...).

Esto me permite hacer un inner join con la tabla detalle_horarios por el campo "día de la semana" en la que consigo los siguientes datos:
dia, dia de la semana, horarios de mañana y tarde y horas totales.
Si además, dado que en la tabla de detalle_horarios tengo un campo que me indica las horas de apertura
diarias, con un simple filtro ( and horas_jornada <> 0), elimino los días que no son hábiles. Los que no trabajo entre semana y los que son fin de semana ( ya que los he incluido en la tabla ).

Si además meto en la coctelera la tabla de festivos, eliminando los días que puedan caer en festivo en el intervalo de esa tabla, ya sólo me quedan los días que debo tratar. Lo que antes para mí era un infierno calcular, ahora es una cosa "sencilla".

Código SQL [-]
CREATE OR ALTER PROCEDURE TABLA_DIAS (
    f_inicio date,
    f_fin date)
returns (
    fechasalida date,
    diasemana char(1))
as
declare variable fecha date;
begin
  /* Procedure Text */
  FECHA = f_inicio ;
  while  (FECHA <= F_FIN ) DO
  BEGIN
    FECHASALIDA=FECHA;
    DIASEMANA='-';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 1 ) then DIASEMANA='L';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 2 ) then DIASEMANA='M';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 3 ) then  DIASEMANA='X';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 4 ) then DIASEMANA='J';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 5 ) then DIASEMANA='V';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 6 ) then DIASEMANA='S';
    if (EXTRACT(weekday FROM FECHASALIDA ) = 0 ) then DIASEMANA='D';
    suspend;
    FECHA = FECHA+1;
  END

end

El último detalle es ver cómo lo calculo. Saco la consulta, y por cada registro, voy sumando los tiempos.
1.- si los días no son ni el primero ni el último, simplemente sumo las horas_jornada del día y a otra
cosa.
2. Si el día que trato es el primero, cojo la hora de inicio siempre y cuando no sea anterior a la apertura.
Si es anterior, lógicamente, pondré la hora de apertura.
3.- Si el día que trato es el último, y la hora de cierre del aviso es posterior a la hora de cierre, pongo
la hora de cierre de la avería como la hora de cierre de la oficina.
4.- Si el día de apertura y el de cierre no son el mismo, cojo según el caso la hora de inicio o la
hora de cierre de la oficina las horas de apertura y de cierre de la avería.
5.- Un detalle final. Si tenemos mañana y tarde, y la hora de inicio del aviso es por la mañana, y la
hora de cierre de la avería es por la tarde, le resto el tiempo del mediodía, ya que la oficina ha
estado cerrada y no puedo atender a los técnicos.
6. -Ah, se me olvidaba, si el tiempo es negativo por algún casual, no hago nada.

Código SQL [-]
CREATE OR ALTER PROCEDURE HORAS_CON_HORARIOS (
    horario varchar(25),
    f_inicio timestamp,
    f_fin timestamp)
returns (horas float)
as
declare variable wdiasemana char(1);
declare variable wdia date; /* dia tratado */
declare variable whi_m time; /* horario_inicio_Mañana */
declare variable whf_m time; /* horario_fin_Mañana */
declare variable whi_t time; /* horario_inicio_Tarde */
declare variable whf_t time; /* horario_fin_Tarde */
declare variable whoras_jornada float;
declare variable tiempototal float;
declare variable hora_inicio time;
declare variable hora_fin time;
begin
  /* Procedure Text */
TIEMPOTOTAL=0.0;
for  SELECT D.fechasalida AS DIA, D.DIASEMANA,
            DH.horario_inicio_m, DH.horario_fin_m,
            DH.horario_inicio_t, DH.horario_fin_t,
            DH.horas_jornada
            from tabla_dias(cast(:f_inicio as date),cast(:f_fin as date))  D
            inner join detalle_horarios DH on (DH.diasemana=D.DIASEMANA)
            inner join horarios H on (h.id = DH.idhorario)
            where
            /* cogemos el horario */
            h.nombre=:HORARIO
            /* los dias que no hay servicio no los cogemos. Festivos y horarios especiales */
            and DH.horas_jornada <> 0
            /* los festivos nacionales tampoco los cogemos */
            and D.fechasalida not in (select  DIA from FESTIVOS)
            order by D.fechasalida
            INTO
            :WDIA,:WDIASEMANA,:WHI_M,:WHF_M,:WHI_T,:WHF_T,:WHORAS_JORNADA
DO
  BEGIN
    if (WDIA=CAST(f_inicio AS DATE)) then
         HORA_INICIO = CAST( F_INICIO AS time);
    ELSE
         HORA_INICIO = WHI_M;
    if (wDIA=CAST(f_FIN AS DATE) ) then
         HORA_FIN = CAST( F_FIN AS time);
    ELSE
         HORA_FIN = WHF_T;
    /* los dias intermedios, simplemente sumo las horas totales */
    if (WDIA<>CAST(f_inicio AS DATE) AND WDIA <> CAST(F_FIN AS DATE)) then
       TIEMPOTOTAL=TIEMPOTOTAL+WHORAS_JORNADA;
    ELSE
       BEGIN
         if (HORA_INICIO < WHI_M) then
            HORA_INICIO = whi_m;
         if (HORA_INICIO >= whf_M AND HORA_INICIO < whi_t ) then
            HORA_INICIO = WHI_T;
         if (HORA_FIN > WHF_T) then
            HORA_FIN= WHF_T ;
         if (hora_fin >= WHF_M AND hora_fin < WHI_T ) then
            HORA_FIN= WHF_M;
         if (HORA_FIN-hora_inicio > 0) then
         BEGIN
            TIEMPOTOTAL=TIEMPOTOTAL+(HORA_FIN-hora_inicio );
            if ((HORA_INICIO >= whi_m AND hora_inicio < WHF_M)
               AND
               (hora_fin >= WHI_T AND HORA_FIN < WHF_T)) then
               TIEMPOTOTAL=TIEMPOTOTAL-(WHI_T-WHF_M);
          END
       END
  END
  HORAS=(TIEMPOTOTAL/60)/60;
suspend;
end

Llamadas a la store procedure:

Código SQL [-]
SELECT * FROM HORAS_CON_HORARIOS('L_a_V', '21-NOV-2014 13:30:00', '24-NOV-2014 08:00:00')

devuelve 0,5 (horas)

Código SQL [-]
SELECT * FROM HORAS_CON_HORARIOS('L_a_V', '21-NOV-2014 11:30:00', '24-NOV-2014 19:00:00')

Devuelve 8,5 ( horas)


Espero no haberos aburrido. La verdad es que es un tema que me ha dado muchos dolores de cabeza, y espero que no vuelva a tenerlos.
Respecto al trabajo con fechas, días treanscurridos, horas , etc, ese store procedure de los días es el que me ha hecho ver la luz.

Un saludo a todos.
Espero su retroalimentación...

[pacopenin]

. Gracias.