Cursor with stored procedure as source 1

[grnzbra]

I have a stored procedure which, when called from the query analyzer, gives a set of records.

My understanding of a cursor is that it is declared sort of as follows:

DECLARE csrDesk CURSOR
LOCAL
FORWARD_ONLY
STATIC
READ_ONLY
FOR
SELECT d.Desks, d.Average_Desk_Cost
FROM Desk_Costs d
WHERE d.Office_No = @OffNo AND MONTH(d.Desk_Date) = MONTH(@EnDt) AND YEAR(d.Desk_Date) = YEAR(@EnDt)

 

[grnzbra]

CONTINUED

What I need to do is to use the stored procedure as a source for this cursor. How would I do that?

 

[SQLSister]

Why on earth are you considering using a cursor? Cursors are evil and should be avoided where possible and it is alomost always possible to avoid them.

Insert the results of the stored procedure into a temp table then use it in set based operations just like you would any other table.



Questions about posting. See faq183-874

 

[grnzbra]

I need to look at the first record. If one of the fields is greater than a particular value, I add 1 to a counter and go to the next record and repeat until I hit one that is below that value and set a variable equal to its value. I then go to the last record and add its value to the value already in the variable. Then I check against the comparison value. If it is still below, I go to the next to the last record and add its value to the variable and compare. If it goes above the comparison value, I go back up to the record that originally fell below the the comparison value, move to the next record and set the variable equal to its value. Then I go to the record before the last one use before coming up from the bottom and repeat the process until the value again goes over the comparison value.

Wouldn't this still require the use of a cursor?

 

[SQLSister]

Ow, that made my head hurt. Probably you'll have to use a cursor. Suggest you still insert the results of the sp into a temp table then create the cursor on the temp table. Ain't gonna be efficient, but what can you do with a process like that?

Code for the insert (this assumes you have created the temp table)

insert into #temp1
Exec Sp_test 'inputvariablevalue'

Questions about posting. See faq183-874

 

[zhavic]

Hmmm, I was writing a diagram for your algorythm, but I stoped at this sentence:

If it is still below, I go to the next to the last record and add its value to the variable and compare.

What do you meen by this ? I think there are no 'next' records after the last record

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[grnzbra]

