Orders with Date of Order, Coupon Code, Sub-Total, Tax, Shipping, and Grand Total - Page 2 - Ecommerce Forums
Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22

Thread: Orders with Date of Order, Coupon Code, Sub-Total, Tax, Shipping, and Grand Total

  1. #16
    jmartya (anyone?)

    I was looking for some sql code to extract my total taxable/non-taxable sales and tax collected. I tweaked the code above and on most orders it works work well - any orders though that had a refund do not reflect it or the total_payment_received is really pulling from the total payment authorized and not final? Is there another field for final payments or adjusted payments? Any order adjustment fields? I looked but I did not see anything that looks close.

    This is my first time trying the sql features out so it is like a foreign language to me. It was by pure trial and error that I got this far. I was just hoping to find a quicker way than QB to calculate my taxes and other report items. It just seems so redundant to export information that is in Volusion to another program that is equally as frustrating. I much prefer exporting to a spreadsheet where everything is transparent.


    Code:
    SELECT  Orders.OrderDate as Order_Date,  Orders.OrderID as Order_ID, SUM(Total_Payment_Received-SalesTax1) as Sales, SUM(SalesTax1) as Tax,  SUM(TotalShippingCost) as Shipping, Orders.Total_Payment_Received as Grand_Total     
    
    
    FROM Orders    
    
    
    WHERE  Orders.OrderStatus <> 'Cancelled' AND  Orders.OrderStatus <> 'Returned' AND  DATEDIFF(DAY, Orders.OrderDate, '1/1/2013') <= 0 AND DATEDIFF(DAY, Orders.OrderDate, '6/30/2013') >= 0                      
    
    
    GROUP BY Orders.OrderDate, Orders.OrderID, Orders.Total_Payment_Received       
    
    
    ORDER BY Orders.OrderDate, Orders.OrderID
    Brenda
    The Gentle Bath & Company

  2. #17
    Actually that may be working ok - I just checked the order in question and it was an order I had refunded using the "official RMA process" That was before I learned how to avoid that altogether and do my returns using unship and credit adjustments. I checked on some orders futher in the year and they are reflecting the refunds that I did outside of the RMA process!

    THANKS SO MUCH jmartyka! That code was a huge help in the right direction! I sure hope you are still here and consider posting your ebook - I would pay for downloads of that in a heartbeat!
    Brenda
    The Gentle Bath & Company

  3. #18
    Quote Originally Posted by jmartyka View Post
    I've thought about putting together an ebook of a bunch of useful Volusion SQL.
    It would seem that someone might want to make money on this - just post a "product" for each script and then provide examples of the output of the script in the description and then just make it a downloadable item. Maybe you throw in some excel spreadsheets that show how to do some "cool" things with the resulting export/report. While I'm thankful for the great scripts that the smart folks here have posted, I also don't have any problem paying for something that takes skill to do.

  4. #19
    Count me in. I would certainly pay for a variety of scripts and exports.

  5. #20
    OK - I know this is a super old thread, but I just tried to run the code today and got an error "Your custom query sql statement is not filled in.".

    Can anyone else confirm they are unable to generate this report now? Worked for me a few months ago. Thanks

  6. #21

    Simple -Date Range- Volusion SQL query

    Quote Originally Posted by marc.vasquez View Post
    OK - I know this is a super old thread, but I just tried to run the code today and got an error "Your custom query sql statement is not filled in.".

    Can anyone else confirm they are unable to generate this report now? Worked for me a few months ago. Thanks
    I use this for pulling reports on a monthly, quarterly, yearly basis... Alter the date range to what you are looking to pull. This pulls together the Order and Shipping info into one .csv export. I then pull this into EXCEL, change it to .XLSX and use the Conditional Formatting to highlight cells on the 'received' payment so that if there are any discrepancies they can be addressed.

    SELECT Orders.OrderID, Orders.OrderDate, Orders.OrderStatus, Orders.Total_Payment_Received as PmtRec, Orders.Total_Payment_Authorized as PmtAuth, Orders.CashTender, SUM(TrackingNumbers.Shipment_Cost) as ADVship, Orders.TotalShippingCost as CustShip, Orders.SalesTax1 as Tax, Orders.ShipAddress1, Orders.ShipCity, Orders.ShipState, Orders.ShipPostalCode
    FROM Orders LEFT JOIN TrackingNumbers ON Orders.OrderID=TrackingNumbers.OrderID
    WHERE Orders.OrderDate >= '4/1/2017' AND Orders.OrderDate <= '5/1/2017'
    GROUP BY Orders.OrderID, Orders.OrderDate, Orders.OrderStatus, Orders.Total_Payment_Received, Orders.Total_Payment_Authorized, Orders.CashTender, Orders.TotalShippingCost, Orders.SalesTax1, Orders.ShipAddress1, Orders.ShipCity, Orders.ShipState, Orders.ShipPostalCode
    ORDER BY Orders.OrderID DESC;

  7. #22
    Code:
    SELECT Orders.OrderID, Orders.OrderDate, Orders.OrderStatus, Orders.Total_Payment_Received as PmtRec, Orders.Total_Payment_Authorized as PmtAuth, Orders.CashTender, SUM(TrackingNumbers.Shipment_Cost) as ADVship, Orders.TotalShippingCost as CustShip, Orders.SalesTax1 as Tax, Orders.ShipAddress1, Orders.ShipCity, Orders.ShipState, Orders.ShipPostalCode
    FROM Orders LEFT JOIN TrackingNumbers ON Orders.OrderID=TrackingNumbers.OrderID
    WHERE Orders.OrderDate >= '4/1/2017' AND Orders.OrderDate <= '5/1/2017'
    GROUP BY Orders.OrderID, Orders.OrderDate, Orders.OrderStatus, Orders.Total_Payment_Received, Orders.Total_Payment_Authorized, Orders.CashTender, Orders.TotalShippingCost, Orders.SalesTax1, Orders.ShipAddress1, Orders.ShipCity, Orders.ShipState, Orders.ShipPostalCode
    ORDER BY Orders.OrderID DESC;

Similar Threads

  1. Total Spent per Customer
    By fitzwilly in forum Reporting
    Replies: 5
    Last Post: 04-29-2014, 01:36 PM
  2. Enter site with coupon code assigned?
    By Dan2357 in forum Tips & Tricks
    Replies: 12
    Last Post: 10-25-2012, 01:51 PM
  3. Replies: 10
    Last Post: 10-09-2012, 03:30 PM
  4. Free Shipping Code Or No Code?
    By AWCthreads in forum Conversion Rate Optimization
    Replies: 5
    Last Post: 09-13-2012, 03:28 PM
  5. Prevent Orders With No Shipping Orders Chosen
    By AWCthreads in forum Tips & Tricks
    Replies: 2
    Last Post: 06-24-2012, 12:09 PM

Tags for this Thread

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
  •