Recently I saw a video in YouTube where one of the SQL developers was saying that "I never used a cursor. When developers have no other idea then they use it cursor".
Now this is quite strange. How will one iterate without using cursor and while loop. Yes joins can be an option. But is it possible we can get ride of cursor 100%.
I have written a stored procedure, where I am using a cursor and it fetches all the records and I am doing calculation type of things for all the records and then final row I am showing the avg(particular column), sum(particular column).
for eg, I am showing only 3 columns as of now. Now I m using cursor to fetch each hub(A,B..)
and then calculate.
-->to calcualte totalrequest
I calculate totalpick
I calculate totaldrop
totalrequest=totalpick + totaldrop
(lot of joins used)
-->avgrequest(using joins from multiple tables)
-->occupancy(using joins from multiple tables)
==
then after the cursor ends
-->calculate totalsum of totalrequest
-->calculate average of avgrequest
-->calculate occupancy average
hub totalrequest avgrequest occupancy
A 34 20 2.4
B 16 20 1.6
C 20 20 1.0
D 10 10 2.5
E 20 30 2.5
FINAL 100 20 2.5
Is it possible to handle it in set based operation. This SP runs slow as in realtime I am doing it for one month data.
Kindly need your intervention..please help.