SQL Server: Group Results by Time Interval

Question

I have a MS SQL Server table that logs our emergency events at a station with a row for each alarm and a datetime column to capture when an alarm occurs.

We start our plant in a 12-hour shift (from 6:00 to 18:00, from 6:00 to 6:00), and I need to find out how many alarms we receive each shift. How do I group results to get this?

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 like this:

Date        Shift     Count 
2010-01-05  Day       1  
2010-01-05  Night     2 
2010-01-06  Day       1

Please note that if the alarm occurs from 6:00 p.m. to January 5 and 6 a.m. on January 6, all of them count as a night descent from January 5.

Any tips?

Answer 1

/ , 6 .

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