0

I designed a Shop Floor Scheduling System and it’s been running well for years, but the managers are now asking for something more precise and what they are asking for puts me outside my comfort zone. I hope you can help.

We recently upgraded to SQL Server 2017 (Purchased Version)

The current Scheduling System operates on a fixed number of hours (XX per resource) every day, seven days a week, but now they want to schedule based on more flexible hours per day that can account for scheduled maintenance, vacation time and employee availability. There are about 3,000 active operations at any given time and about 30 resources, so efficiency is important.

I'm starting with one Table and one View…

The Operation_Start_By_Date = OperationDueDate - Operation_Duration (Hours) This way we an operation with more hours will be started before an operation with less hours if they are both due to complete on the same day.

Operations_TBL

Operation_ID      
Operation_Name      
Operation_Duration      
Resource_ID_Link      
Operation_Due_Date      
Operation_Start_By_Date

Sample of Operations Data

Resource_Availability_View

SELECT        
TOP (100) PERCENT        
Resource_Availability_ID,        
Resource_ID_Link,        
Available_Start_Date,        
Available_End_Date,        
DATEDIFF(hh, Available_Start_Date, Available_End_Date) AS DayHours        
FROM        
dbo.Resource_Availability_TBL        
WHERE        
(Available_Start_Date_FLD > GETDATE())        
ORDER BY Available_Start_Date

Sample of Resource Availability Data

The Operation_Duration is an integer data type representing hours. It is reduced each time an employee books time to that operation.

The Available_Start_Date and Available_End_Date are datetime data types representing the date and time each resource will be available each day.

This is where I need help…

Assuming we will start processing the operations in order of the Operation_Start_By_Date. I need a query to generate the operation start and end dates by subtracting Operation_Duration (hours) from a running sum of the available resource hours for each resource.

What query or maybe temp table would be used for output like this?

The first 34 hour Operation ID 1 on Resource ID 1 will run

8 hours on 2019-06-14 leaving 26 hours left at the end of the day

6 hours on 2019-06-15 leaving 20 hours left at the end of the day

5 hours on 2019-06-16 leaving 15 hours left at the end of the day

8 hours on 2019-06-17 leaving 7 hours left at the end of the day

7 hours (of 8) on 2019-06-18 to finish leaving 1 hour left to start the next 20 hour operation ID 3

Then show when the operations will start and end like this.

Temp tables maybe?

enter image description here

  • 1
    Nobody has responded yet,that means nobody understood your requirement.Don't mention view for now.Just throw table ,sample data and output that you need. – KumarHarsh Jun 14 at 6:27
  • 1
    You're getting there - try checking out some examples of MCVEs to help others help you. Screenshots like these create more work for those who want to work on your problem. – LowlyDBA Jun 14 at 15:25
  • 1
    It makes our job easier helping you if you would post your DDL and some insert statements. – dfhwze Jun 14 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.