CODEWITHSUNDEEP
sql-server
cdrrr
cdrrr

Reputation: 1112

SQL Server - Display the number of active sessions for each minute in a time frame

I have the below table:

SessionID | UserName | Started             | Ended
----------------------------------------------------------------
100         Test1      2015-07-26 00:03:05   2015-07-26 00:08:12

As the title says, I need to extract between a given @FromDate and a @ToDate parameters, for each minute, how many active sessions were. What I have tried so far does not select the non-active session (when no customers were online in that minute) and I cannot figure it out how to do this.

My SQL Statement

CREATE PROCEDURE [dbo].[ActiveSessionsByMinute]  @FromDate datetime, @ToDate datetime

AS
BEGIN

SET NOCOUNT ON


SELECT DATEADD(MINUTE, DATEPART(MINUTE, Started), CAST(CONVERT(varchar(20), Started, 112) AS datetime)) AS DateMinute,
COUNT(SessionID) AS ActiveSessions
FROM ApplicationSessionHistory
GROUP BY DATEADD(MINUTE, DATEPART(MINUTE, Started), CAST(CONVERT(varchar(20), Started, 112) AS datetime))


END

GO

Output

DateMinute              |  ActiveSessions
-----------------------------------------
2015-07-26 00:03:00.000 |  1

If I execute the below statement, I should get the desired output (below):

EXEC dbo.ActiveSessionsByMinute 
@FromDate = '2015-07-26 00:00', 
@ToDate = '2015-07-26 00:10'

Desired Output

DateMinute              |  ActiveSessions
-----------------------------------------
2015-07-26 00:00:00.000 |  0
2015-07-26 00:01:00.000 |  0
2015-07-26 00:02:00.000 |  0
2015-07-26 00:03:00.000 |  1
2015-07-26 00:04:00.000 |  1
2015-07-26 00:05:00.000 |  1
2015-07-26 00:06:00.000 |  1
2015-07-26 00:07:00.000 |  1
2015-07-26 00:08:00.000 |  1
2015-07-26 00:09:00.000 |  0
2015-07-26 00:00:00.000 |  0

Does anyone can give me a tip? Thanks

Upvotes: 0

Views: 553

Answers (2)

S3S
S3S

Reputation: 25112

I would do this with a CTE tally table. Notice I added an extra Session in the sample data.

HERE IS A DEMO

--Sample data
declare @table table (SessionID int, UserName varchar(16), Started datetime, Ended datetime)
insert into @table
values
(100,'Test1','2015-07-26 00:03:05','2015-07-26 00:08:12')
,(101,'Test1','2015-07-26 00:04:05','2015-07-26 00:05:12')


--used as a beginning anchor for the tally table    
declare @startDate datetime = (select min(cast(Started as date)) from @table)

--take the original data, and truncate the seconds
;with NewTable as(
select 
    SessionID 
    ,UserName
    ,Started = CAST(DateAdd(minute, DateDiff(minute, 0, Started), 0) AS smalldatetime)
    ,Ended = CAST(DateAdd(minute, DateDiff(minute, 0, Ended), 0) AS smalldatetime)
from @table
    ),

    --tally table to get 10K minutes. 
    --This can be expanded for larger date ranges, and is faster than recursive CTE
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  TallyDate = dateadd(minute,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),@startDate) FROM E4
    )


--use cross apply and and a case statement to find if it falls in the range
select
    DateMinute = N
    ,SessionID
    ,Started
    ,Ended
    ,IsActive = case when (Started <=N and Ended >= N) then 1 else 0 end
from NewTable t
cross apply cteTally 
where N <= (select max(Ended) from @table)
order by SessionID, N

For the sum part, you can simply aggregate. Replace the last SELECT with this one

--based on the above output, just do the SUM
select
    DateMinute = N
    ,ActiveSessions = sum(case when (Started <=N and Ended >= N) then 1 else 0 end)
from NewTable t
cross apply cteTally 
where N <= (select max(dateadd(minute,1,Ended)) from @table)
group by N
order by N

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

You'll want to SELECT from a tally table with all the minutes and LEFT JOIN to your ApplicationSessionHistory table:

CREATE PROCEDURE [dbo].[ActiveSessionsByMinute]
    @FromDate DATETIME
  , @ToDate DATETIME
AS
BEGIN

    SET NOCOUNT ON;


    SELECT allminutes.alltimes AS DateMinute
         , COUNT(SessionID) AS ActiveSessions
    FROM
    (
        SELECT DATEADD(MINUTE, myrows.rn, @FromDate) AS alltimes
        FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY s.id) - 1 rn
            FROM master.sys.syscolumns AS s
        ) myrows
    ) allminutes
        LEFT OUTER JOIN ApplicationSessionHistory ON allminutes.alltimes BETWEEN ApplicationSessionHistory.Started AND ApplicationSessionHistory.Ended
    WHERE allminutes.alltimes <= @ToDate
    GROUP BY DATEADD(MINUTE, DATEPART(MINUTE, Started), CAST(CONVERT(VARCHAR(20), Started, 112) AS DATETIME));

END;

Upvotes: 1

Related Questions