Machine Utilisation chart

[Mark.rooker]

Good afternoon guys, I am hoping someone could with a problem I have encounter?

Apologies if I do not explain what I want in understandable format!

I am trying to create a chart that shows how a machine is utilised over a period of time and plot this in graph format. I have been able to do this in a simple format where by on the day a job is scheduled to run it will plot the total number of hours it is planned to run, the problem being is say the machine is only available 10 hours per day (Mon to Fri) but the job is due to run for 35 hours the graph will plot 35 hours on day 1 rather than 10 hours day 1, 2 & 3 then 5 hours on day 4 like I would want

I am not expecting anyone to write this for me but just point me in the right direction on a method on how to achieve the desired results
The tables I have are as follows:

Table 1 'Orc_Tasks' that contains all my scheduled data, the relevant columns are as follows:
JobCode
PrimaryResourceID
WorkTimeMins_Actual
StartDate
FinishDate

Table 2 'Orc_Resources' that contain the machine data, the relevant columns are as follows:
Resource Code (this is what would link with 'Orc_Tasks'
ShiftPatternID
ShiftPatternDay (int (1 through to 7 Mon-Sun))

Table 3 'Orc_ShiftPatternHours' that contains the machine availability, , the relevant columns are as follows:
ShiftPatternId (this would link with 'Orc_Resource')
ShiftPatternDay (this would link with 'Orc_Resource')
ShiftStartTime
ShiftFinishTime

Many thanks in advance for any guidance