SQL query - Items Sold by Date and Category : Support Center

Discussions Customisation / Scripting Forums Tips and Tricks

SQL query - Items Sold by Date and Category

Will Harrison

started a topic over 3 years ago

I have a SQL script that I have successfully integrated into a 'custom query data' report, I have mapped the date picker and a field for category name all seems to work well.

You might find it useful for pulling data on your own projects?

You just need to map @CATID, @DATEFROM, @DATETO when making a new custom query data report

SELECT  
t.CategoryName, 
CONVERT(VARCHAR(10), 
o.dReceievedDate, 103) AS 'Order Date', 
o.nOrderId  AS 'Order ID', oi.ItemNumber AS 'Item ID', 
'SKU' = CASE WHEN pis.pkStockItemID IS NOT  NULL THEN pis.ItemNumber WHEN si.pkStockItemID IS NOT NULL THEN si.ItemNumber  WHEN oi.ChannelSKU IS NOT NULL THEN oi.ChannelSKU ELSE 'SKU Not Supplied' END,  
'Product Name' = CASE WHEN pis.pkStockItemID IS NOT NULL THEN pis.ItemTitle WHEN  si.pkStockItemID IS NOT NULL THEN si.ItemTitle WHEN sis.cItemName IS NOT NULL  THEN sis.cItemName ELSE 'Product Name Not Supplied' END,  
'Currency' = CASE  o.cCurrency WHEN '£' THEN 'GBP' WHEN '$' THEN 'USD' WHEN '€' THEN 'EUR' ELSE o.cCurrency END, 
oi.fPricePerUnit AS 'Product Price', 
oi.nQty AS  'Quantity', 
o.fPostageCost AS 'Carriage Amount', 
ISNULL(ROUND((oi.fPricePerUnit / 100) *  oi.LineDiscount,2), 0) as 'Discount', 
ISNULL(b.AccountName, '') AS 'Payment Method',  
ps.PostalServiceName AS 'Shipping Method', 
'Order Status' = CASE o.nStatus  WHEN '1' THEN 'Complete' END, 
o.Source + ' ' + o.SubSource AS 'Marketplace'  

FROM  [Order]  o INNER JOIN OrderItem oi on oi.fkOrderID = o.pkOrderID 
inner join StockItems sis  on sis.pkStockID = oi.fkStockID 
left outer join StockItem si on sis.fkStockControlStockItemId =  si.pkStockItemID 
left outer join StockItem pis on pis.pkStockItemID =  oi.fkStockItemId_processed 
INNER JOIN PostalServices ps on o.fkPostalServiceId =  ps.pkPostalServiceId 
INNER JOIN ListCountries lc on o.fkCountryId = lc.pkCountryId
LEFT OUTER JOIN ProductCategories t on pis.CategoryId = t.CategoryId

 left  outer join Order_AdditionalInfo oia on oia.fkOrderId = o.pkOrderID 
 left outer join  Accounting_Bank b on b.pkBankId = o.fkBankId 


WHERE (oi.fkCompositeParentRowId = '00000000-0000-0000-0000-000000000000' or  oi.fkCompositeParentRowId is null)and o.nStatus = 1 and (o.dReceievedDate BETWEEN  @DATEFROM and @DATETO ) AND o.dProcessedOn IS NOT NULL AND  o.bProcessed = 1 AND t.CategoryName = @CATID
ORDER BY o.dReceievedDate, o.nOrderId

Will Harrison

said over 3 years ago

Also added a dropdown with category names populated...

SELECT DISTINCT

c.CategoryName AS  [Text],
c.CategoryName AS  [Value]

FROM
StockItem si WITH (NOLOCK)
INNER JOIN ProductCategories c WITH (NOLOCK) ON c.CategoryId = si.CategoryId

ORDER BY
c.CategoryName