Search code examples
sqlfirebirdfirebird2.5

Calculate time difference between two rows

I have tables SHIFT_LOG, SHIFT_LOG_DET & SHIFT_LOG_ENTRY having Parent-Child-GrandChild relationships (one-to-many). So,

  • LOG table contains shift details.
  • LOG_DET contains operators in a particular shift &
  • LOG_ENTRY table logs different entry types and timestamp for a user in a shift like (ADDED, STARTED, ON-BREAK, JOINED, ENDED).

For a given shift I can get all operators, and their entries using below query. What I can't do is to find the duration an operator spent on a particular entry type. i.e difference between two rows ENTRY_TIME.

SELECT 
    ent.ID as ENT_ID,
    det.ID as DET_ID,
    usr.CODE as USR_ID,
    ent.SHIFT_LOG_DET_ID,
    ent.ENTRY_TYPE, 
    IIF(ent.ENTRY_TYPE = 0 , 'ADDED', 
        IIF(ent.ENTRY_TYPE = 1 , 'STARTED', 
        IIF(ent.ENTRY_TYPE = 2 , 'ON-BREAK', 
        IIF(ent.ENTRY_TYPE = 3 , 'JOINED', 
        IIF(ent.ENTRY_TYPE = 4 , 'ENDED', 'UNKNOWN ENTRY'))))) as ENTRY_TYPE_VALUE, 
    ent.ENTRY_TIME+cast('31.12.1899' as timestamp) as ENTRY_TIME
FROM SHIFT_LOG_ENTRY ent
LEFT JOIN SHIFT_LOG_DET det on det.ID = ent.SHIFT_LOG_DET_ID
LEFT JOIN SHIFT_LOG log on log.ID = det.SHIFT_LOG_ID
LEFT JOIN USERS usr on usr.USERID = det.OPERATOR_ID
WHERE log.ID = 1

GROUP BY 
usr.CODE,
ent.SHIFT_LOG_DET_ID,
det.ID,
ent.ID,
ENTRY_TYPE_VALUE,
ent.ENTRY_TIME,
ent.ENTRY_TYPE

Result Set:

enter image description here

So Interval is the time spent in secs on a particular ENTRY_TYPE. i.e

ROW(1).Interval = ( Row(2).EntryTime - Row(1).EntryTime )

Entry type ENDED has no interval as there is no other entry for the user after the shift has ended.

Firebird version is 2.5.3

Solution

  • You will need to select the next date from the relevant entries. You can do this using something like:

    select
  SHIFT_LOG_DET_ID,
  ENTRY_TIME,
  datediff(minute from ENTRY_TIME to NEXT_ENTRY_TIME) as DURATION
from (
  select 
    a.SHIFT_LOG_DET_ID,
    a.ENTRY_TIME, 
    (select min(ENTRY_TIME) 
     from SHIFT_LOG_ENTRY 
     where SHIFT_LOG_DET_ID = a.SHIFT_LOG_DET_ID
     and ENTRY_TIME > a.ENTRY_TIME) as NEXT_ENTRY_TIME
  from SHIFT_LOG_ENTRY a
) b

    See also this fiddle.

    In Firebird 3, you can use the window function LEAD to achieve this:

    select
  SHIFT_LOG_DET_ID,
  ENTRY_TIME,
  datediff(minute from ENTRY_TIME 
      to lead(ENTRY_TIME) over (partition by SHIFT_LOG_DET_ID order by ENTRY_TIME)) as DURATION
from SHIFT_LOG_ENTRY

    Full solution

    This solution was contributed by AlphaTry

    select
    ENT_ID,
    DET_ID,
    USR_CODE,
    SHIFT_LOG_DET_ID,
    ENTRY_TYPE, 
    ENTRY_TYPE_VALUE,
    ENTRY_TIME,
    datediff(second from ENTRY_TIME to NEXT_ENTRY_TIME) as DURATION
from (
    SELECT 
        ent.ID as ENT_ID,
        det.ID as DET_ID,
        usr.CODE as USR_CODE,
        ent.SHIFT_LOG_DET_ID,
        ent.ENTRY_TYPE as ENTRY_TYPE, 
        case (ent.ENTRY_TYPE)
        when '0' then 'ADDED'
        when '1' then 'STARTED'
        when '2' then 'ON-BREAK'
        when '3' then 'JOINED'
        when '4' then 'ENDED'
        else 'UNKNOWN ENTRY'
        end as ENTRY_TYPE_VALUE,
        ent.ENTRY_TIME+cast('31.12.1899' as timestamp) as ENTRY_TIME, 
        (
            select min(ENTRY_TIME) 
            from SHIFT_LOG_ENTRY 
            where SHIFT_LOG_DET_ID = ent.SHIFT_LOG_DET_ID
            and ENTRY_TIME > ent.ENTRY_TIME
            
        )+cast('31.12.1899' as timestamp) as NEXT_ENTRY_TIME
    FROM SHIFT_LOG_ENTRY ent
    LEFT JOIN SHIFT_LOG_DET det on det.ID = ent.SHIFT_LOG_DET_ID
    LEFT JOIN SHIFT_LOG log on log.ID = det.SHIFT_LOG_ID
    LEFT JOIN USERS usr on usr.USERID = det.OPERATOR_ID
    WHERE log.ID = 1
    GROUP BY 
    usr.CODE,
    ent.SHIFT_LOG_DET_ID,
    det.ID,
    ent.ID,
    ENTRY_TYPE_VALUE,
    ent.ENTRY_TIME,
    ent.ENTRY_TYPE
) b

    Result

    enter image description here