Orders Per Hour Count

SQL Apprentice Question
Hello,

I have a table with 3 columns


ProductID int, Product varchar(30), OrderDate datetime


I have orders in this table from January to July I want to get a query that
will display how many orders I am getting per day and per hour.


Celko Answers
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. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You talk about orders, then post your personal pseudo-code about
products. Does that make sense to you?



>> I have orders in this table from January to July I want to get a query that will display how many orders I am getting per day and per hour. <<


Build a table of hourly report ranges. Think about using a spreadsheet
or a simple program to give you a year's worth of ranges -- 365 * 24 =
8760 rows

CREATE TABLE ReportRanges
(period_name CHAR(15) NOT NULL PRIMARY KEY, -- or use start_date
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
etc.) ;


Then the query is simply:


SELECT R.period_name, COUNT(*) AS hourly_cnt
FROM ReportRanges AS R, Orders AS O
WHERE O.order_date BETWEEN R.start_date AND R.end_date
GROUP BY R.period_name;