CODEWITHSUNDEEP
sql
Tue
Tue

Reputation: 21

Count records with a criteria like "within days"

I have a table as below on sql.

OrderID Account  OrderMethod     OrderDate  DispatchDate  DispatchMethod 
2145     qaz     14              20/3/2011  23/3/2011     2
4156     aby     12              15/6/2011  25/6/2011     1

I want to count all records that have reordered 'within 30 days' of dispatch date where Dispatch Method is '2' and OrderMethod is '12' and it has come from the same Account.

I want to ask if this all can be achieved with one query or do I need to create different tables and do it in stages as I think I wll have to do now? Please can someone help with a code/query?

Many thanks T

Upvotes: 2

Views: 186

Answers (5)

Shamba
Shamba

Reputation: 31

Try the following, replacing [tablename] with the name of your table. 

SELECT Count(OriginalOrders.OrderID) AS [Total_Orders]
FROM [tablename] AS OriginalOrders  
INNER JOIN [tablename] AS Reorders  
ON OriginalOrders.Account = Reorders.Account  
AND OriginalOrders.OrderDate < Reorders.OrderDate  
AND DATEDIFF(day, OriginalOrders.DispatchDate, Reorders.OrderDate) <= 30  
AND Reorders.DispatchMethod = '2'  
AND Reorders.OrderMethod = '12';

By using an inner join you'll be sure to only grab orders that meet all the criteria.
By linking the two tables (which are essentially the same table with itself using aliases) you make sure only orders under the same account are counted.
The results from the join are further filtered based on the criteria you mentioned requiring only orders that have been placed within 30 days of the dispatch date of a previous order.

Upvotes: 2

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Can we use GROUP BY in this case, such as the following?

SELECT COUNT(Account) 
FROM myTable
WHERE DispatchMethod = 2 AND OrderMethod = 12 
AND DATEDIFF(d, DispatchDate, OrderDate)  <=30
GROUP BY Account

Will the above work or am I missing something here?

Upvotes: 0

BonyT
BonyT

Reputation: 10940

You need a self-join.

The query below assumes that a given account will have either 1 or 2 records in the table - 2 if they've reordered, else 1.

If 3 records exist for a given account, 2 orders + 1 reorder then this won't work - but we'd then need more information on how to distinguish between an order and a reorder.

 SELECT COUNT(*) FROM myTable new, myTable prev
 WHERE new.DispatchMethod = 2
 AND new.OrderMethod = 12
 AND DATEDIFF(day, prev.DispatchDate, new.OrderDate)  <=30
 AND prev.Account == new.Account 
 AND prev.OrderDate < new.OrderDate

Upvotes: 0

Chris Van Opstal
Chris Van Opstal

Reputation: 37537

One query can do it.

SELECT COUNT(*)FROM myTable reOrder
INNER JOIN myTable originalOrder
  ON reOrder.Account = originalOrder.Account
  AND reOrder.OrderID <> originalOrder.OrderID
  -- all re-orders that are within 30 days or the 
  -- original orders dispatch date
  AND DATEDIFF(d, originalOrder.DispatchDate, reOrder.OrderDate) <= 30
WHERE reOrder.DispatchMethod = 2
  AND reOrder.OrderMethod = 12

Upvotes: 0

Coeffect
Coeffect

Reputation: 8866

Totally possible with one query, though my SQL is a little stale..

select count(*) from table 
where DispatchMethod = 2
    AND OrderMethod = 12
    AND DATEDIFF(day, OrderDate, DispatchDate) <= 30;

(Untested, but it's something similar)

Upvotes: 0

Related Questions