Q:

Count rows with two columns and each minute

I have a SQL Server 2012 table with sessions and a start and end time in 2.  I now need to know how many sessions have been active at each minute, meaning >= starttime and <= endtime. 
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

Row 1 counts only for 2016-12-13 15:06, Row 2 for every minute between 2016-12-13 15:09 and 2016-12-13 15:12 etc. 
What I need is 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 this? 
My idea so far has been to have a count of the UserId by minute and then add the time needed to the count.  But I cant 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

A:

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) 

I have used Recursive CTE to generate dates but I prefer to create a Calendar table in my database and use it in queries like this 

Live Demo

