Hi Lydia @Anonymous,
let's focus on one day as a period only. I am already able to filter the page for a specific day and apply a calculation.
The result you posted is right. But we would not devide into different days here but filter on a page or report basis for a specific period we want to see the number of concurrent sessions for. This could be month or day and does not matter.
A simple logic amount of concurrent sessions that ran in a certain period.
The SQL code would look something like this:
@start_date_period datetime,
@end_date_period datetime,
AS
BEGIN
WHEN (count(*)!=0) Then (max(concurrentNumber)+1) ELSE max(concurrentNumber) END as 'Peak concurrent sessions in the measured period'
from (
Select start_time,
(
SELECT count(*)
FROM Sessions
where start_time is not NULL
and end_time is not NULL
and start_time < checkQ.start_time
and start_time > @start_date_period
and end_time < @end_date_period
and end_time > checkQ.start_time
) as concurrentNumber
FROM Sessions as checkQ
where
start_time is not NULL
and end_time is not NULL
and start_time > @start_date_period
and end_time < @end_date_period
group by Start_Time
) as baseQuery
END
Best,
Marcel
