title: Count of rows by month within period
body: <p>I'm working on a T-SQL function to return the count of two categories (enrolls and verifies) for each month within a specific period. The function below works only when there is at least a count greater than 1 for each month. What I'd like to do is given a start date of the lowest date found in the source table, an end date of today, and a given number of months, return the counts of enrolls, and the count of verifies for a given user id between the start and end period of months.  </p>
  
  <p>Ideally I'd like the range-period data be formatted as <code>MMM-YYYY</code>. For any months during this period where there are no counts, I'd like those values to show a count of 0.</p>
  
  <p><code>RELEASED_DATETIME</code> is a <code>DATETIME</code> column in the <code>INTERACTION_SESSION_T</code> table.</p>
  
  <p>Valid Data for a period of 7 Months</p>
  
  <pre><code>RANGEPERIOD | ENROLLS | VERIFIES
  ------------+---------+---------
  Nov-2017    |    15   |    15
  Dec-2017    |   150   |  2582
  Jan-2018    |     0   |     0
  Feb-2018    |     0   |    98
  Mar-2018    |    10   |     0
  Apr-2018    |     8   |     0
  May-2018    |    12   |    85
  </code></pre>
  
  <p>My code:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[SVE_GET_ENROL_VERIFY_COUNT_MONTH_R]
       (@DEV_ID INT, @NUM_MONTHS INT)
  RETURNS @D TABLE (RANGEPERIOD VARCHAR(10),
                    TOTAL_ENROLLMENTS INT,
                    TOTAL_VERIFICATIONS INT)
  AS
  BEGIN
      DECLARE @cur VARCHAR(12); -- stores the current record date the cursor points to
      DECLARE @sdate DATETIME;  -- start date of range
      DECLARE @edate DATETIME;  -- end date of range
      DECLARE @tot_enrls INT;   -- stores the @cur date's enrollment count
      DECLARE @tot_verfs INT;   -- stores the @cur date's verification count
  
      SELECT @sdate = MIN(RELEASED_DATETIME)
      FROM SVE_INTERACTION_SESSION_T 
      WHERE (DEVELOPER_OWNER_ID = @DEV_ID OR DEVELOPER_USER_ID = @DEV_ID) 
        AND RELEASED_DATETIME &gt;= DATEADD(MONTH, -@NUM_MONTHS + 1, GETDATE())
  
      SELECT @edate = GETDATE();
  
      INSERT INTO @D (RANGEPERIOD)
          (SELECT
               RANGEPERIOD = CONCAT(SUBSTRING(datename(month, I.RELEASED_DATETIME), 1, 3 ), '-', YEAR(I.RELEASED_DATETIME))
           FROM
               SVE_INTERACTION_SESSION_T AS I WITH (NOLOCK) 
           INNER JOIN 
               SVE_APPLICATIONS_T AS A WITH (NOLOCK) ON I.APPLICATION_ID = A._ID
           WHERE
               (I.DEVELOPER_OWNER_ID = @DEV_ID OR I.DEVELOPER_USER_ID = @DEV_ID) 
               AND I.RELEASED_CODE = 'C' 
               AND A.IS_DELETED = 0 
               AND I.RELEASED_DATETIME &gt;= @sdate 
               AND I.RELEASED_DATETIME &lt; DATEADD(day, 1, @edate)
           GROUP BY 
               YEAR(I.RELEASED_DATETIME), MONTH(I.RELEASED_DATETIME), 
               DATENAME(MONTH, I.RELEASED_DATETIME)
        );
  
      -- Start of cursor
      DECLARE DTY CURSOR FOR
          SELECT RANGEPERIOD FROM @d
  
      OPEN DTY
  
      -- Fetch the first row
      FETCH NEXT FROM DTY INTO @cur
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          SET @tot_enrls = (SELECT COUNT(DISTINCT I._ID) AS TOT_ENROLLS
                            FROM SVE_INTERACTION_SESSION_T AS I WITH (NOLOCK) 
                            INNER JOIN SVE_ENROLL_SESSION_T AS E WITH (NOLOCK) ON E.INTERACTION_SESSION_ID = I._ID 
                            INNER JOIN SVE_APPLICATIONS_T AS A WITH (NOLOCK) ON I.APPLICATION_ID = A._ID
                            WHERE
                                (I.DEVELOPER_OWNER_ID = @DEV_ID OR I.DEVELOPER_USER_ID = @DEV_ID) 
                                AND E.INSTANCE_ID = 0 
                                AND E.COMPLETION_STATUS = 'T' 
                                AND I.RELEASED_CODE = 'C' 
                                AND A.IS_DELETED = 0 
                                AND CONCAT( SUBSTRING( datename( month, I.RELEASED_DATETIME ), 1, 3 ), '-', YEAR( I.RELEASED_DATETIME ) ) = @cur
              );
  
          SET @tot_verfs = (
              SELECT
                  COUNT( DISTINCT I._ID ) AS TOT_VERIFYS
              FROM
                  SVE_INTERACTION_SESSION_T AS I WITH (NOLOCK) INNER JOIN SVE_VERIFY_SESSION_T AS V WITH (NOLOCK)
              ON
                  V.INTERACTION_SESSION_ID = I._ID INNER JOIN SVE_APPLICATIONS_T AS A WITH (NOLOCK)
              ON
                  I.APPLICATION_ID = A._ID
              WHERE
                  ( I.DEVELOPER_OWNER_ID = @DEV_ID OR I.DEVELOPER_USER_ID = @DEV_ID ) AND
                  V.INSTANCE_ID = 0 AND
                  V.COMPLETION_STATUS = 'S' AND
                  I.RELEASED_CODE = 'C' AND
                  A.IS_DELETED = 0 AND
                  CONCAT( SUBSTRING( datename( month, I.RELEASED_DATETIME ), 1, 3 ), '-', YEAR( I.RELEASED_DATETIME ) ) = @cur
              );
  
       -- Updates the fields in the return table
          UPDATE
              @d
          SET
              TOTAL_ENROLLMENTS = @tot_enrls,
              TOTAL_VERIFICATIONS = @tot_verfs
          WHERE
              RANGEPERIOD = @cur
  
       -- Fetch the next row and repeat the above process
          FETCH NEXT FROM DTY INTO @cur
      END
  
   -- Updates the return table by setting all null values to 0 for better visuals
      UPDATE @d SET TOTAL_ENROLLMENTS = 0, TOTAL_VERIFICATIONS = 0 WHERE TOTAL_ENROLLMENTS IS NULL
  
      RETURN
  END
  </code></pre>
  
  <p>It might be worth noting that I already do something similar for a range of days, and this works flawlessly.  The only difference is I use a calendar table for the day range function.  I know the secret sauce is in the temporary table that gets built from the calendar table, which i attempted to modify the month function to suit, but I'm at this point flustered</p>
  
  <p>Here's the day function which works great</p>
  
  <pre><code>ALTER FUNCTION [dbo].[SVE_GET_ENROL_VERIFY_COUNT_R]( @DEV_ID INT, @NUM_DAYS INT )
  RETURNS @D TABLE
  (
      RANGEDATE           DATE,
      TOTAL_ENROLLMENTS   INT,
      TOTAL_VERIFICATIONS INT
  )
  AS
  BEGIN
      DECLARE @s DATE;          -- start date of range
      DECLARE @e DATE;          -- end date of range
      DECLARE @cur DATE;        -- stores the current record date the cursor points to
      DECLARE @tot_enrls INT;   -- stores the @cur date's enrollment count
      DECLARE @tot_verfs INT;   -- stores the @cur date's verification count
  
      SET @e = getdate();       -- stores today's date as the end date
      SET @s = DATEADD( dd, -( @NUM_DAYS-1 ), @e ); -- Subtract 1 from the incoming day and setup the accurate range of days
  
      INSERT INTO @d ( RANGEDATE )
      (
          SELECT
              RANGEDATE = c.d
          FROM
              Calendar AS c LEFT OUTER JOIN SVE_INTERACTION_SESSION_T as I WITH (NOLOCK)
          ON
              I.RELEASED_DATETIME &gt;= @s AND I.RELEASED_DATETIME &lt; = @e AND
              c.d = CONVERT( DATE, I.RELEASED_DATETIME )
          WHERE
              c.d &gt;= @s AND c.d &lt;= @e
          GROUP BY c.d
      );
  
   -- Start of cursor
      DECLARE DTY CURSOR FOR
  
      SELECT RANGEDATE FROM @d
  
      OPEN DTY
  
   -- Fetch the first row
      FETCH NEXT FROM DTY INTO @cur
  
      WHILE @@FETCH_STATUS = 0
  
      BEGIN
          -- Gets the developer's enrollment count for the current record's day
          SET @tot_enrls = (
              SELECT
                  COUNT( DISTINCT I._ID ) AS TOT_ENROLLS
              FROM
                  SVE_INTERACTION_SESSION_T AS I WITH (NOLOCK) INNER JOIN SVE_ENROLL_SESSION_T AS E WITH (NOLOCK)
              ON
                  E.INTERACTION_SESSION_ID = I._ID INNER JOIN SVE_APPLICATIONS_T AS A WITH (NOLOCK)
              ON
                  I.APPLICATION_ID = A._ID
              WHERE
                  ( I.DEVELOPER_OWNER_ID = @DEV_ID OR I.DEVELOPER_USER_ID = @DEV_ID ) AND
                  E.INSTANCE_ID = 0 AND
                  E.COMPLETION_STATUS = 'T' AND
                  I.RELEASED_CODE = 'C' AND
                  A.IS_DELETED = 0 AND
                  I.RELEASED_DATETIME &gt;= @cur AND 
                  I.RELEASED_DATETIME &lt; dateadd(day,1,@cur)
              );
  
          -- Gets the developer's verification count for the current record's day
          SET @tot_verfs = (
              SELECT
                  COUNT( DISTINCT I._ID ) AS TOT_VERIFYS
              FROM
                  SVE_INTERACTION_SESSION_T AS I WITH (NOLOCK) INNER JOIN SVE_VERIFY_SESSION_T AS V WITH (NOLOCK)
              ON
                  V.INTERACTION_SESSION_ID = I._ID INNER JOIN SVE_APPLICATIONS_T AS A WITH (NOLOCK)
              ON
                  I.APPLICATION_ID = A._ID
              WHERE
                  ( I.DEVELOPER_OWNER_ID = @DEV_ID OR I.DEVELOPER_USER_ID = @DEV_ID ) AND
                  V.INSTANCE_ID = 0 AND
                  V.COMPLETION_STATUS = 'S' AND
                  I.RELEASED_CODE = 'C' AND
                  A.IS_DELETED = 0 AND
                  I.RELEASED_DATETIME &gt;= @cur AND 
                  I.RELEASED_DATETIME &lt; dateadd(day,1,@cur)
              );
  
       -- Updates the fields in the return table
          UPDATE
              @d
          SET
              TOTAL_ENROLLMENTS = @tot_enrls,
              TOTAL_VERIFICATIONS = @tot_verfs
          WHERE
              RANGEDATE = @cur
  
       -- Fetch the next row and repeat the above process
          FETCH NEXT FROM DTY INTO @cur
      END
  
   -- Updates the return table by setting all null values to 0 for better visuals
      UPDATE @d SET TOTAL_ENROLLMENTS = 0, TOTAL_VERIFICATIONS = 0 WHERE TOTAL_ENROLLMENTS IS NULL
  
      RETURN
  END
  </code></pre>
  
  <p>This is what my modification looks like for the month period to use the calendar table but its an epic fail, as it only returns one row.</p>
  
  <pre><code>INSERT INTO @d ( RANGEPERIOD )
  (
      SELECT
          RANGEPERIOD = CONCAT( SUBSTRING( datename( month, c.d ), 1, 3 ), '-', YEAR( c.d ) )
      FROM
          Calendar AS c LEFT OUTER JOIN SVE_INTERACTION_SESSION_T as I WITH (NOLOCK)
      ON
          (YEAR(I.RELEASED_DATETIME) &gt;= YEAR(@s) AND MONTH(I.RELEASED_DATETIME) &gt;= MONTH(@s)) AND (YEAR(I.RELEASED_DATETIME) &lt;= YEAR(@e) AND MONTH(I.RELEASED_DATETIME) &lt;= MONTH(@e)) AND
          c.d = CONVERT( DATE, I.RELEASED_DATETIME )
      WHERE
          (YEAR(c.d) &gt;= YEAR(@s) AND MONTH(c.d) &gt;= MONTH(@s)) AND (YEAR(c.d) &lt;= YEAR(@e) AND MONTH(c.d) &lt;= MONTH(@e))
      GROUP BY c.d
  );
  </code></pre>
  
  <p>Can someone point me in the right direction, or provide an assist?</p>