Looking for old forum posts and information? View the old forum archive here ยป
Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

A static copy of our previous forums website is available online for reference. Click here to access the forum archive.

Learn more about...


SQL Query for Bestsellers Report

I'm trying to create a custom report to get the top selling products and/or categories for that week. I know there's already a built-in bestsellers report, but I only want to look at specific categories, not the entire site, and I want more than the top 10.

Is there some way to see the query that's used to create the bestsellers report? I think I can figure out most of this, but some aspects, like trying to limit the range to within a week, are stumping me. It'd be a lot easier if I could use the bestsellers report was made as reference and go from there.

On a similar note, is there a page that has the names of the databases, tables, and columns typically used for sites? I'm currently guessing the columns names (OrderedProductSKU, CategoryID, etc) based on the xml packages.


Edit: After messing around, I managed to build a report that doesn't give me error messages, but it doesn't bring up any products either. So, I'm not sure if I pulling from the wrong table or something. Here's the query I'm using (not worried about categories right now, I just want it to bring up products first):

INSERT INTO CustomReport (Name, Description, SQLCommand)
VALUES ('GM Bestsellers Test', 'Top 20 selling products for Gear and Gifts', 'SET DATEFIRST 1
        SELECT TOP 20 ProductID,
        OrderedProductName AS Name,
        OrderedProductSKU AS WebID,
        SUM(Quantity) AS Sales,
        SUM(OrderedProductPrice) AS Dollars   
        FROM dbo.Orders_ShoppingCart
        WHERE OrderDate >= DATEADD(day, 1-datepart(dw, GETDATE()), CONVERT(date,GETDATE()))
        AND OrderDate < DATEADD(day, 8-datepart(dw, GETDATE()), CONVERT(date,GETDATE()))
        GROUP BY ProductID, OrderedProductName, OrderedProductSKU, OrderDate
        ORDER BY Sales DESC')


asked Oct 18, 2017 in MultiStore by gesturepoke (210 points)
edited Oct 19, 2017 by gesturepoke

1 Answer

0 votes
Best answer

I figured this out on my own after no one replied. So, for anyone else is trying to build a similar custom report, here's what I did:

First off, I ended up building some custom reports to see the names of tables on the site and their column names. I don't have access to a SQL environment on my work computer so I can't just type "sp_help table_name" in a terminal or something.

This is the report I used to get the tables in our site's database:

INSERT INTO CustomReport (Name, Description, SQLCommand)

And this is the report I used to find column names in individual tables, replacing "TABLENAME" with the name of whatever table I was trying to select data from:

INSERT INTO CustomReport (Name, Description, SQLCommand)
VALUES ('Get TABLENAME Columns', 'Get names of columns in TABLENAME table', 
'SELECT column_name
FROM information_schema.columns
WHERE table_name = ''NAMEOFTABLE''')

And this is the report I used to get the sales for the week with corresponding categories, using the information obtained from previous reports:

INSERT INTO CustomReport (Name, Description, SQLCommand)
VALUES ('GM Bestsellers', 'Top 20 weekly GM sales and their categories', 
SELECT TOP 20 OrderedProductSKU AS WebID,
Orders_ShoppingCart.CreatedOn AS PurchaseDate,
Category.Name AS Category,
OrderedProductName AS ProductName, 
SUM(Quantity) AS Sales,
SUM(OrderedProductPrice) AS Profit
FROM Orders_ShoppingCart
INNER JOIN ProductCategory ON Orders_ShoppingCart.ProductID = ProductCategory.ProductID
INNER JOIN Category ON ProductCategory.CategoryID = Category.CategoryID
WHERE Orders_ShoppingCart.CreatedOn >= DATEADD(day, 1-datepart(dw, GETDATE()), CONVERT(date,GETDATE()))
AND Orders_ShoppingCart.CreatedOn < DATEADD(day, 8-datepart(dw, GETDATE()), CONVERT(date,GETDATE()))
AND ProductCategory.CategoryID NOT IN (203, 235, 256, 257, 277, 369, 707, 708)
GROUP BY Quantity, OrderedProductPrice, Orders_ShoppingCart.ProductID, OrderedProductName, Category.Name, OrderedProductSKU, Orders_ShoppingCart.CreatedOn
ORDER BY Profit DESC, Sales DESC, Category.Name')

I grabbed the relevant order information (product, SKU, name, price, quantity, order date) from the Orders_ShoppingCart table, the product's category from ProductCategory, and that category's name from the Category table.

Is this a very roundabout method? Maybe. Is there an easier way? I don't know, because, again, this question never got a response. Hopefully this will help out somebody else looking for answers, as cumbersome as it may or may not be.

answered Oct 25, 2017 by gesturepoke (210 points)