SELECT count(0) as nMessages, convert(nvarchar, datetimein, 103) as currDate, RIGHT(CONVERT(VARCHAR,datetimein, 100),7) as currHour
FROM [radiomix].[dbo].[chat]
GROUP BY convert(nvarchar, datetimein, 103), RIGHT(CONVERT(VARCHAR,datetimein, 100),7)
SELECT reqport,
SUM(CASE WHEN DATEPART(hh, datetimein) = 0 THEN 1 ELSE 0 END) AS 'Midnight',
SUM(CASE WHEN DATEPART(hh, datetimein) = 1 THEN 1 ELSE 0 END) AS '1:00 AM',
SUM(CASE WHEN DATEPART(hh, datetimein) = 2 THEN 1 ELSE 0 END) AS '2:00 AM',
-- additional times here
SUM(CASE WHEN DATEPART(hh, datetimein) = 23 THEN 1 ELSE 0 END) AS '11:00 PM'
FROM radiomix.dbo.chat
GROUP BY reqport,
YEAR(datetimein),
MONTH(datetimein),
DAY(datetimein)
It isn't the cleanest, and you may be able to play around and use the PIVOT function, but seeing as how your time (I'm sure) contains minutes and seconds, it would still take some work.
SELECT reqport,
CAST(datetimein AS DATE) AS Date,
SUM(CASE WHEN DATEPART(hh, datetimein) = 0 THEN 1 ELSE 0 END) AS 'Midnight',
SUM(CASE WHEN DATEPART(hh, datetimein) = 1 THEN 1 ELSE 0 END) AS '1:00 AM',
SUM(CASE WHEN DATEPART(hh, datetimein) = 2 THEN 1 ELSE 0 END) AS '2:00 AM',
-- additional times here
SUM(CASE WHEN DATEPART(hh, datetimein) = 23 THEN 1 ELSE 0 END) AS '11:00 PM'
FROM dbo.chat
GROUP BY reqport,
CAST(datetimein AS DATE)
SELECT reqport,
YEAR(datetimein) as years,
MONTH(datetimein) as months,
DAY(datetimein) as days,
SUM(CASE WHEN DATEPART(hh, datetimein) = 0 THEN 1 ELSE 0 END) AS 'Midnight',
SUM(CASE WHEN DATEPART(hh, datetimein) = 1 THEN 1 ELSE 0 END) AS '1:00 AM',
SUM(CASE WHEN DATEPART(hh, datetimein) = 2 THEN 1 ELSE 0 END) AS '2:00 AM',
-- additional times here
SUM(CASE WHEN DATEPART(hh, datetimein) = 22 THEN 1 ELSE 0 END) AS '10:00 PM',
SUM(CASE WHEN DATEPART(hh, datetimein) = 23 THEN 1 ELSE 0 END) AS '11:00 PM'
FROM radiomix.dbo.chat
GROUP BY reqport,
YEAR(datetimein),
MONTH(datetimein),
DAY(datetimein)
ORDER BY
YEAR(datetimein) DESC,
MONTH(datetimein) DESC,
DAY(datetimein) DESC