Calculate duration time - Firebird 2.5

Asked

Viewed 302 times

-1

I have the following table: tabela

Stages: 0 = Separation, 1 = Packaging, 2 = Conference, 3 = Shipment

Status: 0 = Waiting, 1 = Started, 2 = Finished, 3 = Paused

And this table is part of this screen:

tela

For example, analyzing the image below, we note that the stage of Separation is waiting from 15:06 and finished at 16:16. Thus, the duration of this stage was 1:10 min:

separacao

.

I need to calculate the duration of each stage regardless of status, but I don’t know how. (Aware that I will have to create a temporary view for each step)

Is it possible to do this? If so, how?

select firebird time

  • Make the difference between the result of the MAX and MIN aggregation functions over the date/time and GROUP BY clause per step?

  • makes no difference, I got the solution to this problem.

  • Difference, which I mentioned above, is the subtraction operation.

1 answer

1


I managed to solve by turning time of occurrences into minutes :

  with vwEtapas as
(

select    a.idf_processoexp,
          a.etapa,
          a.status,
          b.IDF_IDENT ididentusu,
         sum(
              (CAST(SUBSTRING(a.horaoco FROM 1 FOR 2) AS INTEGER) * 60)  +
              (CAST(SUBSTRING(a.horaoco FROM 4 FOR 2) AS INTEGER))
              )
           HorasEmMinutos

FROM       M3_OCOPROCEXP   A
inner join MTMG_USUA B on B.ID = A.IDF_USUARIO

group by a.idf_processoexp, a.status, a.etapa, b.IDF_IDENT
order by a.idf_processoexp, a.etapa, a.status

)


, vwEtapasClassifica as (

select wep2.idf_processoexp,
       wep2.ididentusu,
       /* Horas Separação */

       case when wep2.etapa = 0 then
            case  when wep2.status = 1 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasInicioSeparacao,

        case when wep2.etapa = 0 then
            case  when wep2.status = 2 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasConcluidoSeparacao,

       case when wep2.etapa = 0 then
            case  when wep2.status = 4 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasPausaSeparacao,


       /* Horas Embalagem */

       case when wep2.etapa = 1 then
            case  when wep2.status = 1 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasInicioEmbalagem,

        case when wep2.etapa = 1 then
            case  when wep2.status = 2 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasConcluidoEmbalagem,

       case when wep2.etapa = 1 then
            case  when wep2.status = 3 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasPausaEmbalagem,

       /* Horas Conferência */

       case when wep2.etapa = 2 then
            case  when wep2.status = 1 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasInicioConferencia,

       case when wep2.etapa = 2 then
            case  when wep2.status = 2 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasConcluidoConferencia,

         case when wep2.etapa = 2 then
            case  when wep2.status = 2 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end HorasPausaConferencia,

       /* Horas Embarque */

        case when wep2.etapa = 3 then
            case  when wep2.status = 1 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end  HorasInicioEmbarque,

        case when wep2.etapa = 3 then
            case  when wep2.status = 2 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end  HorasConcluidoEmbarque,

        case when wep2.etapa = 3 then
            case  when wep2.status = 2 then
                       wep2.HorasEmMinutos
            else 0 end
       else 0 end  HorasPausaEmbarque

FROM    vwEtapas   wep2

)


, vwEtapasSUM as   (

select    wep3.idf_processoexp,
          wep3.ididentusu,
       sum(wep3.HorasInicioSeparacao)    HorasInicioSeparacao,

       sum(wep3.HorasConcluidoSeparacao) HorasConcluidoSeparacao,

       sum(wep3.HorasInicioEmbalagem)   HorasInicioEmbalagem,

       sum(wep3.HorasInicioConferencia) HorasInicioConferencia,

       sum(wep3.HorasInicioEmbarque)    HorasInicioEmbarque,

       sum(wep3.HorasConcluidoEmbalagem) HorasConcluidoEmbalagem,

       sum(wep3.HorasConcluidoConferencia) HorasConcluidoConferencia,

       sum(wep3.HorasConcluidoEmbarque) HorasConcluidoEmbarque,

       sum(wep3.HorasPausaSeparacao)    HorasPausaSeparacao,

       sum(wep3.HorasPausaEmbalagem) HorasPausaEmbalagem,

       sum(wep3.HorasPausaConferencia) HorasPausaConferencia,

       sum(wep3.HorasPausaEmbarque) HorasPausaEmbarque

FROM    vwEtapasClassifica   wep3

group by wep3.idf_processoexp, wep3.ididentusu

)

select
       doc.nrodocto,
       idusu.nome,
      (vwe.HorasConcluidoSeparacao - vwe.HorasInicioSeparacao - vwe.HorasPausaSeparacao) TempoSeparacao,
      (vwe.HorasConcluidoEmbalagem - vwe.HorasInicioEmbalagem - vwe.HorasPausaEmbalagem) TempoEmbalagem,
      (vwe.HorasConcluidoConferencia - vwe.HorasInicioConferencia - vwe.HorasPausaConferencia) TempoCoferencia,
      (vwe.HorasConcluidoEmbarque - vwe.HorasInicioEmbarque - vwe.HorasPausaEmbarque) TempoEmbarque

FROM        m3_procexpedi   d
inner join  m3_integraexp   ite   on ite.id = d.idf_integraexp
inner join  m3_integra      itg   on itg.id = ite.ida
inner join  m3_docto        doc   on doc.id = itg.idf_docto
inner join  vwEtapasSUM     vwe   on vwe.idf_processoexp = d.id
inner join MTMG_IDENT       idusu on idusu.ID = vwe.ididentusu

Browser other questions tagged select firebird time

You are not signed in. Login or sign up in order to post.