Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how i can count record by datetime group by day view per hours

Posted on 2012-12-27
9
Medium Priority
?
492 Views
Last Modified: 2012-12-27
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
0
Comment
Question by:afifosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 38724967
Add DATEPART(hh, datetimein) to the grouping.
0
 
LVL 1

Author Comment

by:afifosh
ID: 38724983
i have added it but i want to get a format like AM PM
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38724988
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:dbbishop
ID: 38724999
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 38725019
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
 
LVL 1

Author Comment

by:afifosh
ID: 38725061
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 38725072
Are you using SQL Server 2008?
0
 
LVL 15

Accepted Solution

by:
dbbishop earned 2000 total points
ID: 38725092
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
 
LVL 1

Author Comment

by:afifosh
ID: 38725114
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question