I have a job table that has the following fields: JobNum, MfgStartDate, Order, HoursPerJob and JobSchedule. Based on the MfgStart Date, Order and HoursPerJob, I need to assign a value to the JobSchedule field based on the relationship to that job record's order in the schedule (determined by the MfgStartDate and Order). This needs to reset and update once a day. I was thinking that the day would start at JobSchedule = 0 and for every record after that ordered by StartDate and Order, that Job schedule would increment based on the HoursPerJob.
For example, today has 3 job records. The 1st job is a 4 hour job, the 2nd job is a 5 hour job and the 3rd job is a 2 hour job. The 1st job's JobSchedule should = 4, the 2nd job's JobSchedule should = 9 (which is the previous jobs jobschedule + the 2nd job's HoursPerJob) and the 3rd job's JobSchedule should = 11. Incidentally, it needs to go backwards in time as well into the negative. So with the above example, if yesterday had a job record that was a 6 hour job, that should have a JobSchedule = (-6) and if there was another one ordered before that for 2 hours, that job's JobSchedule should = (-8).
Attached picture gives an example of this data AND what the resulting JobSchedule value should be.