Reputation: 1
Calculate concurrent sessions with T-SQL
I have the date of sesions in my database (start and end of session) in datatime type. I need to calculate how many concurrent sessions has been done in a concrete time and per user type. It means I need to compute the number of simultaneous sessions per user and per day.
The database use SQL Server and the table is like the next:
SESSION GRU_ID SES_INI SES_END
233550 Enfermeros 2016-02-10 07:49:32.000 2016-02-10 14:51:46.000
233551 Enfermeros 2016-02-10 07:57:05.000 2016-02-10 12:37:03.000
233552 Enfermeros 2016-02-10 08:03:09.000 2016-02-10 14:44:30.000
233553 Enfermeros 2016-02-10 08:04:49.000 2016-02-10 13:22:58.000
233556 Enfermeros 2016-02-10 08:06:52.000 2016-02-10 14:54:29.000
233559 Enfermeros 2016-02-10 08:08:00.000 2016-02-10 10:49:38.000
233560 Enfermeros 2016-02-10 08:08:09.000 2016-02-10 14:25:19.000
233564 Enfermeros 2016-02-10 08:13:54.000 2016-02-10 14:51:19.000
233565 Enfermeros 2016-02-10 08:14:35.000 2016-02-10 11:46:16.000
233567 Enfermeros 2016-02-10 08:16:01.000 2016-02-10 15:05:20.000
233568 Enfermeros 2016-02-10 08:17:01.000 2016-02-10 12:40:05.000
233569 Enfermeros 2016-02-10 08:19:13.000 2016-02-10 14:46:54.000
233570 Enfermeros 2016-02-10 08:17:50.000 2016-02-10 14:48:10.000
233579 Enfermeros 2016-02-10 08:29:52.000 2016-02-10 14:21:38.000
233580 Enfermeros 2016-02-10 08:31:11.000 2016-02-10 10:09:36.000
233584 Enfermeros 2016-02-10 08:38:52.000 2016-02-10 13:22:06.000
233585 Enfermeros 2016-02-10 08:40:11.000 2016-02-10 11:08:10.000
233589 Enfermeros 2016-02-10 08:48:09.000 2016-02-11 16:59:06.000
233603 Enfermeros 2016-02-10 09:24:56.000 2016-02-10 09:37:29.000
233607 Enfermeros 2016-02-10 09:29:31.000 2016-02-10 16:00:31.000
233623 Enfermeros 2016-02-10 10:08:34.000 2016-02-10 12:16:28.000
233624 Enfermeros 2016-02-10 10:09:36.000 2016-02-10 10:10:27.000
233625 Enfermeros 2016-02-10 10:10:51.000 2016-02-10 10:46:30.000
The select I'm using is the next. The problem is that this select summarizes al the sessions that have ocurred during this interval of time between ses_ini and ses_end, but it does not mean simultaneous sessions.
SELECT GRU_ID AS TIPO_USUARIO, count(GRU_ID) AS TOTAL
FROM
dbo.USUARIOS INNER JOIN
dbo.SESIONES ON dbo.USUARIOS.CODIGOUSUARIO = dbo.SESIONES.CODIGOUSUARIO INNER JOIN
dbo.GRUPOS ON dbo.USUARIOS.CODIGOGRUPO = dbo.GRUPOS.CODIGOGRUPO
WHERE
SES_FECHAFIN BETWEEN @SES_INI AND @SES_END AND
DAY(ses_fechainicio) = 10 AND MONTH(ses_INI)= 2 AND YEAR(ses_END) = 2016 AND ses_duracion IS NOT NULL
GROUP BY GRU_ID
I need some kind of algorithm to calculate all simultaneous sessions with this data.
EDIT:
If I have something like this:
Session A: 9.00 - 10.00
Session B: 9.00 - 9.30
Session C: 9.30 - 9.45
Session D: 9.40 - 10:00
Between the first Session A, There are the next simultaneous sessions: A-B and A-C-D (Sessions B and C I don't consider simultaneous) With the select I did, I get all sessions between 9.00-10.00, that is 4, but I really need to get the maximum of simultaneous sessions, that is 3, and I need to do the same for each sessions.
The complete script I did is like this:
One loop (while) for years, one for months and other for days (nested loops). Then one cursor which select all the sessions for a day(using the loops). Once I get all the sessions per days, I make an insert(with the select I pasted) which populate a table with 5 columns: Simultanious sessions, GRU_ID(type of user: Physician,administrator, nurse, etc)day, month and year. The problem is with the insert, because I can only get the total number of sessions between each session, but not the simultaneous sessions. Sorry if I dont explain well, but is difficult and my english is not very good....
Upvotes: 0
Views: 1274
Answers (3)
Reputation: 9470
The idea is that sessions are concurrent if ses_ini of new session is between ses_ini and ses_end of another session.
This is implementation:
create table #sess([session] int,gru_id varchar(20),ses_ini datetime,ses_end datetime)
insert #sess
values
(233550, 'Enfermeros', '2016-02-10 07:49:32.000', '2016-02-10 14:51:46.000'),
(233551 ,'Enfermeros', '2016-02-10 07:57:05.000', '2016-02-10 12:37:03.000'),
(233552 ,'Enfermeros', '2016-02-10 08:03:09.000', '2016-02-10 14:44:30.000'),
(233553 ,'Enfermeros', '2016-02-10 08:04:49.000', '2016-02-10 13:22:58.000'),
(233556 ,'Enfermeros', '2016-02-10 08:06:52.000', '2016-02-10 14:54:29.000'),
(233559 ,'Enfermeros', '2016-02-10 08:08:00.000', '2016-02-10 10:49:38.000'),
(233560 ,'Enfermeros', '2016-02-10 08:08:09.000', '2016-02-10 14:25:19.000'),
(233564 ,'Enfermeros', '2016-02-10 08:13:54.000', '2016-02-10 14:51:19.000'),
(233565 ,'Enfermeros', '2016-02-10 08:14:35.000', '2016-02-10 11:46:16.000'),
(233567 ,'Enfermeros', '2016-02-10 08:16:01.000', '2016-02-10 15:05:20.000'),
(233568 ,'Enfermeros', '2016-02-10 08:17:01.000', '2016-02-10 12:40:05.000'),
(233569 ,'Enfermeros', '2016-02-10 08:19:13.000', '2016-02-10 14:46:54.000'),
(233570 ,'Enfermeros', '2016-02-10 08:17:50.000', '2016-02-10 14:48:10.000'),
(233579 ,'Enfermeros', '2016-02-10 08:29:52.000', '2016-02-10 14:21:38.000'),
(233580 ,'Enfermeros', '2016-02-10 08:31:11.000', '2016-02-10 10:09:36.000'),
(233584 ,'Enfermeros', '2016-02-10 08:38:52.000', '2016-02-10 13:22:06.000'),
(233585 ,'Enfermeros', '2016-02-10 08:40:11.000', '2016-02-10 11:08:10.000'),
(233589 ,'Enfermeros', '2016-02-10 08:48:09.000', '2016-02-11 16:59:06.000'),
(233603 ,'Enfermeros', '2016-02-10 09:24:56.000', '2016-02-10 09:37:29.000'),
(233607 ,'Enfermeros', '2016-02-10 09:29:31.000', '2016-02-10 16:00:31.000'),
(233623 ,'Enfermeros', '2016-02-10 10:08:34.000', '2016-02-10 12:16:28.000'),
(233624 ,'Enfermeros', '2016-02-10 10:09:36.000', '2016-02-10 10:10:27.000'),
(233625 ,'Enfermeros', '2016-02-10 10:10:51.000', '2016-02-10 10:46:30.000')
--select all concurrent sessions
select s.session,s1.session s1_sess,s.ses_ini,s.ses_end,s1.ses_ini s1_ini
from #sess s
inner join #sess s1 on s1.ses_ini >= s.ses_ini and s1.ses_ini < s.ses_end and s.session<>s1.session
order by 1
-- calculate the number of concurrent sessions
;with ss as (
select s.session,s1.session s1_sess,s.ses_ini,s.ses_end,s1.ses_ini s1_ini
from #sess s
inner join #sess s1 on s1.ses_ini >= s.ses_ini and s1.ses_ini < s.ses_end and s.session<>s1.session
)
select session, count(s1_sess) cnt
from ss
group by session
order by 2 desc
I hope this is what you are looking for.
Update + addition:
declare @dtStart datetime, @dtEnd datetime
select @dtStart= convert(varchar(13),(select min(ses_ini) from #sess), 120)+':00:00'
,@dtEnd= convert(varchar(13),dateadd(hh,1,(select max(ses_end) from #sess)), 120)+':00:00'
;with tm as (
select 1 num, @dtStart sesStart, dateadd(hh,1,@dtStart) sesEnd
union all
select num+1,dateadd(hh,1,sesStart),dateadd(hh,1,sesEnd)
from tm where tm.sesStart<@dtEnd
)
select 'sess '+cast(num as varchar) sess,sesStart,sesEnd, count(session) cnt
from tm
inner join #sess s on (s.ses_ini>=tm.sesStart and s.ses_ini < tm.sesEnd) or
(s.ses_end>tm.sesStart and s.ses_end <= tm.sesEnd)
group by num,sesStart,sesEnd
Result:
sess sesStart sesEnd cnt
sess 1 2016-02-10 07:00:00.000 2016-02-10 08:00:00.000 2
sess 2 2016-02-10 08:00:00.000 2016-02-10 09:00:00.000 16
sess 3 2016-02-10 09:00:00.000 2016-02-10 10:00:00.000 2
sess 4 2016-02-10 10:00:00.000 2016-02-10 11:00:00.000 5
sess 5 2016-02-10 11:00:00.000 2016-02-10 12:00:00.000 2
sess 6 2016-02-10 12:00:00.000 2016-02-10 13:00:00.000 3
sess 7 2016-02-10 13:00:00.000 2016-02-10 14:00:00.000 2
sess 8 2016-02-10 14:00:00.000 2016-02-10 15:00:00.000 8
sess 9 2016-02-10 15:00:00.000 2016-02-10 16:00:00.000 1
sess 10 2016-02-10 16:00:00.000 2016-02-10 17:00:00.000 1
sess 34 2016-02-11 16:00:00.000 2016-02-11 17:00:00.000 1
Update 2: Sessions which exist during each time interval
declare @dtStart datetime, @dtEnd datetime
select @dtStart= convert(varchar(13),(select min(ses_ini) from #sess), 120)+':00:00'
,@dtEnd= convert(varchar(13),dateadd(hh,1,(select max(ses_end) from #sess)), 120)+':00:00'
;with tm as (
select 1 num, @dtStart sesStart, dateadd(hh,1,@dtStart) sesEnd
union all
select num+1,dateadd(hh,1,sesStart),dateadd(hh,1,sesEnd)
from tm where tm.sesStart<@dtEnd
)
select 'sess '+cast(num as varchar) sess,sesStart,sesEnd, count(session) cnt
from tm
inner join #sess s on (s.ses_end > tm.sesStart and s.ses_ini <= tm.sesEnd)
group by num,sesStart,sesEnd
Results:
sess sesStart sesEnd cnt
sess 1 2016-02-10 07:00:00 2016-02-10 08:00:00 2
sess 2 2016-02-10 08:00:00 2016-02-10 09:00:00 18
sess 3 2016-02-10 09:00:00 2016-02-10 10:00:00 20
sess 4 2016-02-10 10:00:00 2016-02-10 11:00:00 22
sess 5 2016-02-10 11:00:00 2016-02-10 12:00:00 18
sess 6 2016-02-10 12:00:00 2016-02-10 13:00:00 16
sess 7 2016-02-10 13:00:00 2016-02-10 14:00:00 13
sess 8 2016-02-10 14:00:00 2016-02-10 15:00:00 11
sess 9 2016-02-10 15:00:00 2016-02-10 16:00:00 3
sess 10 2016-02-10 16:00:00 2016-02-10 17:00:00 2
sess 11 2016-02-10 17:00:00 2016-02-10 18:00:00 1
sess 12 2016-02-10 18:00:00 2016-02-10 19:00:00 1
sess 13 2016-02-10 19:00:00 2016-02-10 20:00:00 1
sess 14 2016-02-10 20:00:00 2016-02-10 21:00:00 1
sess 15 2016-02-10 21:00:00 2016-02-10 22:00:00 1
sess 16 2016-02-10 22:00:00 2016-02-10 23:00:00 1
sess 17 2016-02-10 23:00:00 2016-02-11 00:00:00 1
sess 18 2016-02-11 00:00:00 2016-02-11 01:00:00 1
sess 19 2016-02-11 01:00:00 2016-02-11 02:00:00 1
sess 20 2016-02-11 02:00:00 2016-02-11 03:00:00 1
sess 21 2016-02-11 03:00:00 2016-02-11 04:00:00 1
sess 22 2016-02-11 04:00:00 2016-02-11 05:00:00 1
sess 23 2016-02-11 05:00:00 2016-02-11 06:00:00 1
sess 24 2016-02-11 06:00:00 2016-02-11 07:00:00 1
sess 25 2016-02-11 07:00:00 2016-02-11 08:00:00 1
sess 26 2016-02-11 08:00:00 2016-02-11 09:00:00 1
sess 27 2016-02-11 09:00:00 2016-02-11 10:00:00 1
sess 28 2016-02-11 10:00:00 2016-02-11 11:00:00 1
sess 29 2016-02-11 11:00:00 2016-02-11 12:00:00 1
sess 30 2016-02-11 12:00:00 2016-02-11 13:00:00 1
sess 31 2016-02-11 13:00:00 2016-02-11 14:00:00 1
sess 32 2016-02-11 14:00:00 2016-02-11 15:00:00 1
sess 33 2016-02-11 15:00:00 2016-02-11 16:00:00 1
sess 34 2016-02-11 16:00:00 2016-02-11 17:00:00 1
Upvotes: 0
Reputation: 22443
How about something like this... Count the number of sessions that start or end during another session. Then summarize by date and max count.
SELECT
[GRU_ID]
,CAST([Ses_Ini] AS DATE) AS [Date]
,MAX([Sessions Count]) AS [Max Connections]
FROM (
SELECT
[Data].[Session]
,[Data].[GRU_ID]
,[Data].[Ses_Ini]
,[Data].[Ses_End]
,COUNT(DISTINCT [D2].[Session]) AS [Sessions Count]
FROM [Data]
INNER JOIN [Data] AS [D2]
ON ([d2].[Ses_Ini] BETWEEN [Data].[Ses_Ini] AND [Data].[Ses_End])
OR ([d2].[Ses_End] BETWEEN [Data].[Ses_Ini] AND [Data].[Ses_End])
GROUP BY
[Data].[Session]
,[Data].[GRU_ID]
,[Data].[Ses_Ini]
,[Data].[Ses_End]
) AS [SessionCounts]
GROUP BY
[GRU_ID]
,CAST([Ses_Ini] AS DATE)
Upvotes: 0
Reputation: 1270623
The idea is to put all the times in one big list with a +1 for session starts and a -1 for session ends. Then a cumulative sum is pretty close to what you want.
So:
select gru_id, dt,
sum(val) over (partition by gru_id order by dt) as concurrent_sessions
from ((select gru_id, ses_ini as dt, 1 as val from t
) union all
(select gru_id, ses_end, -1 as val from t
)
) g;
This gives the number for each time in the data. You probably want to summarize it somehow. Your existing question is not clear on this point -- you should ask another question if you want to specify a particular output.
This is "close" in the sense of handling ties. If a user starts and stops sessions at exactly the same time, there might be an "off-by-one" error, depending on how you want to count the sessions.
Upvotes: 1
Related Questions
- How to concatenate text from multiple rows into a single text string in SQL Server
- How to return only the Date from a SQL Server DateTime datatype
- How to add a column with a default value to an existing table in SQL Server?
- Find all tables containing column with specified name
- Finding duplicate values in a SQL table
- How do I UPDATE from a SELECT in SQL Server?
- How to check if a column exists in a SQL Server table
- How do I perform an IF...THEN in an SQL SELECT?
- How can I do an UPDATE statement with JOIN in SQL Server?
- LEFT JOIN vs. LEFT OUTER JOIN in SQL Server