Deactivate Stock Options with No Stock AND Deactivate Models with No Active Stock Options
UPDATE Stock
SET Active = 0
WHERE StockID IN(
SELECT StockID
FROM Stock
WHERE Active = 1 AND InStock <= 0 AND NOT(ModelID IN(
SELECT ModelID
FROM Models
WHERE BrandID IN(11,12,43,8,39,54))))
UPDATE Models
SET Active = 0
WHERE Active = 1 AND ModelID IN(
SELECT ModelID
FROM Models
WHERE Active = 1 AND ModelID IN(
SELECT DISTINCT s0.ModelID
FROM Stock s0
LEFT JOIN Stock s1
ON s0.ModelID = s1.ModelID
AND s1.Active = 1
WHERE s0.Active = 0 AND s1.ModelID IS NULL))
Models - Deactivate Models With No Image
UPDATE MODELS SET ACTIVE=0 WHERE Image=0 AND Active=1
Stock Levels by Location
SELECT
Stock.StockID,
StockLocationLevels.StockLocationID,
Models.Model,
Stock.[Option],
Stock.InStock AS AllLocationsStock,
StockLocationLevels.StockLevel AS Instock
FROM
Stock
INNER JOIN
Models
ON Models.ModelID = stock.ModelID
INNER JOIN
StockLocationLevels
ON StockLocationLevels.StockID = stock.StockID
WHERE
Models.Active = 1
AND
Stock.Active = 1
AND
StockLocationLevels.StockLocationID = 1 --change 1 Artigarvan to 2 for Galgorm 3 External Stock
Stock - Active with none in stock
SELECT
Models.ModelID,
Stock.StockID,
Models.Model,
Stock.[Option],
Stock.Instock
FROM
Models
INNER JOIN
Stock
ON Models.ModelID = Stock.ModelID
WHERE
Models.Active = 1
AND
Stock.Active = 1
AND
Stock.Instock <= 0
Stock - Inactive with 1 or more in stock
SELECT
Models.ModelID,
Stock.StockID,
Models.Active AS ModelsActive,
Stock.Active AS StockActive,
Models.Model,
Stock.[Option],
Stock.Instock
FROM
Models
INNER JOIN
Stock
ON Models.ModelID = Stock.ModelID
WHERE
Stock.Active = 0
AND
Stock.Instock >= 1
Stock - Items currently Active and on Sale
SELECT Distinct ModelID, Offer, ExcludeFromPromotions FROM Models
WHERE Offer = 1
AND Active = 1
Stock - Update web price following the min price
UPDATE Models SET Models.MinPrice=Stock.MinPrice
FROM Models INNER JOIN (SELECT ModelID, MIN(Price) AS MinPrice FROM Stock WHERE Active=1 GROUP BY ModelID)
AS Stock ON Models.ModelID=Stock.ModelID
Low Stock
SELECT
Stock.StockID,
Stock.ModelID,
Brands.Brand,
Models.Model,
Stock.[Option],
Stock.Price,
Stock.InStock AS [Current_Stock],
[~LowStock].LowStock AS [Low_Stock]
FROM
[~LowStock]
INNER JOIN Stock
ON Stock.StockID = [~LowStock].StockID
INNER JOIN Models
ON Models.ModelID = Stock.ModelID
INNER JOIN Brands
ON Brands.BrandID = Models.BrandID
WHERE
Stock.InStock <= [~LowStock].LowStock