Report based on order subtotal - Ecommerce Forums
Results 1 to 2 of 2

Thread: Report based on order subtotal

  1. #1

    Report based on order subtotal

    Hello

    Would like to run a report on orders where subtotal is less than $20 and get back a list of order#s, customer IDs and order subtotals. Want to track if customers who place small orders eventually place large orders too Any help is always appreciated.

  2. #2
    Cool idea.

    This will get you started...it doesn't do subtotal. It works off the total order value instead. I think subtotal had to be calculated which is more work. I couldn't get arrays to return, so you just get the customer id not each order. But that gives you a list you could work off of at least.

    It is also just looking at orders placed this year so you will get your long term repeat customers in the list.

    Code:
    SELECT     COUNT(Orders.OrderID) as NumOfOrders, SUM(Total_Payment_Received) as TotalOrdered,   MIN(Total_Payment_Received) as MinOrder, MAX(Total_Payment_Received) as MaxOrder, AVG(Total_Payment_Received) as AvgOrder, Customers.CustomerID,     Customers.CompanyName,     Customers.FirstName,     Customers.LastName,     Customers.BillingAddress1,    Customers.BillingAddress2,    Customers.City,    Customers.State,    Customers.PostalCode,    Customers.Country,    Customers.PhoneNumber,    Customers.EmailAddress FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID WHERE Total_Payment_Received > 0 AND Orders.OrderStatus <> 'Cancelled' AND Orders.OrderStatus <> 'Returned' AND Customers.CustomerID <> 3 AND DATEDIFF(YEAR, GETDATE(), Orders.OrderDate) = 0 GROUP BY Customers.CustomerID, Customers.CompanyName, Customers.FirstName, Customers.LastName,    Customers.BillingAddress1,    Customers.BillingAddress2,    Customers.City,    Customers.State,    Customers.PostalCode,    Customers.Country,    Customers.PhoneNumber,    Customers.EmailAddress  HAVING COUNT(Orders.OrderID) > 1  AND MIN(Total_Payment_Received) < 20 ORDER BY MinOrder DESC

Similar Threads

  1. Unable to place order "Tax has been updated based..."
    By toddler4372 in forum Site Performance
    Replies: 1
    Last Post: 08-22-2016, 01:25 PM
  2. Report based on customer name or company name
    By marc.vasquez in forum Reporting
    Replies: 1
    Last Post: 04-04-2015, 03:26 PM
  3. Replies: 2
    Last Post: 10-29-2014, 09:29 PM
  4. Soft Cart Subtotal not considering discount?
    By demitrius in forum Setup & Configuration
    Replies: 16
    Last Post: 02-09-2013, 12:38 PM
  5. Replies: 4
    Last Post: 01-31-2013, 03:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •