Split a query results multiple times

I need to create a procedure to make statistics on my database.

First, I decided to create a temporary table to “simplify” my work.

It contains all the relevant actions (FormIdNb) of the working units (NumCavalier) on one day.

-- Every action from all units will be stored in this temp table
-- DateHeure = Time of action
-- NumCavalier = ID of working unit
-- FormIdNb = Action type
CREATE TABLE #tmp_DailyMissions (
DateHeure datetime,
NumCavalier  nchar(3),
FormIdNb int
)

INSERT INTO #tmp_DailyMissions
SELECT        DateHeure, Cavalier.NumCavalier, FormIdNb
FROM            ECN4, Cavalier
WHERE        (DateHeure BETWEEN @StartTime AND @EndTime) AND (Cavalier.Terminal = @terminal) AND (Cavalier.NumCavalier = ECN4.NumCavalier)

Problem is, I’m not able to make the request to make what I want :

1. I need to split the results by time interval (15min for 1 day)
2. I need to split again on the maximum value of ‘FormIdNb’ of each working unit ‘NumCavalier’ as :

FormIdNb > 3 ==> The working unit was active on that time interval

FormIdNb = 3 ==> The working unit was idle on that time interval (if not active on that same time interval)

FormIdNb < 3 ==> The working unit was inactive on that time interval (if not active or idle on that same time interval)

3. And finally, make a count for each time interval of working units depending the previous value.

I’ve already done this, it works partially, but it does not take into account the values of the preceding joins (for example : a working unit can appear active and idle at the same time, and so on…).

I think that I have to make a lot of changes to make it work :

;WITH 
time_cte(StartTime, EndTime) AS
(
SELECT @starttime StartTime, DATEADD(mi, 15, @starttime) EndTime
UNION ALL
SELECT EndTime, DATEADD(mi, 15, EndTime) FROM time_cte
WHERE EndTime < @EndTime
)

SELECT StartTime, Count(DISTINCT CavsTerminal_ACTIVE.NumCavalier),
Count(DISTINCT CavsTerminal_IDLE.NumCavalier), 
Count(DISTINCT CavsTerminal_INACTIVE.NumCavalier)
FROM time_cte
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) > 3
) CavsTerminal_ACTIVE ON  CavsTerminal_ACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) = 3
) CavsTerminal_IDLE ON  (CavsTerminal_IDLE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) < 3
) CavsTerminal_INACTIVE ON  (CavsTerminal_INACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
GROUP BY StartTime

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 < @EndTime
  )
SELECT
  t.StartTime,
  UnitCount = COUNT(d.NumCavalier)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < 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 < @EndTime
  )
SELECT
  t.StartTime,
  ActiveUnitCount   = COUNT(CASE WHEN d.FormIdNb < 3 THEN d.NumCavalier END),
  IdleUnitCount     = COUNT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END),
  InactiveUnitCount = COUNT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < 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.