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
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?