Help to complete a cursor

Question

Hi, I am working on cursors. I want to create a cursor to display each customer’s information and the product that he has purchased more. For this aim, I wrote the following cursor:

declare myCursor cursor
 for select Customers.CustomerID, Customers.ContactName, Products.ProductName, SUM(Quantity) as Total
  from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID
 inner join( [Order Details] inner join Products on [Order Details].ProductID = Products.ProductID)
 on Orders.OrderID = [Order Details].OrderID
 group by Customers.CustomerID, Customers.ContactName, Products.ProductName
 --
 declare @CustomerID nchar(10), @ContactName nvarchar(30), @ProductName nvarchar(4), @Total int
 open myCursor
 fetch next from myCursor into @CustomerID, @ContactName , @ProductName, @Total
 while @@FETCH_STATUS = 0
     begin

     begin
          print @CustomerID+' '+@ContactName+' '+@ProductName+' '+CAST(@Total as varchar)
          fetch next from myCursor into @CustomerID, @ContactName , @ProductName, @Total
     end
     end

Now it displays that how many times each customers has purchased each product. Now I want to use condition keywords like IN, CONTAIN or EXISTS to complete my cursor to display the product that each customer has purchased more. But I have no idea to do it. Would you please help me?

Answer 1

If I understand correctly, you are looking to show the product that each customer has purchased most. Using your cursor, you could do this by first declaring a table variable such as

declare @customersprocessed table
(
  @CustomerID
)

Then, you would need to order your cursor by SUM(Quantity) DESC so that the top items would rise to the top.

Finally, you would need to modify they inner part of the query to only print if the customer id doesn't already exist in the table, then add the customerid to the table so it wouldn't be printed again.

 begin
      if ((select count(1) from @customersprocessed where CustomerID = @CustomerID) = 0)
      begin
         print @CustomerID+' '+@ContactName+' '+@ProductName+' '+CAST(@Total as varchar)
      end
      else
      begin
         insert into @customersprocessed select @CustomerID
      end

      fetch next from myCursor into @CustomerID, @ContactName , @ProductName, @Total
 end

This of course is assuming you want to keep the cursor. There would be other ways of doing this using a select of all customers you wanted to show and a subquery which returned the most purchased product.