VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-Cart StoreFront Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 VP-ASP 7.0 Questions
 Query and Export
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
46 Posts

Posted - May 10 2011 :  04:24:01  Show Profile  Visit Renners's Homepage  Reply with Quote

I'm trying to run a query to report sales of products by a specific supplier over a specfic period using the following sql code

SELECT products.ccode, products.cname, products.cstock, COUNT(oitems.numitems) AS [Sold in Period], SUM(oitems.unitprice) AS [Total SV] FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid INNER JOIN suppliers ON oitems.supplierid = suppliers.supplierid INNER JOIN products ON oitems.catalogid = products.catalogid WHERE (orders.odate > CONVERT(DATETIME, '2010-02-01', 102)) AND (orders.odate < CONVERT(DATETIME, '2011-05-01', 102)) AND (orders.customercancel = 0 OR orders.customercancel IS NULL) AND (orders.canceled = 0) AND (orders.ocardtype <> N'Fail') AND (suppliers.supplierid = 22) GROUP BY products.cstock, products.ccode, products.cname ORDER BY [Total SV] DESC

This runs fine from within MS SQL server studio but will dump me out of the Admin section entirely with an 'An error occured please restart shopping' if I run it via the Query and Export Manager.

Does anyone know if the query and export is a single table query tool?


VP-ASP Expert

738 Posts

Posted - May 14 2011 :  05:22:47  Show Profile  Reply with Quote
seems like you cannot use the builtin sql function such as SUM(oitems.unitprice) , COUNT(oitems.numitems), CONVERT(DATETIME, '2011-05-01', 102)) etc...

Go to Top of Page


4287 Posts

Posted - July 12 2011 :  08:17:41  Show Profile  Visit support's Homepage  Reply with Quote
Hi Renners,

Not sure if this is what you are looking for but have a look at the following helpnote:


Thank you.

Cam Flanigan
VP-ASP Cart Support

Follow us on Twitter:
Go to Top of Page

Starting Member

United Kingdom
46 Posts

Posted - August 03 2011 :  10:23:17  Show Profile  Visit Renners's Homepage  Reply with Quote
Hi Cam,

Thanks for the Link, that cured it.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000