SQL Query for Bestsellers Report

Question

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,
        OrderDate,
        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')

 

Answer 1

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)
VALUES ('Get Tables', 'Get names of  tables in store DB', 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE''')

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', 
'SET DATEFIRST 1 
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.