Help replacing a CURSOR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help replacing a CURSOR

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-08-24 : 12:57:48

I'm a bit stuck here. I am trying to replace an existing CURSOR from following main logic:


SELECT 
	EmployeeNo 
	,SUM(RegHours + OverTime) AS Total
	,SUM(RegHours) AS RegHours
	,SUM(OverTime) AS OverTime
FROM Hours
WHERE (Date BETWEEN '1/1/2011' and '2/15/2011') 
	--AND EmployeeNo = 12345
GROUP BY EmployeeNo

What it does, if not filtered by employee which is commented above, calculate hours worked for a whole set of employees. The existing solution uses a Cursor to iterates over each row or employee, and returns those values for a given worker, which is the main goal.

How can I replace the cursor with pure T-SQL and maybe sub-queries or other similar row set alternative?

Thanks in advance,

PS: the code must be compatible with SQL2005

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 13:12:18

Doesn't the code you posted work correctly? It should give hours, one row per employeeNo and if you uncomment, the hours for a specific employee. The only thing I see as perhaps a problem is if Overtime or regular hours is null, it may not calculate the Total correctly - which can be fixed using ISNULL or COALESCE as follows:

SELECT 
	EmployeeNo 
	,SUM(COALESCE(RegHours,0) + COALESCE(OverTime,0)) AS Total
	,SUM(RegHours) AS RegHours
	,SUM(OverTime) AS OverTime
FROM Hours
WHERE (Date BETWEEN '1/1/2011' and '2/15/2011') 
	--AND EmployeeNo = 12345
GROUP BY EmployeeNo

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-08-24 : 13:56:33

I definitively need more coffee

Yes, the code works, but I need to do that "n" times for "n" employees, each one.

But I want to avoid a CURSOR for that.

Maybe the solution is in front on my nose already. It's that it took so many hours for my to understand the whole code that my brain is going to explode now, lol

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-24 : 14:38:44

that code should give you what you are asking for. It will do the calculations for ALL the employees in the table. If it does not, perhaps you can show us some example tables and sample output?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-08-24 : 15:42:28

I need to iterate and show that for all and every Employee.

I tried using a simple WHILE, and setting initial and last values to respective EmployeeID values, but it takes some time.

Running the simple query alone with the WHERE clause will only show results for one particular person.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 15:57:30

quote:
Originally posted by sql-lover

I need to iterate and show that for all and every Employee.

I tried using a simple WHILE, and setting initial and last values to respective EmployeeID values, but it takes some time.

Running the simple query alone with the WHERE clause will only show results for one particular person.

Do the following experiment and you will see what Don and I have been trying to say.

Step 1: This will show data for only one employee - you know that already.

SELECT 
	EmployeeNo 
	,SUM(COALESCE(RegHours,0) + COALESCE(OverTime,0)) AS Total
	,SUM(RegHours) AS RegHours
	,SUM(OverTime) AS OverTime
FROM Hours
WHERE (Date BETWEEN '1/1/2011' and '2/15/2011') 
	AND EmployeeNo = 12345
GROUP BY EmployeeNo

Step 2: Run this code - but change the employee numbers to some valid numbers. Run it just once, and you should see the data for both emloyees:

SELECT 
	EmployeeNo 
	,SUM(COALESCE(RegHours,0) + COALESCE(OverTime,0)) AS Total
	,SUM(RegHours) AS RegHours
	,SUM(OverTime) AS OverTime
FROM Hours
WHERE (Date BETWEEN '1/1/2011' and '2/15/2011') 
	AND EmployeeNo IN (12345, 56789)
GROUP BY EmployeeNo

The key thing here is that you did not use any while loops or anything, you just ran this code once and it showed the data for TWO employees.

Look at the output data and see if that is what you are trying to get if you assume that you had ONLY two employees. I will think you will see that it is.

Step 3: Remove the "AND EmployeeNo..." completely and run it JUST ONCE. You should see data for ALL employees.

SELECT 
	EmployeeNo 
	,SUM(COALESCE(RegHours,0) + COALESCE(OverTime,0)) AS Total
	,SUM(RegHours) AS RegHours
	,SUM(OverTime) AS OverTime
FROM Hours
WHERE (Date BETWEEN '1/1/2011' and '2/15/2011') 
GROUP BY EmployeeNo

So you DON'T need to use a loop.

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-08-24 : 16:26:19

I know what you mean!!!

Let me compare and check performance to what the developer had before, with CURSOR.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources