Counter

Last Post 12 Feb 2009 12:14 AM by SwePeso. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rchua
New Member
New Member

--
12 Jan 2009 08:48 AM

Employee ----------- Dept ----------- counter
emp1 ---------------- 1 ---------------- 1
emp2 ---------------- 1 ---------------- 2
emp3 --------------- 1 ---------------- 3
emp4 --------------- 2 ---------------- 1
emp5 --------------- 2 ---------------- 2
emp6 --------------- 3 ---------------- 1
emp7 --------------- 3 ---------------- 2
emp8 -------------- 3 ---------------- 3

I need help writing and SQL update query. The data consists on employee and Dept. I need an update statement that will update the counter column GROUP by dept. (The output should look like the one on top)
AlexB_SQL
New Member
New Member

--
28 Jan 2009 10:20 AM
I would make a cursor built on a select ordered by dept and employee.
Doing that, I would walk through cursor on each line and store dept value of line read on last cursor pace to know if Dept value is the same of Dept value of last cursor read and use pk to update counter value on each row.
I hope it helped.
nosepicker
New Member
New Member

--
29 Jan 2009 08:07 AM
Or, you can do this instead (this will only work if you have a unique key - in this case I'm going on the assumption that Employee is unique):

SELECT A.employee, A.dept, COUNT(*) AS counter
FROM YourTable A
JOIN YourTable B
ON A.employee >= B.employee
AND A.dept = B.dept
GROUP BY A.employee, A.dept
ORDER BY A.employee, A.dept

To turn this into an UPDATE statement:

UPDATE A
SET counter = X.counter
FROM YourTable A
JOIN
(SELECT A.employee, A.dept, COUNT(*) AS counter
FROM YourTable A
JOIN YourTable B
ON A.employee >= B.employee
AND A.dept = B.dept
GROUP BY A.employee, A.dept) AS X
ON A.employee = X.employee
SQLUSA
New Member
New Member

--
08 Feb 2009 04:29 AM
For SQL Server 2005 and SQL Server 2008 the following solution will work.

In this solution the GROUP BY is replaced by PARTITION BY since you don't need GROUP BY aggregates like SUM or AVG, only ROW_COUNT.

CTE is being used, but you can implement it as subselect (derived table) also.

;WITH cteStaff AS
(
SELECT EmployeeID, DepartmentID, Counter=ROW_NUMBER()
OVER(PARTITION BY DepartmentID order by EmployeeID)
FROM Employee
)
UPDATE e SET e.Counter = e1.Counter
FROM Employee e
INNER JOIN cteStaff e1
ON e.EmployeeID = e1.EmployeeID
GO

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com/bestpractices/dynamicsql/
SwePeso
New Member
New Member

--
12 Feb 2009 12:14 AM
For SQL Server 2005 and later, wouldn't this be more efficient and easier to read and understand?

UPDATE f
SET [Counter] = recID
FROM (
SELECT [Counter],
ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY EmployeeID) AS recID
FROM Employees
) AS f
You are not authorized to post a reply.

Acceptable Use Policy