Member_2_6478753
asked on
how i can count record by datetime group by day view per hours
i have the following table
select reqport,count(message)
FROM [radiomix].[dbo].[chat]
GROUP BY reqport
, YEAR(datetimein)
, MONTH(datetimein)
, DAY(datetimein)
but i want to group by day per hours + ???
i want to show like 1:00 AM 2:00Am ...... 11:59 PM
and each hours the number of message
select reqport,count(message)
FROM [radiomix].[dbo].[chat]
GROUP BY reqport
, YEAR(datetimein)
, MONTH(datetimein)
, DAY(datetimein)
but i want to group by day per hours + ???
i want to show like 1:00 AM 2:00Am ...... 11:59 PM
and each hours the number of message
Add DATEPART(hh, datetimein) to the grouping.
ASKER
i have added it but i want to get a format like AM PM
can't test, but try this :
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)
If you are wanting to see the hours across the report, try this:
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.
The above won't give you the date. Use
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)
ASKER
dbbishop
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
sognoct your code now work :D
i want to count the message for example
MIDNIG. TO 1:00AM
1:00AM TO 2:00AM
TO
11:00PM TO 11:59PM
FOR EACH REQPORT
AND THE GROUP SHOULD BE
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
sognoct your code now work :D
i want to count the message for example
MIDNIG. TO 1:00AM
1:00AM TO 2:00AM
TO
11:00PM TO 11:59PM
FOR EACH REQPORT
AND THE GROUP SHOULD BE
Are you using SQL Server 2008?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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