A Sequential Generator?? - 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

SQL Server Development (2000)

A Sequential Generator??

Author

Topic

belfastchild
Starting Member

11 Posts

Posted - 2006-10-31 : 16:29:56

Hi,

I have a stored proc(below) which assigns a task to an employee depending on which division they are in.

This is working fine, but is only selecting the first name in the table.

In order for the workload of task assigning to be spread evenly amongst the employees I need to come up with some way as to "sequentially" select an employee (actorname).
i.e. each time I go to the stored proc below I want to pick a different employee from the appropriate division each time until I get to the last employee of that division. Then I repeat the process again.

Hope I'm making myself clear

Any help would be appreciated??

CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50), 
@division nVarChar(50)

AS
IF @division = 'North'
BEGIN

--Get ActorName and ActorLogon if division is North
SELECT    dbo.Actor.ActorName, dbo.ActorRole.ActorLogon
FROM      dbo.Actor INNER JOIN
          dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE     dbo.ActorRole.RoleName = @roleName and
          dbo.ActorRole.North = '1'
END

--Get ActorName and ActorLogon if division is South
ELSE IF @division = 'South' 
BEGIN

SELECT    dbo.Actor.ActorName, dbo.ActorRole.ActorLogon
FROM      dbo.Actor INNER JOIN
          dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE     dbo.ActorRole.RoleName = @roleName and
          dbo.ActorRole.South = '1'
END

--Get ActorName and ActorLogon if division is West
ELSE IF @division = 'West' 
BEGIN

SELECT    dbo.Actor.ActorName, dbo.ActorRole.ActorLogon
FROM      dbo.Actor INNER JOIN
          dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE     dbo.ActorRole.RoleName = @roleName and
          dbo.ActorRole.West = '1'
END

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 05:27:12

Basically what I'm trying to do is......

If I have 20 employees in my Actor Table I need to select one that hasn't been selected before each time I go to the Actor Table.

This is required so that a different employee is assigned a task every time I call the sp_GetAssignedDetails from my application.

Once I get to the last employee and assign him/her a task, all employees have now had a task assigned so I then want to go back to employee No.1 and start all over again.

Any ideas?

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-01 : 05:32:16

So that rights order by newid() out then ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 05:43:06

SOmething like this

CREATE PROCEDURE sp_GetAssignedDetails
(
	@roleName nVarChar(50), 
	@division nVarChar(50)
)
AS

SET NOCOUNT ON

SELECT TOP 1	dbo.Actor.ActorName,
		dbo.ActorRole.ActorLogon
FROM		dbo.Actor
INNER JOIN	dbo.ActorRole ON dbo.ActorRole.ID = dbo.Actor.ID AND dbo.ActorRole.RoleName = @roleName
WHERE		<some criteria for excuding already assigned actors>
		AND (dbo.ActorRole.West = '1' AND @division = 'West'
			OR dbo.ActorRole.North = '1' AND @division = 'North'
			OR dbo.ActorRole.South = '1' AND @division = 'South'
			OR dbo.ActorRole.East = '1' AND @division = 'East')
ORDER BY	NEWID()

Peter Larsson
Helsingborg, Sweden

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 06:05:24

Cheers Peso.
Could you explain how newID() works? I'm new to T-SQL. Thrown in to the deep end as such.
When it gets through all employess how will it know to start at the first employee again?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 06:09:41

That's where the part of <some criteria for excuding already assigned actors> come to the resque.
How do you know that previous assignment is done? If actor A gets an assignment that lasts for three weeks, then actor B should be next, because his/hers assignment is done. You must build logic of how to allocate the resources.

NewID() creates a GUID, which is fairly random. Do a SELECT NEWID() in Query Analyzer and you will see.
ORDER BY NewID() creates a GUID for every row in the SELECT ~~database~~, and sorts by the GUID.

Peter Larsson
Helsingborg, Sweden

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 06:14:16

Good explanation.
Cheers!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-01 : 12:12:52

"sp_GetAssignedDetails from my application"

Incredibly bad name for your stored proc. Any stored procedure that begings with sp_ makes SQL look in the master database FIRST, no matter what.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

belfastchild
Starting Member

11 Posts

Posted - 2006-11-01 : 13:40:22

thanks for advice.

belfastchild
Starting Member

11 Posts

Posted - 2006-11-03 : 08:48:04

GOTCHA!!

CREATE PROCEDURE p_GetAssignedDetails (
@roleName nVarChar(50), 
@division nVarChar(50))

 AS

Begin

 
SET NOCOUNT ON
 
if ((SELECT     COUNT(*) 
FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleid
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division  = 'West'
OR dbo.ActorRole.North = '1' AND @division  = 'North'
OR dbo.ActorRole.South = '1' AND @division  = 'South')) = 0)

begin

update actor set processed = 0
WHERE  actorName in (SELECT     actorname
FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId
WHERE     (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division  = 'West' 
OR dbo.ActorRole.North = '1' AND @division  = 'North'
OR dbo.ActorRole.South = '1' AND @division  = 'South'))

end

SELECT     TOP 1 dbo.Actor.ActorName, dbo.ActorRole.ActorLogon
FROM         dbo.Actor left outer JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId 
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division  = 'West'
OR dbo.ActorRole.North = '1' AND @division  = 'North'
OR dbo.ActorRole.South = '1' AND @division  = 'South')

 
update actor set processed = 1
WHERE     actor.actorname = (SELECT     TOP 1 dbo.Actor.ActorName FROM         dbo.Actor LEFT OUTER JOIN
                      dbo.ActorRole ON dbo.Actor.ActorId = dbo.ActorRole.ActorRoleId 
WHERE     (dbo.actor.Processed = 0) AND (dbo.ActorRole.RoleName = @roleName)
AND (dbo.ActorRole.West = '1' AND @division  = 'West'
OR dbo.ActorRole.North = '1' AND @division  = 'North'
OR dbo.ActorRole.South = '1' AND @division  = 'South'))


end
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources