Group by hour, month etc

Hi All,

I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries

1. Get number of orders per year

2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)

3. Get number of orders per hour

Note: If there are no orders in a year etc, it should return a row with 0.

You need to use the DatePart function in your SQL.|||

1.

select datepart(year, OrderTime), count(*) from table group by datepart(year, OrderTime)

or

select year(OrderTime), count(*) from table group by year(OrderTime)

or

select dateadd(year, datediff(year, 0, OrderTime), 0), count(*) fromtable group by dateadd(month, datediff(month, 0, OrderTime), 0)

2.

select dateadd(month, datediff(month, 0, OrderTime), 0), count(*) from table group by dateadd(month, datediff(month, 0, OrderTime), 0)

3.

select dateadd(hour, datediff(hour, 0, OrderTime), 0), count(*) from table group by dateadd(hour, datediff(hour, 0, OrderTime), 0)

|||

sachinsurana:

Note: If there are no orders in a year etc, it should return a row with 0.

No order for which year ? Year 2008, 2009, 3000 ? You have to define what are the range of year. If you have a calendar table, use it to LEFT JOIN to your table.

|||I dont have a calender table and I can't create one as we are not supposed to modify the database.|||

then create one on the fly

select [year]

from

(

select 2000 as [year] union all select 2001 as [year] union all select 2002 as [year]

) y

or

make use of this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685