Forum Stats

  • 3,874,655 Users
  • 2,266,761 Discussions
  • 7,911,929 Comments

Discussions

How to combine the break time intervals into continues time period?

User_8XMDD
User_8XMDD Member Posts: 3 Green Ribbon

Hi All,

This is my input data. Here the 1st logout time is equal to 2nd login time.

LogInTime       LogOutTime   

4/15/2022 15:32     4/15/2022 16:28   

4/15/2022 16:28     4/15/2022 16:58

4/15/2022 16:58    4/15/2022 17:20---> Break Time

4/15/2022 17:20   4/15/2022 18:42

4/15/2022 18:42   4/15/2022 19:13

4/15/2022 19:13   4/15/2022 22:56


I want to combine the data like this,

4/15/2022 15:32 4/15/2022 16:58

4/15/2022 16:58  4/15/2022 17:20---> Break Time

4/15/2022 17:20 4/15/2022 22:56


And i need a one more column which as Total Login Time (in seconds) for adding up the combined time intervals.


Thanks in advance.

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond
    Answer ✓

    Example, assuming the breaktime is indicated in the data... and using basic functionality that should work in earlier versions of the database...

    SQL> with t(LogInTime,LogOutTime,BreakTime) as (
      2    select to_date('4/15/2022 15:32','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      3    select to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      4    select to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), 'Y' from dual union all
      5    select to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      6    select to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      7    select to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 22:56','MM/DD/YYYY HH24:MI'), 'N' from dual
      8    )
      9   ,ch as (-- detect changes
     10    select LogInTime, LogOutTime, BreakTime
     11          ,case when nvl(lag(LogOutTime) over (order by LoginTime),LogInTime) = LogInTime
     12                 and nvl(lag(BreakTime) over (order by LoginTime),BreakTime) = BreakTime
     13           then 0
     14           else 1
     15           end as chg
     16    from   t
     17    )
     18   ,grp as (-- create groups
     19    select LogInTime, LogOutTime, BreakTime
     20          ,sum(chg) over (order by LogInTime) as grp
     21    from   ch
     22    )
     23  select min(LogInTime) as LogInTime
     24        ,max(LogOutTime) as LogOutTime
     25        ,max(BreakTime) as BreakTime
     26  from   grp
     27  group by grp
     28  order by LogInTime
     29  /
    
    
    LOGINTIME            LOGOUTTIME           B
    -------------------- -------------------- -
    15-APR-2022 15:32:00 15-APR-2022 16:58:00 N
    15-APR-2022 16:58:00 15-APR-2022 17:20:00 Y
    15-APR-2022 17:20:00 15-APR-2022 22:56:00 N
    


    If you've got a more modern version there are other techniques that can be used too.

    User_8XMDD
«1

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond

    Why is your expected output as you show it?

    Given 2 columns with date/time ranges as in your example data, all of those times could be combined to a single result of 4/15/2022 15:32 to 4/15/2022 22:56.

    Are you saying the "---> Break Time" is actually some data in your table and not just some comment you've added on yourself, and that the times should combine around that? If so, you should be clear in your example data as it looks as though it only has two columns.

    Please post example data that people can use e.g. create table and insert statements, or a WITH statement with the data. It's harder for people to answer you if they can't just copy/paste the data to their database and use it.

    Also, indicate the full version of your Oracle database, as different answers can be used depending on your version.

    Frank KulashUser_8XMDD
  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond
    Answer ✓

    Example, assuming the breaktime is indicated in the data... and using basic functionality that should work in earlier versions of the database...

    SQL> with t(LogInTime,LogOutTime,BreakTime) as (
      2    select to_date('4/15/2022 15:32','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      3    select to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      4    select to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), 'Y' from dual union all
      5    select to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      6    select to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      7    select to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 22:56','MM/DD/YYYY HH24:MI'), 'N' from dual
      8    )
      9   ,ch as (-- detect changes
     10    select LogInTime, LogOutTime, BreakTime
     11          ,case when nvl(lag(LogOutTime) over (order by LoginTime),LogInTime) = LogInTime
     12                 and nvl(lag(BreakTime) over (order by LoginTime),BreakTime) = BreakTime
     13           then 0
     14           else 1
     15           end as chg
     16    from   t
     17    )
     18   ,grp as (-- create groups
     19    select LogInTime, LogOutTime, BreakTime
     20          ,sum(chg) over (order by LogInTime) as grp
     21    from   ch
     22    )
     23  select min(LogInTime) as LogInTime
     24        ,max(LogOutTime) as LogOutTime
     25        ,max(BreakTime) as BreakTime
     26  from   grp
     27  group by grp
     28  order by LogInTime
     29  /
    
    
    LOGINTIME            LOGOUTTIME           B
    -------------------- -------------------- -
    15-APR-2022 15:32:00 15-APR-2022 16:58:00 N
    15-APR-2022 16:58:00 15-APR-2022 17:20:00 Y
    15-APR-2022 17:20:00 15-APR-2022 22:56:00 N
    


    If you've got a more modern version there are other techniques that can be used too.

    User_8XMDD
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,757 Red Diamond

    Hi, @User_8XMDD

    MATCH_RECOGNIXE is good for problems like this, assuming you're using Oracle 12.1 or higher. Here's one way to do it:

    SELECT	 *
    FROM	 t
    MATCH_RECOGNIZE
    	 (
    	   PARTITION BY breaktime
    	   ORDER BY     logintime
    	   MEASURES	MIN (logintime)  AS session_logintime
    	   ,		MAX (logouttime)  AS session_logouttime
    	   ONE ROW PER MATCH
    	   PATTERN      ( frst same* )
    	   DEFINE	same  AS  logintime = PREV (logouttime)
    	 )
    ORDER BY session_logintime -- or whatever you want
    ;
    


  • mathguy
    mathguy Member Posts: 10,902 Black Diamond

    First, obviously, please answer BluShadow's question. I strongly doubt that you have an extra "comment" column with the marking "break time"; rather, you probably didn't mean to have that row in the INPUTS. The inputs should only show login time and logout time; "break time" should be identified by the query, not already present in the input.

    Do you have this data just for one user, or do you have multiple users, and you must do this type of computation separately for each user, but in a single query? (Very likely!) If so, then include data for at least two distinct users, to make sure the solutions you receive work for your real-life data. Obviously, this means including a USER_ID column in the input.

    Just out of curiosity, if your login and logout times are expressed only down to the minute (no seconds component), why do you need the output to show "combined" login time in seconds? Why not in minutes? That doesn't make a lot of sense. (Or does your data actually also have "seconds" components? - in which case, why doesn't your sample data posted above show the same?)

    Also out of curiosity - you say "combine the break time intervals" in your title; but obviously that isn't what you are asking. You want to combine the login intervals, not the break intervals.

    User_8XMDD
  • User_8XMDD
    User_8XMDD Member Posts: 3 Green Ribbon
    edited Jul 19, 2022 5:20AM

    Thanks for the comment @BluShadow

    My Oracle database version is --> Oracle Database 11g Express Edition   11.2.0.2.0

    In actual case I have some more columns, for question I shorted the table.

    And your query is working fine. In addition to that, I need a one more column as mentioned in my question.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond


    So, to determine your extra columns, you just need to work out the interval between each date/time (out and in times) which gives you the interval in days, and multiply that so you have the result in seconds...

    You didn't make it clear if this "duration" should be for each grouping or an overall total... so I've done both...

    SQL> with t(LogInTime,LogOutTime,BreakTime) as (
      2    select to_date('4/15/2022 15:32','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      3    select to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      4    select to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), 'Y' from dual union all
      5    select to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      6    select to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      7    select to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 22:56','MM/DD/YYYY HH24:MI'), 'N' from dual
      8    )
      9   ,ch as (-- detect changes
     10    select LogInTime, LogOutTime, BreakTime
     11          ,case when nvl(lag(LogOutTime) over (order by LoginTime),LogInTime) = LogInTime
     12                 and nvl(lag(BreakTime) over (order by LoginTime),BreakTime) = BreakTime
     13           then 0
     14           else 1
     15           end as chg
     16    from   t
     17    )
     18   ,grp as (-- create groups
     19    select LogInTime, LogOutTime, BreakTime
     20          ,sum(chg) over (order by LogInTime) as grp
     21    from   ch
     22    )
     23   ,agg as (-- aggregate times in group
     24    select min(LogInTime) as LogInTime
     25          ,max(LogOutTime) as LogOutTime
     26          ,max(BreakTime) as BreakTime
     27    from   grp
     28    group by grp
     29    )
     30  select LogInTime
     31        ,LogOutTime
     32        ,BreakTime
     33        ,(LogOutTime-LogInTime)*(24*60*60) as dur_seconds
     34        ,sum((LogOutTime-LogInTime)*(24*60*60)) over () as overall_dur_seconds
     35  from   agg
     36  order by LogInTime
     37  /
    
    
    LOGINTIME            LOGOUTTIME           B DUR_SECONDS OVERALL_DUR_SECONDS
    -------------------- -------------------- - ----------- -------------------
    15-APR-2022 15:32:00 15-APR-2022 16:58:00 N        5160               26640
    15-APR-2022 16:58:00 15-APR-2022 17:20:00 Y        1320               26640
    15-APR-2022 17:20:00 15-APR-2022 22:56:00 N       20160               26640
    
    
    


    User_8XMDD
  • BluShadow
    BluShadow Member, Moderator Posts: 42,566 Red Diamond

    @mathguy

    First, obviously, please answer BluShadow's question. I strongly doubt that you have an extra "comment" column with the marking "break time"; rather, you probably didn't mean to have that row in the INPUTS. The inputs should only show login time and logout time; "break time" should be identified by the query, not already present in the input.

    Based on the given date/times only, there would be no way for the query to determine where there is a "break". The given date/times are all contiguous, so there's no indication of any break in them. Hence there must be something else that indicates when the break takes place.

    JackK
  • Stax
    Stax Member Posts: 184 Silver Badge
    SQL> ed
    Wrote file afiedt.buf
    
    
      1   with t(LogInTime,LogOutTime,BreakTime) as (
      2      select to_date('4/15/2022 15:32','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      3      select to_date('4/15/2022 16:28','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      4      select to_date('4/15/2022 16:58','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), 'Y' from dual union all
      5      select to_date('4/15/2022 17:20','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      6      select to_date('4/15/2022 18:42','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), 'N' from dual union all
      7      select to_date('4/15/2022 19:13','MM/DD/YYYY HH24:MI'), to_date('4/15/2022 22:56','MM/DD/YYYY HH24:MI'), 'N' from dual
      8      )
      9  ,tt as (
     10  select
     11    t.*
     12  ,LogOutTime-sum(LogOutTime-LogInTime) over (partition by BreakTime order by LogInTime) gr
     13  from t
     14  )
     15  select
     16    min(LogInTime) l_in
     17   ,max(LogOutTime) t_out
     18   ,BreakTime
     19  from tt
     20  group by BreakTime, gr
     21* order by 1
    SQL> /
    
    
    L_IN           T_OUT          B
    -------------- -------------- -
    04/15/22 15:32 04/15/22 16:58 N
    04/15/22 16:58 04/15/22 17:20 Y
    04/15/22 17:20 04/15/22 22:56 N
    
    
    SQL>
    
  • mathguy
    mathguy Member Posts: 10,902 Black Diamond

    @BluShadow

    Based on the given date/times only, there would be no way for the query to determine where there is a "break"

    Of course there would. What you wrote suggest that you didn't read what I said closely enough, or you missed the significance of the word "row": rather, you probably didn't mean to have that row in the INPUTS.

    The row where the OP added the "Break Time" comment should not exist in the inputs. I didn't just mean "the comments shouldn't exist in the inputs"; the "Break Time" rows shouldn't exist in the inputs either. in their entirety.

    Think about how the raw data comes into existence in the first place. The user logs in, a new row is inserted in the table, with the user id and the login time. The user logs out, the same row is updated to add the logout time in a different column. Or something similar.

    How do rows with "Break Time" markings come into being in the raw data, in your interpretation of the problem? It can be done, no doubt; but how much sense does that make?