April 15, 2018 at 4:20 am
Hi all. need some help to remove CURSOR from the following code to get the same result. Will highly appreciate!
DECLARE @CustomerOrderItems TABLE ( CustomerId int, StockItemID int, Quantity int, PRIMARY KEY ( CustomerId, StockItemID ) )
DECLARE CUR_CUS CURSOR FOR SELECT CustomerID FROM Sales.Customers
DECLARE @CustomerId int, @OrderId int, @StockItemID int, @Quantity int
OPEN CUR_CUS
FETCH NEXT FROM CUR_CUS INTO @CustomerId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CUR_CUS_O CURSOR FOR SELECT OrderId FROM Sales.Orders WHERE CustomerID = @CustomerId AND OrderDate >= '2015-12-01' AND OrderDate < '2016-01-01' OPEN CUR_CUS_O FETCH NEXT FROM CUR_CUS_O INTO @OrderId WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CUR_CUS_OL CURSOR FOR SELECT StockItemID, Quantity FROM Sales.OrderLines WHERE OrderID = @OrderId OPEN CUR_CUS_OL FETCH NEXT FROM CUR_CUS_OL INTO @StockItemID, @Quantity WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS ( SELECT * FROM @CustomerOrderItems WHERE CustomerId = @CustomerId AND StockItemID = @StockItemID ) UPDATE @CustomerOrderItems SET Quantity = Quantity + @Quantity WHERE CustomerId = @CustomerId AND StockItemID = @StockItemID ELSE INSERT INTO @CustomerOrderItems ( CustomerId, StockItemID, Quantity ) VALUES ( @CustomerId, @StockItemID, @Quantity ) FETCH NEXT FROM CUR_CUS_OL INTO @StockItemID, @Quantity END CLOSE CUR_CUS_OL DEALLOCATE CUR_CUS_OL FETCH NEXT FROM CUR_CUS_O INTO @OrderId END CLOSE CUR_CUS_O DEALLOCATE CUR_CUS_O FETCH NEXT FROM CUR_CUS INTO @CustomerId END CLOSE CUR_CUS DEALLOCATE CUR_CUS SELECT TOP ( 50 ) WITH TIES C.CustomerID, C.CustomerName, COI.StockItemID, COI.Quantity FROM @CustomerOrderItems COI INNER JOIN Sales.Customers C ON C.CustomerID = COI.CustomerId ORDER BY COI.Quantity DESC, C.CustomerName
April 15, 2018 at 4:56 am
sultankahut - Sunday, April 15, 2018 4:20 AMHi all. need some help to remove CURSOR from the following code to get the same result. Will highly appreciate! DECLARE @CustomerOrderItems TABLE ( CustomerId int, StockItemID int, Quantity int, PRIMARY KEY ( CustomerId, StockItemID ) ) DECLARE CUR_CUS CURSOR FOR SELECT CustomerID FROM Sales.Customers DECLARE @CustomerId int, @OrderId int, @StockItemID int, @Quantity int OPEN CUR_CUSFETCH NEXT FROM CUR_CUS INTO @CustomerIdWHILE @@FETCH_STATUS = 0BEGIN DECLARE CUR_CUS_O CURSOR FOR SELECT OrderId FROM Sales.Orders WHERE CustomerID = @CustomerId AND OrderDate >= '2015-12-01' AND OrderDate < '2016-01-01' OPEN CUR_CUS_O FETCH NEXT FROM CUR_CUS_O INTO @OrderId WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CUR_CUS_OL CURSOR FOR SELECT StockItemID, Quantity FROM Sales.OrderLines WHERE OrderID = @OrderId OPEN CUR_CUS_OL FETCH NEXT FROM CUR_CUS_OL INTO @StockItemID, @Quantity WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS ( SELECT * FROM @CustomerOrderItems WHERE CustomerId = @CustomerId AND StockItemID = @StockItemID ) UPDATE @CustomerOrderItems SET Quantity = Quantity + @Quantity WHERE CustomerId = @CustomerId AND StockItemID = @StockItemID ELSE INSERT INTO @CustomerOrderItems ( CustomerId, StockItemID, Quantity ) VALUES ( @CustomerId, @StockItemID, @Quantity ) FETCH NEXT FROM CUR_CUS_OL INTO @StockItemID, @Quantity END CLOSE CUR_CUS_OL DEALLOCATE CUR_CUS_OL FETCH NEXT FROM CUR_CUS_O INTO @OrderId END CLOSE CUR_CUS_O DEALLOCATE CUR_CUS_O FETCH NEXT FROM CUR_CUS INTO @CustomerIdENDCLOSE CUR_CUS DEALLOCATE CUR_CUS SELECT TOP ( 50 ) WITH TIES C.CustomerID, C.CustomerName, COI.StockItemID, COI.QuantityFROM @CustomerOrderItems COI INNER JOIN Sales.Customers C ON C.CustomerID = COI.CustomerIdORDER BY COI.Quantity DESC, C.CustomerName
Have a look at the following.
Without the create table scripts the relationships/references of the tables and dml statements, its no joy to write code like this.
There may/may not be parsing errors, no guarantees!
SELECT TOP ( 50 ) WITH TIES
C.CustomerID
,C.CustomerName
,COI.StockItemID
,COI.Quantity
FROM (SELECT OL.StockItemID
,OL.Quantity as ol_quantity
,O.OrderId
,C.CustomerId
,SUM(OL.Quantity) OVER(partition by OL.StockItemID,C.CustomerId) as Quantity
,ROW_NUMBER() OVER(partition by OL.StockItemID,C.CustomerId ORDER BY c.Customerid) as rnk
FROM Sales.Orders O
JOIN Sales.Customers C
ON O.customerid=C.CustomerID
JOIN Sales.OrderLines OL
ON OL.OrderID=O.OrderId
WHERE O.OrderDate >= '2015-12-01'
AND O.OrderDate < '2016-01-01'
) COI
JOIN Sales.Customers C
ON C.CustomerID = COI.CustomerId
WHERE COI.rnk=1
ORDER BY COI.Quantity DESC, C.CustomerName
April 16, 2018 at 6:47 am
Did that solve your issue?
April 16, 2018 at 6:58 am
Thanks George for your help. I will test the code tomorrow as I was busy for last two days.
April 17, 2018 at 12:55 am
Thank your very much George, I tested and it's producing the same result with and without cursor
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply