query optimisation question - 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 2000 Forums

Transact-SQL (2000)

query optimisation question

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2007-07-16 : 06:19:55

Hi all, I've got a script that I'm almost sure should be able to be expressed as a pivot table or a single query (but I don't know anything about them.) i'd like a few pointers because I have to deal with a lot of these types of problems. Currently the script uses a cursor :(
This script gets a dataset of employee ID's and a set of benefit records (there can be many benefit records per employee). I just want to bring back the status of the most recent benefit record per employee (highest Id). Here's the script as it stands (which works but is rather ugly)


DECLARE @employeeId 	INT

DECLARE @emplist TABLE	(staffNumber	VARCHAR(50) COLLATE Latin1_General_CS_AS
			,employeeId	INT
			,ebrId		INT PRIMARY KEY
			,ebrStatus	CHAR)

DECLARE @results TABLE	(staffNumber	VARCHAR(50) COLLATE Latin1_General_CS_AS PRIMARY KEY
			,employeeId	INT
			,ebrId		INT
			,ebrStatus	CHAR)
	
SET NOCOUNT ON

--  Get Search Set	
INSERT INTO @emplist
	SELECT
		e.staffnumber,
		e.id,
		ebr.id,
		ebr.status
	FROM
		employee e INNER JOIN employeeBenefitRecord ebr
			ON e.id = ebr.employeeId
	

DECLARE empcur CURSOR FOR
	SELECT DISTINCT employeeId FROM @emplist

OPEN empcur
	FETCH NEXT FROM empcur INTO @employeeId
 	WHILE @@FETCH_STATUS=0 BEGIN
		INSERT INTO @results
			SELECT TOP 1
				el.StaffNumber,
				el.employeeId,
				el.ebrId,
				el.ebrStatus
			FROM
				#emplist el
			WHERE
				el.employeeId = @employeeId
			ORDER BY
				el.ebrId DESC 

		FETCH NEXT FROM empcur INTO @employeeId
	END
CLOSE empcur
DEALLOCATE empcur

--  Display results
SELECT * FROM @results ORDER BY StaffNumber

-------

Anyone willing to enlighten me? All replies appreciated.

-------------
Charlie

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-16 : 08:21:56

have a look at group by - that will give you the max ebr.id for each employee.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-16 : 08:27:03

something like

select e.StaffNumber,
a.employeeId,
a.ebrId,
ebrStatus = ebr.Status
from (
SELECT
employeeId = employeeId ,
ebrid = max(id)
FROM
employeeBenefitRecord
group by employeeId
) a
join employee e ON e.id = a.employeeId
join employeeBenefitRecord ebr ON ebr.id = a.ebr_id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2007-07-16 : 09:25:28

That's great.

I didn't know you could set up a SELECT statement with an alias that way, I've learned a lot today.

Thanks,

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources