SQL reordering Priority row-by-row

Question

I posted an earlier version of this question last week, but after further consideration I realized the situation is a bit more complicated than first described, and I could use some further help:

My SQL table has 3 fields that impact my problem: "Priority" as real, Start_Date as datetime, and Furnace_Name as varchar(10). As the user adds items to the table (via a VB.net Windows app), (s)he specifies the Priority for a given Start_Date.

If Item1 is Priority 1 in a specified Furnace, then Item2 in that same Furnace, may be Priority 2 and it will be given a Start_Date after Item 1.

But if Item2 is given Priority 0.5 (or any number less than the Priority of Item1), then its Start_Date will be before Item 1's Start_Date. Note: The user could specify a prioirty, say, of 3.75, or any number they want. Due to business rules a job with PR 0.5 could be pushed forward and end up after a job with a higher priority number in the same furnace.

After each Item is added, I want to go through the list of Items and Update all of the Priorities so they are integers, starting with the earliest Start_Date with a Priority =1, then 2, 3, etc. But the first time I posted this question I neglected to mention that renumbering Priorities occurs within each Furnace, so that each Furnace may have a Prioirty 1, 2, etc.

In addition, a specified furnace may have 1:n items with the same Priority. After I renumber these Priorities to integers, items that started with the same Priority should still have the same Priority.

A reader suggested:

Update Table 
Set Priority = 
    (Select Count(*) 
     From table
     Where Start_Date <= T.Start_Date)   
     From Table T   
Where Start_Date > getDate()

I enhanced this to:

Update tblTable
Set      Priority = 
 (Select Count(*) 
 From tblTable
 Where Start_Date <= T.Start_Date and
 Start_Date  >= @CutoffDate 
 and Furnace_Name = T.Furnace_Name
 )   
From tblTable T

But this doesn't quite work because the Priorities within a Furnace don't start from 1. Can someone clarify for me the best way to do this or approach the problem?

Here is some test data:

Sch_ID Furnace Start_Date Priority Desired Priority after Re-Order 372 1335 11/9/09 8:00 AM 1 1 380 1335 11/9/09 7:00 AM 1 1 314 1335 11/9/09 10:30 AM 2 2 324 1335 11/9/09 10:00 AM 2 2 235 1335 11/9/09 4:00 PM 0.5 3 234 1335 11/9/09 4:00 PM 0.5 3

403 1510 11/9/09 11:30 AM 2 2 404 1510 11/9/09 11:30 AM 2 2 402 1510 11/9/09 8:30 AM 2 2 389 1510 11/9/09 7:30 AM 1 1 390 1510 11/9/09 7:30 AM 1 1 388 1510 11/9/09 7:00 AM 1 1 374 1510 11/9/09 6:30 PM 3.5 4 383 1510 11/9/09 5:30 PM 3.5 4 385 1510 11/9/09 3:30 PM 3 3 386 1510 11/9/09 1:30 PM 3 3

Thank you.

P.S.

The reason why I need to re-number a Job's Priority in the first place is, after applying the business rules that pertain to scheduling Jobs, it is possible (albeit uncommon) for their Priorities to be out of numeric order. For example, the presence of a dupe in a different furnace could push the Start time of this current job back in time, forcing it to be later in the day than a Job w/ a higher PR number (and therefore lower priority).

Since the user indicates the relative importance of a Job in a furnace by specifying the PR, it is imperative that we renumber the Priorities after each job is added. The situation you described above where a job with PR 2 lies between 2 jobs with PR 1 cannot occur, although 2 or more jobs with the same priority would be scheduled at the same time.

I appreciate the help you've offered. Can you take it the last step?

Thank you.

Answer 1

I think it sounds like a bad idea to have a field that's set both by the user and by a semi-automatic algorithm by the application/database. Can't you just order based on both the Start_Date and the Priority when you selectfrom the table?

Answer 2

WITH    rows AS
        (
        SELECT  tt.*, DENSE_RANK() OVER (PARTITION BY Furnace_Name ORDER BY Priority) AS dr
        FROM    tblTable tt
        )
UPDATE  rows
SET     Priority = dr

Update 2:

Try this:

WITH    data (Sch_ID, Furnace, Start_Date, Priority, Pr) AS
        (
        SELECT 372, 1335, CAST('11/9/09 8:00 AM' AS DATETIME), 1, 1
        UNION ALL
        SELECT 380, 1335, '11/9/09 7:00 AM', 1, 1
        UNION ALL
        SELECT 314, 1335, '11/9/09 10:30 AM', 2, 2
        UNION ALL
        SELECT 324, 1335, '11/9/09 10:00 AM', 2, 2
        UNION ALL
        SELECT 235, 1335, '11/9/09 4:00 PM', 0.5, 3
        UNION ALL
        SELECT 234, 1335, '11/9/09 4:00 PM', 0.5, 3
        UNION ALL
        SELECT 403, 1510, '11/9/09 11:30 AM', 2, 2
        UNION ALL
        SELECT 404, 1510, '11/9/09 11:30 AM', 2, 2
        UNION ALL
        SELECT 402, 1510, '11/9/09 8:30 AM', 2, 2
        UNION ALL
        SELECT 389, 1510, '11/9/09 7:30 AM', 1, 1
        UNION ALL
        SELECT 390, 1510, '11/9/09 7:30 AM', 1, 1
        UNION ALL
        SELECT 388, 1510, '11/9/09 7:00 AM', 1, 1
        UNION ALL
        SELECT 374, 1510, '11/9/09 6:30 PM', 3.5, 4
        UNION ALL
        SELECT 383, 1510, '11/9/09 5:30 PM', 3.5, 4
        UNION ALL
        SELECT 385, 1510, '11/9/09 3:30 PM', 3, 3
        UNION ALL
        SELECT 386, 1510, '11/9/09 1:30 PM', 3, 3
        ),
        ranks AS
        (
        SELECT  *, DENSE_RANK() OVER (PARTITION BY Furnace ORDER BY CASE WHEN Priority = 0.5 THEN 1 ELSE 0 END, Priority) AS Dr
        FROM    data
        )
SELECT  *
FROM    Ranks
ORDER BY
        Furnace, Start_Date

This relies on the fact that original priorities are ordered the same as the StartDates.

You should decide what happens if they are not. Say, how these data would be ordered?

Date   Priority 
07:00  1
08:00  2
09:00  1
10:00  2

Answer 3

Seems to me that the main problem here is the underlying model, which makes this complicated. If you were to consider model change, most of your problems would go away, for example:


I know that this is not exactly what you have asked for, but in the long run...

UPDATE AFTER COMMENT:
This one should enable you to specify who scheduled what, when and where.