query

gistfile1.txt

/****** Object:  StoredProcedure [dbo].[sp_GetRecommendedProducts]    Script Date: 02/27/2013 11:40:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetRecommendedProducts]
  -- Add the parameters for the stored procedure here
  @productID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @apparelCount int

	DECLARE @tempRec TABLE 
	( 
		ProductID INT
	)

	DECLARE @productGroupID int;
	SELECT @productGroupID=ProductGroupID FROM Products p WHERE p.ProductID=@productID;
	
	IF @productGroupID IS NULL 
	BEGIN
		PRINT 'It is null'
	END
	
	INSERT INTO @tempRec 
		SELECT ISNULL(p.ProductGroupID, p.ProductID) FROM Orders o 
			INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
			INNER JOIN Products p ON od.ProductID=p.ProductID
			WHERE EXISTS (
				SELECT * FROM OrderDetails od2 
					INNER JOIN Products p2 ON od2.productid=p2.productid
					WHERE od2.OrderID = o.OrderID 
					AND (od2.ProductID=@productID OR p2.ProductGroupID=@productID))
			AND od.ProductID IS NOT NULL 
			AND od.ProductID <> 0 
			-- Don't recommend products in the same group.
			AND (p.ProductGroupID IS NULL OR @productGroupID IS NULL
					OR (p.ProductGroupID IS NOT NULL AND p.ProductGroupID <> @productGroupID))
			-- Don't recommend required products
			AND NOT EXISTS(SELECT * FROM RelatedProducts rp 
								WHERE rp.ProductID=@productID
								AND rp.RelatedProductID=ISNULL(p.ProductGroupID, p.ProductID))
			-- Don't recommend self
			AND ISNULL(p.ProductGroupID, p.ProductID)<>@productID;
			
	-- Is this product an apparel item?
	SELECT @apparelCount = COUNT(*) FROM Products_Categories pc 
					INNER JOIN Categories c ON pc.CategoryID=c.CategoryID
					LEFT JOIN Categories cp ON cp.CategoryID=c.Parent
					WHERE pc.ProductID=@productID AND (pc.CategoryID=1 OR c.Parent=1 OR cp.Parent=1);
		
	IF @apparelCount > 0
	BEGIN
		PRINT 'This is an apparel product.'
		-- Delete any products that aren't in the apparel category from the temporary table.
		DELETE t FROM @tempRec AS t WHERE NOT EXISTS (SELECT * FROM Products_Categories pc 
					INNER JOIN Categories c ON pc.CategoryID=c.CategoryID
					LEFT JOIN Categories cp ON cp.CategoryID=c.Parent
					WHERE t.ProductID=pc.ProductID AND (pc.CategoryID=1 OR c.Parent=1 OR cp.Parent=1));
	END
			
	SELECT 
		ProductID,
		COUNT(*) AS NumPurchases
		FROM @tempRec
		GROUP BY ProductID
		ORDER BY COUNT(*) DESC;

END