SQL Server: Group results on time interval

datetime, group-by, sql-server

Solution

In this solution, I work out the shift start/end times by subtracting 6 hours from the event time.

DECLARE @t TABLE
([DateTime] DATETIME
,[Alarm Name] VARCHAR(30)
)

INSERT @t
      SELECT '2010-01-05 14:32:22','Overpressure'
UNION SELECT '2010-01-05 21:32:59','Underspeed'
UNION SELECT '2010-01-06 05:58:13','Underspeed'
UNION SELECT '2010-01-06 06:02:46','Machine Current Fault'



SELECT CONVERT(CHAR(10),DATEADD(hh,-6,[DateTime]),120) AS date
       ,CASE WHEN DATEPART(hh,DATEADD(hh,-6,[DateTime])) < 12
            THEN 'day'
            ELSE 'night'
        END  AS shift
       ,COUNT(1) AS cnt
FROM @t
GROUP BY CONVERT(CHAR(10),DATEADD(hh,-6,[DateTime]),120) 
       ,CASE WHEN DATEPART(hh,DATEADD(hh,-6,[DateTime])) < 12
            THEN 'day'
            ELSE 'night'
        END  
order by 1,2

Problem

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. 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? The original table looks something like this: ``` 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 ``` And we need to group the results something like this: ``` Date Shift Count 2010-01-05 Day 1 2010-01-05 Night 2 2010-01-06 Day 1 ``` 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. Any advice?

Original source