Solved

sql query group by hour

Posted on 2014-01-02
2
2,471 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 24

Accepted Solution

by:
chaau earned 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now