Hi guys, this query works (I think) but it's written in my usual clunky style, can anybody see a more elegant solution. It produces results that show the most used product and supplier on current live contracts using SQL Server 2008:
SELECT a.OrderID, a.OrderDate, a.ContractID, b.ProductID, c.ProductName, c.SupplierID, d.SupplierName
INTO #contracts
FROM [Orders] a
INNER JOIN [Order Details] b
ON a.OrderID = b.OrderID
INNER JOIN [Products] c
ON b.ProductID = c.ProductID
INNER JOIN [Suppliers] d
ON c.SupplierID = d.SuppliersID
WHERE DATEPART(year, a.OrderDate) >= '2016'
SELECT a.ContractID, a.SupplierID, a.ProductID, a.ProductName,
(SELECT COUNT() FROM #contracts WHERE SupplierID = a.SupplierID AND ContractID = a.ContractID) AS 'MostUsedSupplier',
(SELECT COUNT() FROM #contracts WHERE ProductID = a.ProductID AND ContractID = a.ContractID) AS 'MostUsedProduct'
INTO #orders
FROM #contracts a
GROUP BY ContractID, SupplierID, ProductID, ProductName
ORDER BY MostUsedSupplier
SELECT ROW_NUMBER()
OVER (ORDER BY a.ContractID) AS RowNum,
a.ContractID, a.MostUsedSupplier , a.SupplierID, a.MostUsedProduct, a.ProductID, a.ProductName, b.name_, c.SupplierName
INTO #results
FROM #orders a
left JOIN demaa.dbo.jcacc b
ON a.ContractID = b.code_
left JOIN Suppliers c
ON a.SupplierID = c.SuppliersID
WHERE b.co_ = '01'
ORDER BY a.ContractID
SELECT a.*
INTO #results1
FROM #results a
JOIN
(
SELECT ContractID, MAX(MostUsedSupplier) maxSupplier, MAX(MostUsedProduct) maxProduct
FROM #results
GROUP BY ContractID
) b ON a.ContractID = b.ContractID AND
a.MostUsedSupplier = b.maxSupplier AND
a.MostUsedProduct = b.maxProduct
ORDER BY a.ContractID
SELECT
MAX(RowNum)AS 'RowNum',
ContractID AS 'ContractID',
name_ AS 'Contract',
MAX(ProductName) AS 'MostUsedProduct',
MAX(SupplierName) AS 'MostUsedSupplier'
FROM #results1
GROUP BY ContractID,name_
ORDER BY ContractID
DROP TABLE #contracts
DROP TABLE #orders
DROP TABLE #results
DROP TABLE #results1
Sample Output:
RowNum ContractID Contract MostUsedProduct MostUsedSupplier
133 100638 Sample Contract DULUX TR MATT DULUX -
190 100607 Sample Contract JOHNSTONES COVA PLUS KALON