Split a query results multiple times
Without implementing #2, your query would probably look like this:

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      @starttime StartTime,
      DATEADD(mi, 15, @starttime) EndTime

    UNION ALL

    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)
    FROM
      time_cte
    WHERE
      EndTime &lt; @EndTime
  )
SELECT
  t.StartTime,
  UnitCount = COUNT(d.NumCavalier)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure &gt;= t.StartTime
    AND d.DateHeure &lt; t.EndTime
GROUP BY
  t.StartTime
;


Now in order to split the COUNT(d.NumCavalier) into three values based on the value of FormIdNb, you check that value when counting – like this:

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)

    UNION ALL

    SELECT
      EndTime,
      DATEADD(mi, 15, EndTime)
    FROM
      time_cte
    WHERE
      EndTime &lt; @EndTime
  )
SELECT
  t.StartTime,
  ActiveUnitCount   = COUNT(CASE WHEN d.FormIdNb &lt; 3 THEN d.NumCavalier END),
  IdleUnitCount     = COUNT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END),
  InactiveUnitCount = COUNT(CASE WHEN d.FormIdNb &gt; 3 THEN d.NumCavalier END)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure &gt;= t.StartTime
    AND d.DateHeure &lt; t.EndTime
GROUP BY
  t.StartTime
;


This is called conditional aggregation: you are aggregating the results based on some condition.

One of the charms of the method is that you can still get the total count in the same output, if necessary, by including COUNT(d.NumCavalier) into the resulting column set.