Models - Models without a category
SELECT
Models.ModelID, Models.Active
FROM
Models
WHERE
Models.CategoryID NOT IN (SELECT CategoryID FROM Categories)
Best Selling Models - Configurable date range
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '01/01/2015 00:00:00'
SET @endDate = '01/03/2016 00:00:00'
SELECT
BRANDS.BRAND,
CATEGORIES.[NAME],
MODELS.MODEL,
MODELS.ACTIVE,
CAST(SUM(UNITPRICE*QUANTITY) AS DECIMAL(18,2)) AS [Total Price]
FROM ORDERITEMS
INNER JOIN STOCK ON STOCK.STOCKID=ORDERITEMS.STOCKID
INNER JOIN ORDERS ON ORDERS.ORDERID=ORDERITEMS.ORDERID
INNER JOIN MODELS ON MODELS.MODELID=STOCK.MODELID
INNER JOIN BRANDS ON BRANDS.BRANDID=MODELS.BRANDID
INNER JOIN CATEGORIES ON CATEGORIES.CATEGORYID=MODELS.CATEGORYID
WHERE orders.timestamp BETWEEN @startDate AND @endDate
AND ORDERS.ORDERTYPE=1
GROUP BY
MODELS.MODEL,
BRANDS.BRAND,
CATEGORIES.[NAME],
MODELS.ACTIVE
ORDER BY
[Total Price] DESC
Models - Activate model where - Inactive and with positive Stock
UPDATE
Stock
SET
Stock.Active = 1
WHERE
Stock.Active = 0
AND
(
Stock.StockHeldExternally = 1
OR Stock.InStock > 0
)
-- Update Model to Active where Model is
-- InActive and it has Active Stock Items
UPDATE
Models
SET
Models.Active = 1
WHERE
Models.Active = 0
AND Models.ModelID IN (
SELECT DISTINCT
ModelID
FROM
Stock
WHERE
Active = 1
)