[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1077
  • Last Modified:

Select Query - Total Orders Per Day / Month

Assuming I have a table of orders and a date range, how might I go about writing a select query to return just the total number of orders per day / month within that date range?

Order Table
OrderID, DateOrdered, Value, Customer Name (etc)

Ideal Query Results
Day 1 - Total Value - Number of Orders
Day 2 - Total Value - Number of Orders
...

or similar for months rather than days.  Is this possible just using TSQL or will I need to use some sort of reporting addon?
0
Psychotext
Asked:
Psychotext
  • 3
  • 2
  • 2
  • +1
1 Solution
 
St3veMaxCommented:
It's possible...

Something like below should set you off in the right direction:
SELECT
	CONVERT(Varchar(11), DateOrdered, 103)
	,COUNT(OrderID)
FROM
	X
ORDER BY
	DateOrdered ASC

Open in new window

0
 
Pratima PharandeCommented:
per day
select Convert(varchar(20),DateOrdered),101) , count(*)
From ORDER_TABLE
Group by Convert(varchar(20),DateOrdered),101)
0
 
Pratima PharandeCommented:
per day
select Convert(varchar(20),DateOrdered,101) , count(*)
From ORDER_TABLE
Group by Convert(varchar(20),DateOrdered,101)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Pratima PharandeCommented:
per month
select  Month(DateOrdered) ,Year(DateOrdered), count(*)
From ORDER_TABLE
Group by  Month(DateOrdered) ,Year(DateOrdered)
0
 
PsychotextAuthor Commented:
Thanks.  Will award to pratima as the examples are exactly what I need without modification.

Noted that CAST(FLOOR(CAST(Datecolumn AS FLOAT)) AS DATETIME) seems to work much faster than converting to varchar.
0
 
Mark WillsTopic AdvisorCommented:
well, here is my two cents worth - pretty much the same as above, just with all the bits you asked for.... Including an order by to make sure it shows in desired datetime sequence (which should do anyway).

select convert(varchar,orderdate,106),  count(distinct orders) as number_of_orders, sum(Value) as daily_order_value
from orders
group by convert(varchar,orderdate,106)
order by convert(datetime,convert(varchar,orderdate,106))
0
 
Mark WillsTopic AdvisorCommented:
didn't realise how long I had this open for... sorry about that.

also, don't use float, it might appear to be quicker mainly because it retains datetime construct, but use : select CAST(floor(CAST(getdate() AS decimal(18,6))) AS DATETIME)   float is an approximation and decimal is an absolute numeric.
0
 
PsychotextAuthor Commented:
Thanks for the decimal comment.  Interesting.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now