locked
A Simple Query with RRS feed

  • 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. 

    Wednesday, April 3, 2013 7:21 PM

Answers

  • 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

    • Proposed as answer by Naomi N Wednesday, April 3, 2013 8:01 PM
    • Marked as answer by XYZ001 Wednesday, April 3, 2013 10:24 PM
    Wednesday, April 3, 2013 7:37 PM

All replies

  • 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

    Wednesday, April 3, 2013 7:25 PM
  • 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

    • Proposed as answer by Naomi N Wednesday, April 3, 2013 8:01 PM
    • Marked as answer by XYZ001 Wednesday, April 3, 2013 10:24 PM
    Wednesday, April 3, 2013 7:37 PM
  • 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

    Wednesday, April 3, 2013 8:50 PM