Count rows with two columns and every minute

Question

I have a SQL Server 2012 table with sessions and the start and end times are 2. I now need to know how many sessions were active in each minute, that is,> = start time and <= end time.

Example

Order_strUserId | Order_dtmInitiated      | Order_dtmLastUpdated
s2ir1f8vqx      | 2016-12-13 15:06:17.993 | 2016-12-13 15:06:59.723
4839m6lnjchn    | 2016-12-13 15:09:42.807 | 2016-12-13 15:12:21.220
y7k3u6q1wjn     | 2016-12-13 15:11:40.173 | 2016-12-13 15:12:01.630
sdc74a0ahid     | 2016-12-13 15:14:06.013 | 2016-12-13 15:14:28.703
2pgj2ixpta9     | 2016-12-13 15:17:41.567 | 2016-12-13 15:17:42.063
qlfv4vmxdb      | 2016-12-13 15:18:17.750 | 2016-12-13 15:18:47.227
y9jd24i59x5v    | 2016-12-13 15:19:30.160 | 2016-12-13 15:19:30.607
9vb2d6u90hn     | 2016-12-13 15:22:09.257 | 2016-12-13 15:22:09.743

Line 1 is calculated only for 2016-12-13 15:06, line 2 for every minute between 2016-12-13 15:09 and 2016-12-13 15:12, etc.

I need something like

Time             | ActiveSessions
2016-12-13 15:06 | 1
2016-12-13 15:09 | 1
2016-12-13 15:10 | 1
2016-12-13 15:11 | 2
2016-12-13 15:12 | 2

How to do it?

My idea so far has been to count the number of users by minute, and then add the time needed to count. But I can’t figure out how to calculate this.

SELECT COUNT(t.UserSessionId) ,
    DATEPART( yyyy , t.TimeInitiated) AS 'Year', 
    DATEPART(MM, t.TimeInitiated) 'Month', 
    DATEPART(DD, t.TimeInitiated) 'Day', 
    DATEPART(HH, t.TimeInitiated) 'Hour', 
    DATEPART(MI, t.TimeInitiated) 'Minute', 
    t.TimeNeeded
FROM ( SELECT DATEDIFF(MI, Order_dtmInitiated, Order_dtmLastUpdated) AS 'TimeNeeded',
        Order_strUserId AS 'UserSessionId',
        Order_dtmInitiated AS 'TimeInitiated'
    FROM tblOrder ) t
GROUP BY DATEPART( yyyy , t.TimeInitiated), DATEPART(MM, t.TimeInitiated), DATEPART(DD, t.TimeInitiated), DATEPART(HH, t.TimeInitiated), DATEPART(MI, t.TimeInitiated), t.TimeNeeded

Answer 1

You need a calendar table with minutes

;WITH cte
     AS (SELECT Min(Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmInitiated), 0) AS SMALLDATETIME))   AS st_date,
                Max(Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmLastUpdated), 0) AS SMALLDATETIME)) ed_date
         FROM   Yourtable
         UNION ALL
         SELECT Dateadd(minute, 1, st_date),
                ed_date
         FROM   cte
         WHERE  st_date < ed_date)
SELECT st_date,
       Count(1)
FROM   cte a
       LEFT JOIN Yourtable b
              ON a.st_date >= Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmInitiated), 0) AS SMALLDATETIME)
                 AND a.st_date <= Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmLastUpdated), 0) AS SMALLDATETIME)
GROUP  BY st_date
OPTION (maxrecursion 0) 

Recursive CTE , Calendar

• Live Demo

Answer 2

,

with Numbers as
(
select 0 as NN
union all
select NN+1
from Numbers
where NN < 99999
)
, Cal_table as
(
select dateadd(MI, NN, @StartTime) as By_Minute -- Throwing these in as parameters, but you can use a query to fill this bit in
from Numbers
where dateadd(MI, NN, @StartTime) <= @EndTime -- Throwing these in as parameters, but you can use a query to fill this bit in
)
select By_Minute, count(Order_strUserId) as ActiveUsers
from Cal_Table 
left join MyTable
on Order_dtmInitiated <= By_Minute
and Order_dtmLastUpdated >= By_Minute

Answer 3

CTE, , , :

-- Setup data:

DECLARE @rows TABLE(UserId VARCHAR(20), Initiated DATETIME, LastUpdated DATETIME)
INSERT INTO @rows VALUES
  ('s2ir1f8vqx'  , '2016-12-13 15:06:17.993', '2016-12-13 15:06:59.723'),
  ('4839m6lnjchn', '2016-12-13 15:09:42.807', '2016-12-13 15:12:21.220'),
  ('y7k3u6q1wjn' , '2016-12-13 15:11:40.173', '2016-12-13 15:12:01.630'),
  ('sdc74a0ahid' , '2016-12-13 15:14:06.013', '2016-12-13 15:14:28.703'),
  ('2pgj2ixpta9' , '2016-12-13 15:17:41.567', '2016-12-13 15:17:42.063'),
  ('qlfv4vmxdb'  , '2016-12-13 15:18:17.750', '2016-12-13 15:18:47.227'),
  ('y9jd24i59x5v', '2016-12-13 15:19:30.160', '2016-12-13 15:19:30.607'),
  ('9vb2d6u90hn' , '2016-12-13 15:22:09.257', '2016-12-13 15:22:09.743')

-- Do the work:

;WITH activity AS
(
  SELECT r1.UserId, CAST(CONVERT(CHAR(17), r1.Initiated, 113) AS DATETIME) AS dt FROM @rows r1
  UNION ALL
  SELECT r2.UserId, DATEADD(minute, 1, dt) AS dt FROM activity INNER JOIN @rows r2 ON r2.UserId = activity.UserId WHERE DATEADD(minute, 1, dt) <= r2.LastUpdated
)
SELECT dt as Time, count(UserId) as ActiveSessions FROM activity
GROUP BY dt
OPTION (MAXRECURSION 10080)  -- 10080 = 7*24*60 = user active every minute of one consecutive week (max is 32767 or ~ 3 weeks)

:

Time                     ActiveSessions
2016-12-13 15:06:00.000     1
2016-12-13 15:09:00.000     1
2016-12-13 15:10:00.000     1
2016-12-13 15:11:00.000     2
2016-12-13 15:12:00.000     2
2016-12-13 15:14:00.000     1
2016-12-13 15:17:00.000     1
2016-12-13 15:18:00.000     1
2016-12-13 15:19:00.000     1
2016-12-13 15:22:00.000     1

Answer 4

, cte calendar. , , .

DECLARE 
     @dt_from   DATETIME    =   '2016-12-13 15:06:17.993'
    ,@dt_to     DATETIME    =   '2016-12-13 15:22:09.743'
    ,@cnt       INT
    ,@mins      INT         =   2;

DECLARE @result TABLE([Time] DATETIME, [ActiveSessions] INT);

SET @dt_from    = DATEADD(mi, DATEDIFF(mi, 0, @dt_from),    0);
SET @dt_to      = DATEADD(mi, DATEDIFF(mi, 0, @dt_to),      0);

WHILE @dt_from <= @dt_to
BEGIN

    SELECT @cnt = COUNT(*) FROM [YourTable] 
    WHERE 
        DATEADD(mi, DATEDIFF(mi, 0, [Order_dtmInitiated]),      0)  BETWEEN @dt_from AND DATEADD(mi, @mins, @dt_from)
    OR  DATEADD(mi, DATEDIFF(mi, 0, [Order_dtmLastUpdated]),    0)  BETWEEN @dt_from AND DATEADD(mi, @mins, @dt_from)

    INSERT INTO @result([Time], [ActiveSessions]) 
    VALUES (@dt_from, @cnt);

    SET @dt_from = DATEADD(mi, @mins, @dt_from);
END; 

SELECT * FROM @result;