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.



Sample of Operations Data


TOP (100) PERCENT        
DATEDIFF(hh, Available_Start_Date, Available_End_Date) AS DayHours        
(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 '19 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 '19 at 15:25
  • 1
    It makes our job easier helping you if you would post your DDL and some insert statements. – user179304 Jun 14 '19 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.