Priority Ranking

[jfgambit]

I was wondering if anyone had some experience with creating a module to rank according to a priority. I know how to create a module and query to rank based on a cost or date, but this is a little different.

I have a table tblTicketEsc that contains the following fields:

TicketID - Unique Ticket Escalation ID
PriorityID - Current Priority Ranking ( 1 - n )
ManPriorityID - Manually entered Priority Ranking (1 - n)

Example: 

TicketID       PriorityID       ManPriorityID
  12345                1    
  12346                2
  12347                3

I want to be able to add a number to ManPriorityID and have all the other numbers re-rank based on the Manual number entered.

[U]Example:[/U]
TicketID        PriorityID       ManPriorityID
 12345                1    
 12346                2                 1 <--I enter this number
 12347                3

[U]Becomes:[/U]
TicketID        PriorityID       ManPriorityID
 12346                1    
 12345                2
 12347                3

[U]Another Example:[/U]
TicketID        PriorityID       ManPriorityID
 12345                1                    3<--I enter this number
 12346                2                          
 12347                3                    1 <--I enter this number

[U]Becomes:[/U]
TicketID        PriorityID       ManPriorityID
 12347                1    
 12346                2
 12345                3

 

[scottn]

you want to change the ranking of an item from n to m. i guess you could look at it like this. there are two cases:

1. you're moving the item up (higher priority) in the ranking. in this case, n > m. in this case, you have to do these steps:

- take all the items ranked m to n-1 and bump their rankings up by one. you could write a bulk UPDATE query to do this pretty easily.
- set the ranking of the item in question to m.



2. you're moving the item down (lower priority) in the ranking. in this case, n < m. in this case, you'd to this

- take all the items ranked n+1 to m and bump their rankings down by one. again, you could use an UPDATE statement.
- set the ranking of the item in question to m.