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;