[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query group by hour

Posted on 2014-01-02
2
Medium Priority
?
3,734 Views
Last Modified: 2014-01-06
I have a table named LogEntries that has the following structure




Logdatetime (datetime)

Entries (integer)
Exits (integer)


Here is some sample data


LogDateTime                        Entries      Exits
2013-12-20 08:30:00.000      0                 3      
2013-12-20 08:35:00.000      5                  2
2013-12-20 08:55:00.000      12               7
2013-12-21 09:20:00.000      10                 3      
2013-12-21 09:35:00.000      6                 2
2013-12-21 09:59:00.000      27               4
2013-12-23 17:20:00.000      6                1
2013-12-23 17:48:00.000      6                2
2013-12-23 17:59:00.000      7               3


Trying to write a query that will return sum of entries and exists by date and hour of date

using example above I am looking for following output

Logdate               Hour                 entries    exits

2013-12-20               8                       17                 12      
2013-12-21               9                       43              9
2013-12-23              17                    19              6
0
Comment
Question by:johnnyg123
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39752756
Yes, it is possible:
select CAST(LogDateTime AS Date) as LogDate
, datepart(hh, LogDateTime) as Hour
, Sum(Entries) as Entries
, Sum(Exits) as Exits
FROM Table1
Group By CAST(LogDateTime AS Date)
, datepart(hh, LogDateTime)

Open in new window

0
 
LVL 8

Expert Comment

by:Surrano
ID: 39753359
Using dateadd:
select dateadd(hour, datediff(hour, 0, logdatetime), 0) as loghour,
  sum(entries) as entries, sum(exits) as exits)
from LogEntries 
-- where <any condition>
group by dateadd(hour, datediff(hour, 0, logdatetime), 0) 
order by dateadd(hour, datediff(hour, 0, logdatetime), 0);

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

830 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