CODEWITHSUNDEEP
sqlsql-serversql-server-2012
JMIII
JMIII

Reputation: 404

How to use ms sql query to determine max number of concurrent sessions over a timespan

Lets say I have a simple table like so

Session

id dtmStarted dtmEnded

At the minute level I want to know the max number of concurrent sessions over a time-span

such that

id   dtmStarted           dtmEnded
1 - '2020-01-01 10:00' - '2020-01-01 10:05'
2 - '2020-01-01 10:00' - '2020-01-01 10:05'
3 - '2020-01-01 10:00' - '2020-01-01 10:05'
4 - '2020-01-01 10:06' - '2020-01-01 10:09'
5 - '2020-01-01 10:07' - '2020-01-01 10:08'
6 - '2020-01-01 10:10' - '2020-01-01 10:11'

So between 10:10 and 10:11 there was only 1 session. Between 10:07 and 10:08 there where 2 concurrent sessions. Between 10:00 - 10:01 there is 3 concurrent session and so forth. In this case the query should return 3.

Upvotes: 1

Views: 523

Answers (1)

Serg
Serg

Reputation: 22811

You need a tally table for that. I create it here on the fly but it preferably may be instantiated as a regular table as well.

declare @t table(
  id int,
  dtmStarted datetime,
  dtmEnded datetime
  );
insert @t values
(1, '2020-01-01 10:00', '2020-01-01 10:05'),
(2, '2020-01-01 10:00', '2020-01-01 10:05'),
(3, '2020-01-01 10:00', '2020-01-01 10:05'),
(4, '2020-01-01 10:06', '2020-01-01 10:09'),
(5, '2020-01-01 10:07', '2020-01-01 10:08'),
(6, '2020-01-01 10:10', '2020-01-01 10:11');

declare @s datetime = '2020-01-01 10:00';
declare @e datetime = '2020-01-01 11:00';

-- table of 1000 numbers starting 0
with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),nmbs as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2, t0 t3
)
select dateadd(minute, n, @s) start, count(id) cnt
from nmbs
left join @t on dtmStarted <= dateadd(minute, n+1, @s) and dateadd(minute, n, @s)<= dtmEnded
where dateadd(minute, n+1, @s) <= @e
group by dateadd(minute, n, @s)
order by dateadd(minute, n, @s);

Returns

2020-01-01 10:01:00.000 3
2020-01-01 10:02:00.000 3
2020-01-01 10:03:00.000 3
2020-01-01 10:04:00.000 3
2020-01-01 10:05:00.000 4
2020-01-01 10:06:00.000 2
2020-01-01 10:07:00.000 2
2020-01-01 10:08:00.000 2
2020-01-01 10:09:00.000 2
2020-01-01 10:10:00.000 1
2020-01-01 10:11:00.000 1
2020-01-01 10:12:00.000 0
2020-01-01 10:13:00.000 0
2020-01-01 10:14:00.000 0
2020-01-01 10:15:00.000 0
...
2020-01-01 10:59:00.000 0

Probably you'll need to change both or one of predicates dtmStarted <= dateadd(minute, n+1, @s) and dateadd(minute, n, @s) <= dtmEnded from <= to strict < to get the expected result.

Upvotes: 1

Related Questions