Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Course Of The Month
8 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how i can count record by datetime group by day view per hours
Posted on 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
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
[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
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)
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)
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
The code I provided would give you smething in the format of
If you want each in a separate row, try:
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
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
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
Featured Post
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 9 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.