How do I aggregate from multiple rows-CodePudding

I have the below table:

Person_ID   | Timestamp           | Action
1           | 01-01-2020 00:01:00 | LOGGED ON
1           | 01-01-2020 00:02:00 | ON BREAK
1           | 01-01-2020 00:02:30 | OFF BREAK
1           | 01-01-2020 00:04:00 | LOGGED OFF
1           | 01-01-2020 00:04:30 | LOGGED ON
1           | 01-01-2020 00:05:30 | ON BREAK
1           | 01-01-2020 00:06:00 | OFF BREAK
1           | 01-01-2020 00:08:00 | LOGGED OFF
2           | 01-02-2020 00:04:00 | LOGGED ON
2           | 01-02-2020 00:10:00 | LOGGED OFF

My goal is to write an SQL view to get the below result:

Person_ID   | LoggedInTime_hours | OnBreakTime_hours
1           | 6.5                | 1 
2           | 6                  | 0

For each Person_ID, calculate the difference in hours between each pair of occurrences of LOGGED IN/LOGGED OUT and ON BREAK/OFF BREAK respectively.

Thanks a lot for your expertise and help.

CodePudding user response：

1st of all LoggedInTime_hours for PERSON_ID = 1 should be 5.5 hours not 6.5 hours.

Let us assume the table name is TEST.

Database : MySQL

SELECT
PERSON_ID,
IFNULL(SUM(CASE WHEN PREV_ACTIVE_IND - ACTIVE_IND > 0 THEN TIMESTAMPDIFF(SECOND, PREV_TIMESTAMP, TIMESTAMP)/3600 END),0) LoggedInTime_hours,
IFNULL(SUM(CASE WHEN PREV_ACTIVE_IND - ACTIVE_IND = 0 THEN TIMESTAMPDIFF(SECOND, PREV_TIMESTAMP, TIMESTAMP)/3600 END),0) OnBreakTime_hours
FROM
(SELECT
PERSON_ID,
PREV_ACTION,
ACTION,
CASE WHEN PREV_ACTION = 'LOGGED OFF' THEN 1 
WHEN PREV_ACTION LIKE '%BREAK' THEN 2 
WHEN PREV_ACTION IS NULL THEN NULL 
ELSE 3 
END AS PREV_ACTIVE_IND,
CASE WHEN ACTION = 'LOGGED OFF' THEN 1
WHEN ACTION LIKE '%BREAK' THEN 2
WHEN ACTION IS NULL THEN NULL
ELSE 3 
END AS ACTIVE_IND,
PREV_TIMESTAMP,
TIMESTAMP
FROM
(SELECT PERSON_ID,
TIMESTAMP,
LAG(TIMESTAMP,1) OVER (PARTITION BY PERSON_ID) PREV_TIMESTAMP,
LAG(ACTION,1) OVER (PARTITION BY PERSON_ID) PREV_ACTION,
ACTION
FROM TEST) T) T2
GROUP BY PERSON_ID

Hope this query will help.

CodePudding user response：

Try the following query using MySQL v8.0:

With CTE As
(
  Select Person_ID, Action_,
  TIME_TO_SEC(TIMEDIFF(Lead(Timestamp_) Over (Partition By Person_ID Order By Timestamp_), Timestamp_))/60 As DIFF
  From MyData Where Action_ In ('LOGGED ON','LOGGED OFF')
  Union All
  Select Person_ID,Action_,
  TIME_TO_SEC(TIMEDIFF(Lead(Timestamp_) Over (Partition By Person_ID Order By Timestamp_), Timestamp_))/60 As DIFF
  From MyData Where Action_ In ('ON BREAK','OFF BREAK')
)

Select Person_ID, 
Sum(Case When Action_='LOGGED ON' Then DIFF Else 0 End) As LoggedInTime_hours,
Sum(Case When Action_='ON BREAK' Then DIFF Else 0 End) As OnBreakTime_hours 
From CTE
Group By Person_ID

See a demo from db-fiddle.