Sales report per SKU

[Skriver]

Hi

Anybody know of a way to extract a report of the total sales of each SKU for a required date range?

Example - A total sales report for a year of all SKU's that have sold?

[webopius]

Very rough (and you might want to check that I've used the correct product price field) but something like this to get you started...

select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales 
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
where o.OrderDate between '01-Jan-2011' and '01-Jan-2012'
group by os.OrderedProductSKU 
order by 2 desc

[Skriver]

Just needed the

USE [dbname]
GO

before. Seems to work like a charm. Is it also possible to include the Quantity & Ordered ProductName from within the Orders_ShoppingCart table so there is the SKU, Total amount, Product name and Quantity?

[webopius]

Yes, you can add Qty and Product name but you need a slight SQL trick to convert the name field to nvarchar because you can't group by ntext columns:

select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales,
cast(os.OrderedProductName as nvarchar(255)) Name, SUM(os.Quantity) Qty
 from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
where o.OrderDate between '01-Jan-2011' and '01-Jan-2012'
group by os.OrderedProductSKU, cast(os.OrderedProductName as nvarchar(255))
order by 2 desc

What would be really nice is to have this type of report available in the admin pages.... but I can't do that 'pro bono' I'm afraid. Someone else here might be able to though.

Adam

[esedirect]

You can always add it to the custom report area

[Skriver]

That's great as it is thanks!

Should be a standard report I think

Great work!