CODEWITHSUNDEEP
sqldaterange
kiwijus
kiwijus

Reputation: 1237

SQL count date range

I'm trying to query my SQL database to get the number of orders made by each client within a certain date range.

I have a list of orders as follows

CustomerName       ClientID        DateOrdered
Customer No.2         10            2011-11-25
Customer No.3         11            2011-10-15
Customer No.3         11            2011-11-25

and I want to be able to find out how many orders have been made by a specific client for example between 2011-11-1 and 2011-11-30, this should result in :

CustomerName       ClientID        Number
Customer No.3         11             1
Customer No.2         10             1

So far I've managed to get this 

SELECT CustomerName, ClientID, COUNT(*) AS Number      
FROM Orders t     
GROUP BY CustomerName, ClientID      
HAVING COUNT(*) =      
(SELECT MAX(Number)         
FROM          
(SELECT CustomerName, ClientID, COUNT(*) AS Number             
FROM Orders            
GROUP BY CustomerName, ClientID ) x       
WHERE CustomerName = t.CustomerName )

Which gives me every order the customer has ever made

CustomerName       ClientID        Number
Customer No.3         11             2
Customer No.2         10             1

Am I going about the right way to solve this or is there a simpler way which I've completely overlooked!

Upvotes: 4

Views: 33514

Answers (4)

Michał Powaga
Michał Powaga

Reputation: 23183

Should work fine:

select CustomerName, ClientID, count(*) as Number
from Orders
where DateOrdered between '20111101' and '20111130'
group by CustomerName, ClientID

Upvotes: 9

user596075
user596075

Reputation: 

select CustomerName, ClientID, count(*)
from
(
    select CustomerName, ClientID
    from Orders
    where datediff(mm, DateOrdered, getdate()) <= 1
 )a
group by CustomerName, ClientID

What this does is utilize a subquery that filters the rows by the dates in a given month (that seems to be what you are looking for). Then it groups by the CustomerName and ClientID and gets the sum of their orders.

Upvotes: 6

Prisoner
Prisoner

Reputation: 27638

SELECT
  c.CustomerName,
  count(o.OrderID) as Orders
FROM
  orders o
JOIN clients c ON o.ClientID = c.ClientID
WHERE
  o.DateOrdered BETWEEN '2011-11-01' AND '2011-11-20'
GROUP BY c.ClientID ;

Upvotes: 1

user359040
user359040

Reputation: 

select ClientID, max(CustomerName), count(*) 
from Orders 
where DateOrdered between '2011-11-01' and '2011-11-30'
group by ClientID

Depending on database, the syntax of the DateOrdered selection may need to be varied.

Upvotes: 1

Related Questions