A Simple Query with

Question

I have following table.

OrderID	CreatedDateTime
001	2013-04-02 03:04:00.427
002	2013-04-02 03:11:19.133
003	2013-04-02 04:14:41.457
004	2013-04-02 04:19:54.037
005	2013-04-02 05:24:17.730
006	2013-04-03 05:24:17.750
007	2013-04-02 01:12:44.930
008	2013-04-01 01:04:00.427

Now I would like know how many orders placed yesterday in every hour. Basically I would like to have following report:

Number Of Order	Date
0	2013-04-02 00:00:00
1	2013-04-02 01:00:00
0	2013-04-02 02:00:00
2	2013-04-02 03:00:00
2	2013-04-02 04:00:00

Any help would be highly appreciated. 

Answer 1

Couple options for you here:

http://stackoverflow.com/questions/7001718/sql-server-group-by-count-of-datetime-per-hour

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 2

create table test (order_ID varchar(10), CreatedDateTime datetime)
Insert into test values ('001',
'2013-04-02 03:04:00.427'),
('002','2013-04-02 03:11:19.133'),
('003',
'2013-04-02 04:14:41.457'),
('004',
'2013-04-02 04:19:54.037'),
('005',
'2013-04-02 05:24:17.730'),
('006',
'2013-04-03 05:24:17.750'),
('007',
'2013-04-02 01:12:44.930'),
('008',
'2013-04-01 01:04:00.427')

SELECT  Count(order_ID) as [Number Of Order],DATEADD(hour, DATEDIFF(hour,0,CreatedDateTime),0)  WholeHour
FROM  test
Group by DATEADD(hour, DATEDIFF(hour,0,CreatedDateTime),0) 

drop table test

Answer 3

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. We now to copy your picture into code by hand. Are you this rude to the people you with whom you work? 

CREATE TABLE Orders
(order_nbr CHAR(3) NOT NULL PRIMARY KEY,
 creation_dater DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL);

INSERT INTO Orders
VALUES('001,'2013-04-02 03:04:00'),
('002', '2013-04-02 03:11:19'),
('003', '2013-04-02 04:14:41'),
('004', '2013-04-02 04:19:54'),
('005', '2013-04-02 05:24:17'),
('006', '2013-04-03 05:24:17'),
('007', '2013-04-02 01:12:44'),
('008', '2013-04-01 01:04:00');


Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

CREATE TABLE Hourly_Report_Periods
(hourly_report_name CHAR(16) NOT NULL PRIMARY KEY
 CHECK (hourly_report_name 
        LIKE '[12][0-9][0-9][0-9]-[01][0-9]-[01][0-9] [0-2][0-9]:[0-5][0-9]'),
 hourly_report_start_timestamp DATETIME2(1) NOT NULL,
 hourly_report_end_timestamp DATETIME2(1) NOT NULL,
 CONSTRAINT Datetime_Ordering
 CHECK (hourly_report_start_timestamp <= hourly_report_end_timestamp),
etc);

INSERT INTO Hourly_Report_Periods 
VALUES ('2013-04-02 00:00', '2013-04-02 00:00:00.0', '2013-04-02 00:00:59.9
       ('2013-04-02 01:00', '2013-04-02 00:01:00.0', '2013-04-02 00:01:59.9
 etc.

Now a simple BETWEEN predicate puts the orders into buckets. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL