Thread: Recurring Order Count Query - How many recurring orders have we had this month?

Hi All, I'm trying to come up with a query that returns a count of recurring orders for a given month that were CAPTURED. Is it as easy as follows?

Select COUNT(o.OrderNumber)
FROM Orders o
WHERE Month(o.OrderDate)= 12 and Year(o.OrderDate) = 2010
and o.CartType = 2
AND o.OrderTotal > 0 AND o.TransactionState = 'CAPTURED'


Are these true statements:

• A recurring order must have a ParentOrderNumber in the Orders table.
• A recurring order must have a CartType of 2 (Recurring Cart) in the Orders table.

Are there other fields that determine if the order was a recurring order?

Sorry to bring up an old thread. But I found this post with no replies and thought maybe someone might find my input useful even after all this time.

The OP's query as written, because it queries the order date column twice requires a Clustered Index SCAN on the Orders table. First add to the where clause a reference to the Orders table's Primary Key (OrderNumber > 0 will work). Then comment out either the month or year in the where clause and see that the query then uses a Clustered Index SEEK. But if BOTH the month and year are referenced the query still does a Clustered Index SCAN.

Code:

SELECT
    COUNT(o.OrderNumber) AS OrderCount
FROM
    Orders AS o
WHERE
    OrderNumber > 0
    AND MONTH(o.OrderDate) = 5
    AND YEAR(o.OrderDate) = 2012
    AND o.CartType = 2
    AND o.OrderTotal > 0
    AND o.TransactionState = 'CAPTURED'

So how to get around this?

First option:

Declare the first date of the month, then use that date to define the end period (i.e., '20120601' in this example). It's important to make sure the DATEADD function is on the right side of the equation in order to make the statement 'sargable' to avoid having to scan every row.

Code:

DECLARE @DateStart DATETIME
SET @DateStart = '20120501'
    
SELECT
    COUNT(o.OrderNumber) AS OrderCount
FROM
    Orders AS o
WHERE
    OrderNumber > 0
    AND o.OrderDate >= @DateStart
    AND o.OrderDate < DATEADD(MONTH, 1, @DateStart)
    AND o.CartType = 2
    AND o.OrderTotal > 0
    AND o.TransactionState = 'CAPTURED'

Second option:

The first option is somewhat limited to a single time period. The DateStart could vary of course as well as the parameters of the DateAdd function but it still requires a formatted date to be passed to the procedure. By use of a NUMBERS or TALLY table the query can be expanded to include one month or a range of months and the resulting query does the desired Clustered Index Seeks. For information on creating and using a Tally table (a most useful tool) see: http://www.sqlservercentral.com/articles/T-SQL/62867/

Code:

DECLARE	
     @Month TINYINT
    ,@Year INT
    ,@CartType TINYINT 
        -- ShoppingCart = 0, WishCart = 1, RecurringCart = 2, GiftRegistryCart = 3
	
SET @Month = 5
SET @Year = 2012
SET @CartType = 2

SELECT
    COUNT(o.OrderNumber) AS OrderCount
FROM
    Orders AS o
    INNER JOIN Tally AS t
        ON t.N = @Month
WHERE
    OrderNumber > 0
    AND o.CartType = @CartType
    AND o.OrderTotal > 0
    AND o.TransactionState = 'CAPTURED'
    AND DATEPART(yyyy,o.OrderDate) = @Year

Or to do the query on a range:

Code:

DECLARE	
     @StartMonth TINYINT
    ,@EndMonth TINYINT
    ,@Year INT
    ,@CartType TINYINT 
        -- ShoppingCart = 0, WishCart = 1, RecurringCart = 2, GiftRegistryCart = 3
	
SET @StartMonth = 1
SET @EndMonth = 3
SET @Year = 2012
SET @CartType = 2

SELECT
    COUNT(o.OrderNumber) AS OrderCount
FROM
    Orders AS o
    INNER JOIN Tally AS t
        ON t.n BETWEEN @StartMonth AND @EndMonth
WHERE
    OrderNumber > 0
    AND o.CartType = @CartType
    AND o.OrderTotal > 0
    AND o.TransactionState = 'CAPTURED'
    AND DATEPART(yyyy,o.OrderDate) = @Year

Happy SQL Coding!