This query pulls signal data for a month and does it for each hour of each day.
This was built by a customer.
use MANITOU SELECT CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),101) + ' - ' + DATENAME(DW,(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME))) 'Date', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '00:00:00' AND '00:59:59' THEN 1 ELSE 0 END) 'Hour 00', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '01:00:00' AND '01:59:59' THEN 1 ELSE 0 END) 'Hour 01', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '02:00:00' AND '02:59:59' THEN 1 ELSE 0 END) 'Hour 02', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '03:00:00' AND '03:59:59' THEN 1 ELSE 0 END) 'Hour 03', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '04:00:00' AND '04:59:59' THEN 1 ELSE 0 END) 'Hour 04', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '05:00:00' AND '05:59:59' THEN 1 ELSE 0 END) 'Hour 05', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '06:00:00' AND '06:59:59' THEN 1 ELSE 0 END) 'Hour 06', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '07:00:00' AND '07:59:59' THEN 1 ELSE 0 END) 'Hour 07', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '08:00:00' AND '08:59:59' THEN 1 ELSE 0 END) 'Hour 08', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '09:00:00' AND '09:59:59' THEN 1 ELSE 0 END) 'Hour 09', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '10:00:00' AND '10:59:59' THEN 1 ELSE 0 END) 'Hour 10', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '11:00:00' AND '11:59:59' THEN 1 ELSE 0 END) 'Hour 11', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '12:00:00' AND '12:59:59' THEN 1 ELSE 0 END) 'Hour 12', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '13:00:00' AND '13:59:59' THEN 1 ELSE 0 END) 'Hour 13', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '14:00:00' AND '14:59:59' THEN 1 ELSE 0 END) 'Hour 14', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '15:00:00' AND '15:59:59' THEN 1 ELSE 0 END) 'Hour 15', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '16:00:00' AND '16:59:59' THEN 1 ELSE 0 END) 'Hour 16', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '17:00:00' AND '17:59:59' THEN 1 ELSE 0 END) 'Hour 17', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '18:00:00' AND '18:59:59' THEN 1 ELSE 0 END) 'Hour 18', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) 'Hour 19', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '20:00:00' AND '20:59:59' THEN 1 ELSE 0 END) 'Hour 20', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '21:00:00' AND '21:59:59' THEN 1 ELSE 0 END) 'Hour 21', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '22:00:00' AND '22:59:59' THEN 1 ELSE 0 END) 'Hour 22', SUM (CASE WHEN (CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),108)) BETWEEN '23:00:00' AND '23:59:59' THEN 1 ELSE 0 END) 'Hour 23' FROM CLOG201502 C JOIN CUSTOMER -- replace this with the month you want to seek ON C.SERIALNO = CUSTOMER.SERIALNO JOIN CONTACT ON CUSTOMER.CONTID = CONTACT.CONTID WHERE C.EVTYPE = 1 --AND (REPLACE(@DateFrom, ' ', '') = '' OR @DateFrom IS NULL OR (CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)) BETWEEN @DateFrom AND @DateTo) GROUP BY CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),101) + ' - ' + DATENAME(DW,(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME))) ORDER BY CONVERT(nvarchar(30),(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)),101) + ' - ' + DATENAME(DW,(CAST (DATEADD(MINUTE, -300, C.EVDATE) AS DATETIME)))
