title: SQL Count Unacknowledged Alarms
body: <p>I'm looking to count the number of alarms that became active in a time range but were not acknowledged and the number of alarms that were not cleared. There are three types of events that are all stored in the same table: alarm activation, acknowledgment, and clearing.</p>
  <p>The table I am querying looks kind of like this.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">ID</th>
  <th style="text-align: center;">Alarm Name</th>
  <th style="text-align: center;">Event Type</th>
  <th style="text-align: center;">Is Active</th>
  <th style="text-align: center;">Is Acknowledged</th>
  <th style="text-align: center;">Date Time</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">Alarm 1</td>
  <td style="text-align: center;">Acknowledged</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">12/22/2020 06:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">Alarm 1</td>
  <td style="text-align: center;">Cleared</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">12/22/2020 06:30:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">Alarm 2</td>
  <td style="text-align: center;">Active</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">12/22/2020 07:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">Alarm 3</td>
  <td style="text-align: center;">Active</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">12/22/2020 07:30:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">Alarm 2</td>
  <td style="text-align: center;">Acknowledged</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">12/22/2020 08:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">Alarm 2</td>
  <td style="text-align: center;">Connection Lost</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">12/22/2020 08:30:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">7</td>
  <td style="text-align: center;">Alarm 2</td>
  <td style="text-align: center;">Connection Regained</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">12/22/2020 09:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">8</td>
  <td style="text-align: center;">Alarm 3</td>
  <td style="text-align: center;">Cleared</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">12/22/2020 09:30:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">9</td>
  <td style="text-align: center;">Alarm 2</td>
  <td style="text-align: center;">Cleared</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">12/22/2020 10:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">Alarm 3</td>
  <td style="text-align: center;">Active</td>
  <td style="text-align: center;">True</td>
  <td style="text-align: center;">False</td>
  <td style="text-align: center;">12/22/2020 10:30:00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The results from this table would be 2 alarms not acknowledged and 1 alarm not cleared.
  The first two rows should be ignored because the initial Active event is not in the time range. Alarm 2 becomes active, acknowledged and then cleared. Alarm 3 becomes active and is cleared before being acknowledged. Alarm 3 becomes active again but there are no acknowledgment or clearing events.</p>
  <p>I had to do something similar with a different database where every row event for the same alarm had an id. In that case I joined the acknowledgement events and the clearing events to the active events using that id and then counted where the joins were null. But here, I don't have that alarm id. I wrote a query that would add a unique ID. Here it is:</p>
  <pre><code>SELECT AlarmName,
  SUM(CASE WHEN EventType = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY AlarmName ORDER BY DateTime) as InstanceID
  FROM AllEvent
  ORDER BY AlarmName, InstanceID
  </code></pre>
  <p>Any ideas?</p>