Assign priority value in stored procedure

[jbehrne]

Hi All,

I have problem that I am trying to handle in a SQL Server 2000 stored procedure. This stored procedure returns a list of records where the count from a sub-query is greater than 3. In these returned records I need to assign a priority number to groups of the records. Basically every three records would get the same number, the next three would get the next number (incremented by 1), and so on.

Can I handle this as a stored procedure?

My stored procedure so far:

ALTER PROCEDURE dbo.usp_Prioritization
@FY int)
AS
DECLARE @priorityOrder int
SELECT FY, InitiativeID FROM dbo.Initiatives
WHERE FY = @FY) AND EXISTS
(SELECT COUNT(FY) AS FYCount, FY
FROM dbo.Initiatives
GROUP BY FY
HAVING COUNT(FY) > 3))
RETURN

The fields in the table are:
FY - An integer
InitiativeID - An autoincrementing integer (PK)
PriorityOrder - Hopefully the output of the stored procedure
My output should look like this:

FY     InitiativeID     PriorityOrder (the generated field)
2010   1                1
2010   2                1
2010   3                1
2010   4                2
2010   5                2
2010   6                2
2010   7                3
2010   8                3
2010   9                3
2010   10               4
2010   11               4
2010   12               4
...

Thanks,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[MarkXaml]

I believe there are some misplaced parenthesis in your code. Without a sample of what some data would look like int the Initiatives table, it's hard for me to guess what you mean.

if that InitiativeID is an auto incrementing int, then the odds of it always beginning at 1 are slim to none. So, I would dump whatever query works for you into a temp table variable with an identity field and a NULL priorityorder field... then update the priority order with the ceiling of the identity value * .333 (because you want them grouped in 3's)

I am using SQL Server 2005, but it appears that CEILING is available in 2000 as well.

try this (not tested with the query you have posted)

DECLARE @temp TABLE (rownum	int IDENTITY(1,1)
					,FY	int
					,InitiativeID	int
					,PriorityOrder int)

INSERT INTO @temp
SELECT		FY
			,InitiativeID 
			,NULL			--will be updated later	
FROM		dbo.Initiatives
WHERE		--whatever criteria works for you in the order that you'd like to see it

UPDATE		@temp
SET			PriorityOrder = CEILING(rownum * .333)	

SELECT		FY
			,InitiativeID
			,PriorityOrder
FROM		@temp
ORDER BY	rownum

good luck!

 

[jbehrne]

Gryphon451,

Thanks for the response. Yes, it does look as if my original procedure is missing some parenthesis.

I'm about done for the day so I'll try it out tomorrow and see what I can come up with. Again, thanks!

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations