I have this i/p table

I want this output

i tried with cases but not helping

CodePudding user response：

In Oracle, you can use MATCH_RECOGNIZE to do row-by-row processing:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY event_time
  MEASURES
    FIRST(event_time)       AS login_time,
    LAST(event_time)        AS logout_time,
    COUNT(on_status.status) AS count_of_on
  PATTERN (on_status  (off_status | $))
  DEFINE
    on_status AS status  = 'on',
    off_status AS status = 'off'
)

Or, in both MySQL and Oracle, you can use analytic functions:

SELECT MIN(event_time) AS login_time,
       MAX(next_time)  AS logout_time,
       COUNT(*)        AS count_of_on
FROM   (
  SELECT t.*,
         LEAD(event_time, 1, event_time) OVER (ORDER BY event_time)
           AS next_time,
         ROW_NUMBER() OVER (ORDER BY event_time)
           - ROW_NUMBER() OVER (PARTITION BY status ORDER BY event_time) AS grp
  FROM   table_name t
) t
WHERE  status = 'on'
GROUP BY grp;

Which, for the sample data:

CREATE TABLE table_name (event_time, status) AS
SELECT '10:01', 'on'  FROM DUAL UNION ALL
SELECT '10:02', 'on'  FROM DUAL UNION ALL
SELECT '10:03', 'on'  FROM DUAL UNION ALL
SELECT '10:04', 'off' FROM DUAL UNION ALL
SELECT '10:07', 'on'  FROM DUAL UNION ALL
SELECT '10:08', 'on'  FROM DUAL UNION ALL
SELECT '10:09', 'off' FROM DUAL UNION ALL
SELECT '10:11', 'on'  FROM DUAL UNION ALL
SELECT '10:12', 'off' FROM DUAL;

Both output:

LOGIN_TIME	LOGOUT_TIME	COUNT_OF_ON
10:01	10:04	3
10:07	10:09	2
10:11	10:12	1

db<>fiddle Oracle MySQL