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
LVL 1
AFIF JABADOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopCommented:
Add DATEPART(hh, datetimein) to the grouping.
0
AFIF JABADOAuthor Commented:
i have added it but i want to get a format like AM PM
0
sognoctCommented:
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)

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
dbbishopCommented:
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)

Open in new window

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.
0
dbbishopCommented:
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)

Open in new window

0
AFIF JABADOAuthor Commented:
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
0
dbbishopCommented:
Are you using SQL Server 2008?
0
dbbishopCommented:
The code I provided would give you smething in the format of 
reqport	Date	Midnight	1:00 AM	2:00 AM	11:00 PM
abcd	2012-12-01	4	1	0	0
xyz	2012-12-01	1	0	0	0
abcd	2012-12-02	0	0	1	0
xyz	2012-12-03	0	0	0	0

Open in new window

(with better formatting). I've tested it. Obviously, if you code the remaining times in the CASE statement, it will give you all 24 hours across the report.

If you want each in a separate row, try:
SELECT reqport,  
CAST(datetimein AS DATE) AS Date,
 CASE WHEN DATEPART(hh, datetimein) = 0 THEN 'Midnight' 
		WHEN DATEPART(hh, datetimein) = 12 THEN 'Noon' 
		WHEN DATEPART(hh, datetimein) BETWEEN 1 AND 11 THEN DATENAME(HH, datetimein) + ':00 AM' 
		WHEN DATEPART(hh, datetimein) BETWEEN 13 AND 23 THEN DATENAME(HH, datetimein) + ':00 PM' 
END AS HOUR, 
	COUNT(*)
FROM chat
GROUP BY reqport,  
CAST(datetimein AS DATE),
CASE WHEN DATEPART(hh, datetimein) = 0 THEN 'Midnight' 
		WHEN DATEPART(hh, datetimein) = 12 THEN 'Noon' 
		WHEN DATEPART(hh, datetimein) BETWEEN 1 AND 11 THEN DATENAME(HH, datetimein) + ':00 AM' 
		WHEN DATEPART(hh, datetimein) BETWEEN 13 AND 23 THEN DATENAME(HH, datetimein) + ':00 PM' 
END

Open in new window

If you are not running SQL Server 2008, which it appears you are, since you've put this question in the SQL Server 2008 zone, try changing CAST(datetimein AS DATE) to CONVERT(VARCHAR(10), datetimein, 112)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AFIF JABADOAuthor Commented: 
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.