Remove Cursors
-
Hi All,
I was recently given this test in a interview to remove the cursors from the first statement and keep the output must be the same. My answers were the update & insert are in the second code. I haven't heard anything so I guess my answers were incorrect so I would like to understand what I missed.
Thanks
--Question 5: Cursors, code optimisation
--Estimate: 10-15 minutes
--Remove cursors for following code, output must be the same
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
------ My Answers
DECLARE @CustomerOrderItems TABLE ( CustomerId int, StockItemID int, Quantity int, PRIMARY KEY ( CustomerId, StockItemID ) )
UPDATE @CustomerOrderItems SET i.Quantity = i.Quantity + c.quantity
FROM @CustomerOrderItems i
JOIN Sales.Customers c
ON i.CustomerId = c.CustomerId AND i.StockItemID = C.StockItemID
INSERT INTO @CustomerOrderItems ( CustomerId, StockItemID, Quantity ) VALUES ( o.CustomerId, l.StockItemID, l.Quantity )
SELECT o.CustomerId, l.StockItemID, l.Quantity
FROM Sales.Orderslines l
JOIN sales.orders o
ON l.orderid = o.order.id
WHERE l.CustomerID NOT EXISTS( SELECT * from Sales.Orders WHERE OrderDate >= '2015-12-01' AND OrderDate < '2016-01-01')- This topic was modified 4 years, 11 months ago by ringovski.
-
To be honest, I'm totally opposed to publishing answers to interview questions that some company obviously feels important about. Such answers would make it pretty easy for other people to "game the system". I strongly recommend that others that may read this thread not provide an answer on this or any other thread. If you want to help the OP understand the code and the way to solve it, please do it offline via PMs just to help out the company that's asking this very reasonable but totally awesome question.
As a bit of a sidebar, I wish I knew the name of the company so that I could tell them that they need to make some obvious improvements in their security. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I am not trying to 'game the system' I want to improve my understanding of cursor's. Any company can ask many different questions and will also change there questions. I made a attempt at answering the questions and now quite reasonably are asking the expert community here what the answers maybe.
-
not saying if code is right or wrong, neither would it matter for the interviewers.
It shows a good attempt at solving the issue even if syntax wise it could be wrong.
As for not getting a reply - this is very common in many companies and it is frustrating - one may have passed all interviews with different degrees of "classification" and not be chosen and not being informed of that decision.
Also take in consideration that the technical aspect is one out of a few aspects that are used to decide on hiring (or calling back for further interviews) so even if you passed this one, you may have scored lower than expected on the others.
baseline is do not take it for granted that you failed just because you didn't hear back from the hiring company.
As for improving your knowledge - setup a home environment, create the tables required to execute both the cursor and your code and try and run both - when you do this you may see if you did anything wrong and why.
-
it looks like this could be a simple merge statement, maybe that's what they were looking for, but I agree with jeff..
MVDBA
-
It's actually much simpler than even that but that's it on any clues on this thread. Please PM the OP if anyone wants to help or discuss further. Help protect the company that's giving this test (and I'll say it again... this is a freakin' awesome question!)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions - ringovski wrote:
I am not trying to 'game the system' I want to improve my understanding of cursor's. Any company can ask many different questions and will also change there questions. I made a attempt at answering the questions and now quite reasonably are asking the expert community here what the answers maybe.
Oh no... I wasn't talking about you gaming the system. You appear to have put forth a good effort. I'm concerned about others gaming the system. Google is pretty good about picking up on this type of thing and it would be a simple task for someone to simply search for a key line or two of code and come up with an answer if an answer were posted.
It's pretty obvious that you had some sort of access to the internet or you wouldn't have been able to make a copy of the question so accurately. Others would have the same access during the test.
Again, not finger pointing at you. I'm just concerned that someone else could use this otherwise very honest post to game the system.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
p.s. And because you've put forward a good effort, IMHO, PM me and I'll show you how to think about this to solve it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions - Jeff Moden wrote:
p.s. And because you've put forward a good effort, IMHO, PM me and I'll show you how to think about this to solve it.
jeff - send me the info too - i'm just curious how you approached it
MVDBA
-
Without wanting to give away the answer, the script creates an empty table variable that it populates to generate a select. So I would look at rewriting the entire script with just one select statement without either the table variable or the cursors.
- Jonathan AC Roberts wrote:
Without wanting to give away the answer, the script creates an empty table variable that it populates to generate a select. So I would look at rewriting the entire script with just one select statement without either the table variable or the cursors.
oh dear lord... why did I not see that
I was so focused on the inside of the cursor and trying to remove it.....
MVDBA
- MVDBA (Mike Vessey) wrote:Jonathan AC Roberts wrote:
Without wanting to give away the answer, the script creates an empty table variable that it populates to generate a select. So I would look at rewriting the entire script with just one select statement without either the table variable or the cursors.
oh dear lord... why did I not see that
I was so focused on the inside of the cursor and trying to remove it.....
Heh... to quote a very old doughnut shop...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply