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.
Operation_ID Operation_Name Operation_Duration Resource_ID_Link Operation_Due_Date Operation_Start_By_Date
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
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?