| Author |
 Topic  |
|
|
sql-lover
Yak Posting Veteran
59 Posts |
 Posted - 08/24/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/24/2012 : 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
59 Posts |
Posted - 08/24/2012 : 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
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
sql-lover
Yak Posting Veteran
59 Posts |
Posted - 08/24/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/24/2012 : 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
59 Posts |
Posted - 08/24/2012 : 16:26:19
|
I know what you mean!!!
Let me compare and check performance to what the developer had before, with CURSOR. |
|
|
| |
Topic |
|
|
|
Help replacing a CURSOR
