SQL Server: Group results on time interval

Question

Hey guys,

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?

Answer 1

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