Advanced Retail (UK) Ltd
Company Wiki

On 10th of every month Alicia needs the top 10 items by value and quantity, therefore the following scripts need to be run at Pets HO and put into a spreadsheet of the following format:

https://drive.google.com/a/advanced-retail.co.uk/file/d/0B34E6frbPyJNa09sT1pyc2JlUGc/view?usp=sharing

Change ? to the current the month and year e.g '%April%2017%'

i.e. January, February, March, April, May, June, July, August, September, October, November, December

For Value

USE priarch
GO
-- Top 10 VALUE
;WITH stockt AS
(
SELECT W.description AS warehouse, S.description, W.whse_no, S.guid FROM [dbo].[stocktake_schedule_hdr] S
INNER JOIN warehouse W ON W.whse_no = S.whse_no
WHERE is_virtual = 0
)
SELECT TOP 10 T.warehouse, V.description, casT(round(D.count_quantity*v.cost, 2) AS DEC(9,2)) as Value, CONVERT(varchar, S.datetime_snapshot, 103) as 'Date' FROM stockt T 
INNER JOIN [stocktake_products_hdr] S ON S.description = T.description AND S.whse_no = T.whse_no
INNER JOIN [stocktake_products_det] D ON D.guid_hdr = S.guid
INNER JOIN vw_ProductPrice V ON V.product = D.product 

WHERE
	S.description LIKE '%value%' AND
	S.description LIKE '%?%201?%' AND 
	product_group <> 'CONSUME' AND
	V.list_no = 1 AND
  	V.description <> '5p Carrier Bag Charge'
ORDER BY 3 DESC

For Quantity

-- Top 10 QUANTITY
;WITH stockt AS
(
SELECT W.description AS warehouse, S.description, W.whse_no, S.guid FROM [dbo].[stocktake_schedule_hdr] S
INNER JOIN warehouse W ON W.whse_no = S.whse_no
WHERE is_virtual = 0
)
SELECT TOP 10 T.warehouse, V.description, CAST(round(D.count_quantity, 2) AS INT) as 'Quantity', CONVERT(varchar, S.datetime_snapshot, 103) as 'Date' FROM stockt T 
INNER JOIN [stocktake_products_hdr] S ON S.description = T.description AND S.whse_no = T.whse_no
INNER JOIN [stocktake_products_det] D ON D.guid_hdr = S.guid
INNER JOIN vw_ProductPrice V ON V.product = D.product 

WHERE
	S.description LIKE '%quan%' AND
	S.description LIKE '%?%201?%' AND 
	product_group <> 'CONSUME' AND
	V.list_no = 1 AND
	V.description <> '5p Carrier Bag Charge'
ORDER BY 3 DESC

Make sure the data looks reasonable and is for this month then copy the output into the template spreadsheet, save it locally and send to Alicia