Dernis
Reputation: 3
SQL-query - Summarize logon time. Result as rows for each session
I'm having a hard time creating a nifty SQL-query for this.
[Background]
A server application is tracking when the client application connects to the server. As long as the server can communicate with the client application it continually writes "logon" rows into a table every five minutes (0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55).
[Sample data]
AgentID Action Start Stop Duration 10 Logon 2016-12-08 13:07:11.857 2016-12-08 13:10:00.000 168,145 First "Logon" session this day. 10 Logon 2016-12-08 13:10:00.000 2016-12-08 13:15:00.000 300 10 Logon 2016-12-08 13:15:00.000 2016-12-08 13:20:00.000 300 10 Logon 2016-12-08 13:20:00.000 2016-12-08 13:25:00.000 300 10 Logon 2016-12-08 13:25:00.000 2016-12-08 13:30:00.000 300 10 Logon 2016-12-08 13:30:00.000 2016-12-08 13:35:00.000 300 10 Logon 2016-12-08 13:35:00.000 2016-12-08 13:40:00.000 300 10 Logon 2016-12-08 13:40:00.000 2016-12-08 13:45:00.000 300 10 Logon 2016-12-08 13:45:00.000 2016-12-08 13:50:00.000 300 10 Logon 2016-12-08 13:50:00.000 2016-12-08 13:55:00.000 300 10 Logon 2016-12-08 13:55:00.000 2016-12-08 13:58:01.117 181,1148155 End session 10 Logon 2016-12-08 13:58:56.337 2016-12-08 14:00:00.000 63,662 Second "Logon" session this day. 10 Logon 2016-12-08 14:00:00.000 2016-12-08 14:05:00.000 300 10 Logon 2016-12-08 14:05:00.000 2016-12-08 14:10:00.000 300 10 Logon 2016-12-08 14:10:00.000 2016-12-08 14:15:00.000 300 10 Logon 2016-12-08 14:15:00.000 2016-12-08 14:20:00.000 300 10 Logon 2016-12-08 14:20:00.000 2016-12-08 14:25:00.000 300 10 Logon 2016-12-08 14:25:00.000 2016-12-08 14:30:00.000 300 10 Logon 2016-12-08 14:30:00.000 2016-12-08 14:35:00.000 300 10 Logon 2016-12-08 14:35:00.000 2016-12-08 14:40:00.000 300 10 Logon 2016-12-08 14:40:00.000 2016-12-08 14:45:00.000 300 10 Logon 2016-12-08 14:45:00.000 2016-12-08 14:50:00.000 300 10 Logon 2016-12-08 14:50:00.000 2016-12-08 14:55:00.000 300 10 Logon 2016-12-08 14:55:00.000 2016-12-08 15:00:00.000 300 10 Logon 2016-12-08 15:00:00.000 2016-12-08 15:05:00.000 300 10 Logon 2016-12-08 15:05:00.000 2016-12-08 15:10:00.000 300 10 Logon 2016-12-08 15:10:00.000 2016-12-08 15:15:00.000 300 10 Logon 2016-12-08 15:15:00.000 2016-12-08 15:20:00.000 300 10 Logon 2016-12-08 15:20:00.000 2016-12-08 15:25:00.000 300 10 Logon 2016-12-08 15:25:00.000 2016-12-08 15:30:00.000 300 10 Logon 2016-12-08 15:30:00.000 2016-12-08 15:35:00.000 300 10 Logon 2016-12-08 15:35:00.000 2016-12-08 15:40:00.000 300 10 Logon 2016-12-08 15:40:00.000 2016-12-08 15:45:00.000 300 10 Logon 2016-12-08 15:45:00.000 2016-12-08 15:50:00.000 300 10 Logon 2016-12-08 15:50:00.000 2016-12-08 15:55:00.000 300 10 Logon 2016-12-08 15:55:00.000 2016-12-08 16:00:00.000 300 10 Logon 2016-12-08 16:00:00.000 2016-12-08 16:05:00.000 300 10 Logon 2016-12-08 16:05:00.000 2016-12-08 16:10:00.000 300 10 Logon 2016-12-08 16:10:00.000 2016-12-08 16:15:00.000 300 10 Logon 2016-12-08 16:15:00.000 2016-12-08 16:20:00.000 300 10 Logon 2016-12-08 16:20:00.000 2016-12-08 16:25:00.000 300 10 Logon 2016-12-08 16:25:00.000 2016-12-08 16:30:00.000 300 10 Logon 2016-12-08 16:30:00.000 2016-12-08 16:35:00.000 300 10 Logon 2016-12-08 16:35:00.000 2016-12-08 16:40:00.000 300 10 Logon 2016-12-08 16:40:00.000 2016-12-08 16:45:00.000 300 10 Logon 2016-12-08 16:45:00.000 2016-12-08 16:50:00.000 300 10 Logon 2016-12-08 16:50:00.000 2016-12-08 16:55:00.000 300 10 Logon 2016-12-08 16:55:00.000 2016-12-08 17:00:00.000 300 10 Logon 2016-12-08 17:00:00.000 2016-12-08 17:05:00.000 300 10 Logon 2016-12-08 17:05:00.000 2016-12-08 17:10:00.000 300 10 Logon 2016-12-08 17:10:00.000 2016-12-08 17:15:00.000 300 10 Logon 2016-12-08 17:15:00.000 2016-12-08 17:19:19.277 259,2776235 End session
[Expected result table from SQL-query]
AgentID Action Start Stop Duration 10 Logon 2016-12-08 13:07:11.857 2016-12-08 13:58:01.117 sum in seconds 10 Logon 2016-12-08 13:58:56.337 2016-12-08 17:19:19.277 sum in seconds
Upvotes: 0
Views: 68
Answers (1)
Boneist
Reputation: 23588
Here's one way:
WITH sample_data AS (select 10 agentid, 'Logon' action, to_date('08/12/2016 13:07:12', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:10:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 168.145 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:10:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:15:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:15:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:20:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:20:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:25:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:25:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:30:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:30:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:35:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:35:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:40:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:40:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:45:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:45:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:50:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:50:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:55:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:55:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 13:58:01', 'dd/mm/yyyy hh24:mi:ss') stop_date, 181.1148155 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 13:58:56', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:00:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 63.662 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:00:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:05:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:05:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:10:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:10:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:15:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:15:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:20:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:20:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:25:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:25:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:30:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:30:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:35:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:35:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:40:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:40:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:45:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:45:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:50:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:50:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 14:55:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 14:55:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:00:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:00:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:05:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:05:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:10:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:10:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:15:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:15:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:20:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:20:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:25:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:25:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:30:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:30:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:35:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:35:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:40:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:40:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:45:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:45:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:50:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:50:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 15:55:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 15:55:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:00:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:00:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:05:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:05:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:10:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:10:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:15:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:15:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:20:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:20:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:25:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:25:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:30:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:30:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:35:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:35:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:40:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:40:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:45:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:45:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:50:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:50:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 16:55:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 16:55:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 17:00:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 17:00:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 17:05:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 17:05:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 17:10:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 17:10:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 17:15:00', 'dd/mm/yyyy hh24:mi:ss') stop_date, 300 duration from dual union all
select 10 agentid, 'Logon' action, to_date('08/12/2016 17:15:00', 'dd/mm/yyyy hh24:mi:ss') start_date, to_date('08/12/2016 17:19:19', 'dd/mm/yyyy hh24:mi:ss') stop_date, 259.2776235 duration from dual)
-- end of mimicking a table called "sample_data" with data in it.
-- see the following SQL for the main logic:
SELECT agentid,
action,
MIN(start_date) start_period,
MAX(stop_date) end_period,
SUM(duration) sum_in_seconds
FROM (SELECT agentid,
action,
start_date,
stop_date,
duration,
SUM(prep_for_grp_col) OVER (PARTITION BY agentid ORDER BY start_date, stop_date) grp
FROM (SELECT agentid,
action,
start_date,
stop_date,
duration,
CASE WHEN start_date != LAG(stop_date, 1, start_date - 1) OVER (PARTITION BY agentid ORDER BY start_date, stop_date) THEN 1
END prep_for_grp_col
FROM sample_data
WHERE action = 'Logon'))
GROUP BY agentid, action, grp;
Results:
AGENTID ACTION START_PERIOD END_PERIOD SUM_IN_SECONDS
---------- ------ ------------------- ------------------- --------------
10 Logon 08/12/2016 13:58:56 08/12/2016 17:19:19 12022.9396235
10 Logon 08/12/2016 13:07:12 08/12/2016 13:58:01 3049.2598155
It first of all works out the start of the groups, by deciding whether the previous row's end date is the same as the current row's start date (using the LAG() analytic function).
Then it's a matter of doing a running total on that column (using the SUM() analytic function) which then labels the rows with their group - every time a new period starts, this number will increase by one.
Then it's a simple matter of doing a group over the rows to find the min/max times and the sum of the duration per each group.
Upvotes: 1
Related Questions
- Total Time Logged Into the Program SQL
- How to count users logged with duration per date and 15 min interval
- SQL Server - Display the number of active sessions for each minute in a time frame
- Calculate duration between login and logout time when some records are incomplete
- How can I get the length of a session in SQL given multiple timestamps?
- In TSQL, how to calculate time duration across 2 tables (login & logout logs)?
- Is there a better way to group a log on minutes?
- Calculate Sessions Between TimeRange in TSQL
- How To get the Last 15 minutes values(sessions) for each row in the table in sql select with SUM
- SQL Server 2008: Getting duration between user sessions