I have the following problem:

There is a table with assemblies and one with repairs.

The manager can choose to order those repairs or assemblies in a way they
should be handled. So I would need an order field on every repair (which
can be null). Now which value should I put in that order field? The end
user doesn't see those numbers of course, he just sees the order and can
move a repair up or down. I guess this is a very normal problem, are there
any best practises for this? What procedure should run when the items get
reordered?


Thanks in advance,

Stijn Verrept.

Thanks a lot Steve,

Tried it out and this seems to work great! Fast and clean solution! :)

[quoted text, click to view]

For those interested, here is the procedure to move an item down :)

create procedure move_down (
@move_this int
) as
declare @old_rank int
declare @new_rank int
set @old_rank = (
select ItemPosition
from #Example
where Item = @move_this
)
print 'Moving item ' + cast(@move_this as varchar(11)) + ' down.'
if @old_rank = (select max(ItemPosition) from #example) begin
print 'Item is already last. No changes made.'
return
end

update #Example
set ItemPosition = ItemPosition +
case when ItemPosition = @old_rank
then +1
else -1 end
where ItemPosition in(@old_rank + 1, @old_rank)
go

Let's create a simple table of jobs waiting in queue to be serviced.
The job with priority #1 is assumed to be the next job to do.

CREATE TABLE RepairQueue
(priority_nbr INTEGER NOT NULL PRIMARY KEY,
job_ticket CHAR(10) NOT NULL UNIQUE);

INSERT INTO RepairQueue VALUES(1, 'Task A');
INSERT INTO RepairQueue VALUES(20, 'Task B');
INSERT INTO RepairQueue VALUES(31, 'Task C');
INSERT INTO RepairQueue VALUES(46, 'Task D');
INSERT INTO RepairQueue VALUES(59, 'Task E');

Pu this UPDATE into a proc to close any gaps in queue numbers; it will
make life easier.

CREATE PROCEDURE RenumberQueue()
AS
UPDATE RepairQueue
SET priority_nbr
= (SELECT COUNT (*)
FROM RepairQueue AS R1
WHERE R1.priority_nbr <= RepairQueue.priority_nbr);
SELECT * FROM RepairQueue ORDER BY priority_nbr;

I have a lot of other procs you need for this kind of system -- insert a
new job, delete a job, make a job history record, etc. but you were
just interested in changing the priority of the jobs:

CREATE PROCEDURE Changepriority_nbr (@target_job_ticket CHAR(20),
@new_priority_nbr INTEGER)
AS
BEGIN
DECLARE @current_priority_nbr INTEGER;
-- EXEC RenumberQueue();
SET @current_priority_nbr
= (SELECT priority_nbr
FROM RepairQueue
WHERE job_ticket = @target_job_ticket);
UPDATE RepairQueue
SET priority_nbr
= CASE WHEN @new_priority_nbr < @current_priority_nbr
THEN CASE
WHEN priority_nbr
NOT BETWEEN @new_priority_nbr
AND @current_priority_nbr
THEN priority_nbr
WHEN priority_nbr = @current_priority_nbr
THEN @new_priority_nbr
WHEN priority_nbr BETWEEN @new_priority_nbr
AND @current_priority_nbr
THEN priority_nbr +1
ELSE priority_nbr END
WHEN @new_priority_nbr > @current_priority_nbr
THEN CASE
WHEN priority_nbr
NOT BETWEEN @current_priority_nbr
AND @new_priority_nbr
THEN priority_nbr
WHEN priority_nbr = @current_priority_nbr
THEN @new_priority_nbr
WHEN priority_nbr BETWEEN @current_priority_nbr
AND @new_priority_nbr
THEN priority_nbr -1
ELSE priority_nbr END
ELSE priority_nbr END
WHERE @new_priority_nbr <> @current_priority_nbr;
END;

The @current_priority_nbr can be replaced with a scalar subquery, if you
wish. But it will be harder to read.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***