Top Sellers - Categories - Last 3 Months
SELECT
CATEGORIES.[NAME],
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 ORDERITEMS.[TIMESTAMP] > GETDATE()-90
AND ORDERS.ORDERTYPE=1
GROUP BY
CATEGORIES.[NAME]
ORDER BY
[Total Price] DESC
Top Sellers - Categories - Last 6 Months
SELECT
CATEGORIES.[NAME],
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 ORDERITEMS.[TIMESTAMP] > GETDATE()-180
AND ORDERS.ORDERTYPE=1
GROUP BY
CATEGORIES.[NAME]
ORDER BY
[Total Price] DESC
Top Sellers - Models - Last 3 Months
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 ORDERITEMS.[TIMESTAMP] > GETDATE()-90
AND ORDERS.ORDERTYPE=1
GROUP BY
MODELS.MODEL,
BRANDS.BRAND,
CATEGORIES.[NAME],
MODELS.ACTIVE
ORDER BY
[Total Price] DESC
Top Sellers - Models - Last 6 Months
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 ORDERITEMS.[TIMESTAMP] > GETDATE()-180
AND ORDERS.ORDERTYPE=1
GROUP BY
MODELS.MODEL,
BRANDS.BRAND,
CATEGORIES.[NAME],
MODELS.ACTIVE
ORDER BY
[Total Price] DESC
Top Sellers - Brands - Last 3 Months
SELECT
BRANDS.BRAND,
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 ORDERITEMS.[TIMESTAMP] > GETDATE()-90
AND ORDERS.ORDERTYPE=1
GROUP BY
BRANDS.BRAND
ORDER BY
[Total Price] DESC