title: SQL Server: Group results on time interval
body: <p>I've got an MS SQL Server table that records our plant's alarm events with a row for each alarm and a datetime column to capture when the alarm happened. </p>
  
  <p>We run our plant in 12 hour shifts (6 am to 6pm, 6pm to 6am) and I need to figure out how many alarms we're getting each shift. How do I group my results to get that?</p>
  
  <p>The original table looks something like this:</p>
  
  <pre><code>DateTime               Alarm Name
  2010-01-05 14:32:22    Overpressure
  2010-01-05 21:32:59    Underspeed
  2010-01-06 05:58:13    Underspeed
  2010-01-06 06:02:46    Machine Current Fault
  </code></pre>
  
  <p>And we need to group the results something like this:</p>
  
  <pre><code>Date        Shift     Count 
  2010-01-05  Day       1  
  2010-01-05  Night     2 
  2010-01-06  Day       1
  </code></pre>
  
  <p>Note that if alarms happen between 6 pm on say Jan 5th and 6 am on Jan 6th, they all get counted as Night Shift from Jan 5th.</p>
  
  <p>Any advice?</p>