title: How to calculate number of users with a particular status across a time period?
body: <p>I have a report that I need to create from a UserHistory table in SQL Server 2008 R2. A sample is below:
  <img src="https://i.stack.imgur.com/GjTn4.jpg" alt="report"></p>
  
  <p>For billing purposes we need to calculate how many distinct users have had a particular status across a particular period. The period can be as low as 1 day or up to something like 2 months. So if one user was active, not active and then active again across a period then that counts as one active user. </p>
  
  <p>The curve ball now comes in to play. If I had no histories within the specified period, the stored proc needs to hunt for the first history entry before the start of the period to see if it matches the status. As obviously if I was active on the 20th of October and then my account was suspended on the 15th of November, if the period was for November, I was still active. </p>
  
  <p>At first I did this with a cursor going through each User table, and doing the queries per user and it's user histories however even with the relevant indexes this takes about 40 seconds for 200000 users. 40s for each day in a period for 9 statuses adds up very quickly. </p>
  
  <p>My colleage and I came up with this method which has come the closest:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[cpUserHistory_CountByStatus] @StartDate DATETIME2,
      @EndDate DATETIME2,
      @Status VARCHAR(50)
  AS
  SET NOCOUNT ON
  
  SELECT SUM(CASE WHEN CurrentMonth.UserId IS NOT NULL OR PreviousTime.UserId IS NOT NULL THEN 1 ELSE 0 END)
  FROM [User]
  LEFT JOIN
         (SELECT UserId
         FROM
                UserHistory
                INNER JOIN
                       (SELECT MAX(UserHistoryId) AS UserHistoryId
                       FROM UserHistory
                       WHERE EditedDate BETWEEN @StartDate AND @EndDate
                       GROUP BY UserId) LastStatus ON UserHistory.UserHistoryId = LastStatus.UserHistoryId
         WHERE UserHistory.Status = @Status                                -- EDIT: Your status you're interested in
         ) CurrentMonth ON [User].UserId = CurrentMonth.UserId
  LEFT JOIN
         (SELECT UserId
         FROM
                UserHistory
                INNER JOIN
                       (SELECT MAX(UserHistoryId) AS UserHistoryId
                       FROM UserHistory
                       WHERE EditedDate &lt; @StartDate
                       GROUP BY UserId) LastStatus ON UserHistory.UserHistoryId = LastStatus.UserHistoryId
         WHERE UserHistory.Status = @Status                               -- EDIT: Your status you're interested in
         ) PreviousTime ON [User].UserId = PreviousTime.UserId
  </code></pre>
  
  <p>But as you can see in the simplified report sample, a user was cancelled on the 3rd of the month and then reactivated on the 5th, however the total does not reflect that at some point in the period there was 1 cancelled user. </p>
  
  <p>Does anyone have any ideas or better ways to go about doing this? I could really use some input.</p>