There is an average cost per desk (Office rent + other expenses divided by the number of desks) in an office. They take a list of money to company of the sales people sorted in decending order. When they hit a sales rep with money to company less than the average cost per desk (let's say record 20 out of 30 records), they place it in a holding variable. Then they go to the last record (30) and add its money to company value in the hold variable and compare the hold variable to the cost per desk. If it is still low, they go to the next previous record (29) and add its money to company to the hold value and compare. If it is equal to or more than the cost per desk, they add that to the count of paid-for desks. Then they go to the next record that didn't meet the cost per desk figure (31) and repeat the process.

Does that make some kind of sense now? (What they are doing, not necessarily why they are doing it this way.)

 

[donutman]

When a process gets that convoluted, you need to think outside of the box to find a more efficient way to get the job done. However, if after letting it percolate in your brain for a few days, you find no other way, then do it in an application not in a SP. That should open up a whole new list of creative ways to get the job done efficiently. A forward-only cursor is clearly not the way to go, even if there is no set-based solution.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[grnzbra]

That would be too easy. My boss wants it in a SQL Server SP.

 

[donutman]

Lol, ask him why. So it can hoze the server?

 

[ESquared]

I bet you can do the whole thing with aggregates, but your example still doesn't make it clear what exactly is going on.

 

[grnzbra]

What's going on is they divide up the expenses by the number of desks in the office. They rank all the sales reps according to the profit they earn the office.

If the first guy earns more than the cost for his desk, they count one desk as covered and go to the next guy. If he covers his desk, they go to the next guy etc until they hit someone who doesn't cover his desk. Then they take the last guy on the list and add him to the guy who didn't cover his desk. If the two of them together don't cover the desk, they add in the next lowest. If that covers the desk, they go to the second guy who didn't cover his desk and add the next lowest guy to him and keep repeating the process until there are no guys left because the stepping down from the top and the stepping up from the bottom have met in the middle.

I'm not sure how it could be done with aggregates.

 

[ESquared]

I understand the process you have described so far... can you help me understand more about what the numbers are supposed to look like when it's all done? And where the numbers start out?

As complete an example as you can give with, say, 5 desks and 5 guys would be sublime. Heck, make it gals for the example!

 

[grnzbra]

Avg cost per desk is $35,000

Gal 1 money to company = $38,000
Gal 2 = $35,350
Gal 3 = $33,100
Gal 4 = $1,100
Gal 5 = $1,000

Read Gal 1. Since she brought in > $35,000, add 1 to number of desks covered.

Read Gal 2. Since she brought in > $35,000, add 1 to number of desks covered. (Total desks covered now = 2)

Read Gal 3. Since she brought in < $35,000, go to last Gal in list (Gal 5) and add the money she brought in ($1,000) to the money brought in by Gal 3 ($33,100) and get a total of $34,100. Since $34,100 is less than $35,000, go to the next lowest (Gal 4) and add her $1,100 to the $34,100 total and get a total of $35,100. Since this is > $35,000, add 1 to the number of desks covered (Total desks covered now = 3). If there were more, Let's say there were 10 and Gals 1 through Gal 3 had the same numbers and Gals 4 and 5 now became Gals 9 and 10, but and had the same amount of money to company, after the above process we would go back up to the person who is now Gal 4 and read her money. Since it is lower than Gal 3, she also doesn't cover a desk. So we go to the bottom of the list. But we have already used 9 and 10, so we go to 8. Add her money to the money from Gal 4. If it is > $35,000, add 1 to the covered desks and go up to gal 5 and repeat. If the total is still less than $35,000, go to Gal 7 and add her money and run the check.

I'm doing this with two loops. The inner loop runs as long as the total is less than the desk cost. The addition to the number of desks covered is done in the outer loop. So, if the first record covers the cost of a desk, the inner loop doesn't run and 1 is added to the total. When the outer loop hits a record that doesn't cover a desk, the inner loop runs and picks up records starting at the bottom and adds to the money until the total money goes over the desk cost.

Both loops keep track of where they are, and when they meet, they stop and spit out the results.

Here are the loops:

WHILE @csrPos <= @FilRecNo AND @csrPos <= @Dsks
BEGIN
FETCH ABSOLUTE @RcdNo FROM csrPorCoRevRnk
INTO @CoNum, @OffNum, @OffNam, @AssocID, @Assoc, @PoCoRev
SET @PoCoRevHld = @PoCoRev


WHILE @PoCoRevHld < @AvgDskCst AND @RcdNo < @FilRecNo
BEGIN
FETCH ABSOLUTE @FilRecNo FROM csrPorCoRevRnk
INTO @CoNum, @OffNum, @OffNam, @AssocID, @Assoc, @PoCoRev
SET @PoCoRevHld = @PoCoRevHld + @PoCoRev
SET @FilRecNo = @FilRecNo - 1
END
IF @PoCoRevHld >= @AvgDskCst SET @PdFrDsks = @PdFrDsks + 1
--PRINT CAST(@PdFrDsks AS VARCHAR) + ' desks are paid for with Portion Co Rev of ' + CAST(@PoCoRevHld AS VARCHAR) + ' and ' + CAST(@PoCoRev AS VARCHAR) + ' ' + CAST((@PoCoRevHld - @PoCoRev) AS VARCHAR)
SET @csrPos = @csrPos + 1
SET @PoCoRevHld = 0
SET @RcdNo = @csrPos

END

PoCoRev and PoCoRevHld are the money
FilRecNo is the number of agents

 

[ESquared]

Thank you for explaining in detail. I will think about it today

May I ask why it works to use more than one person to cover a desk? And also, if you are looking to just barely cover a desk, is there a reason you go to the bottom and picked the lowest one? I mean, what if you're $2,000 short, and the lowest one only made $1,000. If you use it, you'll need to add another one and let's say it's $2,000, now you're $1,000 over a desk cost. But if you had used the $1,000 first you'd make the desk cost with only 2 people instead of 3 and not 'waste' the extra $1,000.

It sounds like the process is a standard one and can't be changed, though. I am asking just out of curiosity.

 

[grnzbra]

I have no idea. This is something the accountants want, and while I have a degree in accounting, I have never worked in the field. Therefore, the only thing that makes any sense to me in accounting is a theory of why the government pays $600 for a toilet seat. I just create the reports they want the system to spit out.

 

[grnzbra]

After the info obtained from the cursor was placed into a temporary table (#TempTable), I closed and deallocated the cursor. Should the SP have code at the end to do something similar to the table?

 

[ESquared]

Sigh

But if you had used the $1,000 first you'd make the desk cost with only 2 people

->

But if you had used the [red]$2,000[/red] first you'd make the desk cost with only 2 people

 

[grnzbra]

Personally, I think they should just total up the money brought in by the agents and see how many times the average desk cost will go into it. Now THAT would tell you how many desks were being covered by the agents. But, hey, they don't consult me about such things. I assume that there is some reason they are doing it this way, but I don't need to make that assumption in order to make the computer process it in this manner.

They don't trust me to engage in hazardous activities like thinking about their reasons for doing things a certain way. This whole process takes about seven seconds, 99.999% of which is taken up by their user-defined-function which produces the money to company list for the agents in an office. The problem is that it is a very convoluted calculation among the calculations for a large bunch of numbers (all of which have very convoluted calculations). I asked if they wanted me to have this sp create the money list first or create it in another sp and they insisted that I use their function. So I use it.

By the way, should I have any code at the end of the SP that closes the #TempTable?

 

[ESquared]

DROP TABLE #TempTable

I might be able to look at all this again today at work, but things have been hectic and I am not sure...