Solved

# how to assign a value based on a job record's start date, order and # of hours per job

Posted on 2011-10-11
Medium Priority
264 Views
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.

Thanks!
jobschedule.bmp
0
Question by:Bianca
• 6
• 4
• 3

LVL 32

Expert Comment

ID: 36952919

I would handle this in the database query such that the program does not contain any calculations. If you want to do it from the program, you will need to iterate your dataset, does not sound too efficient to me.

Here is what I would do in SQL Server query
``````declare @JobSchedule table(
[Start Date] date,
[Order] int,
[HoursPerJob] real,
[Job Schedule] real)

declare @Temp table(
[Start Date] date,
[Order] int,
[HoursPerJob] real,
[Job Schedule] real)

insert into @JobSchedule
select '10/10/2011', 1, 2, 0
union all
select '10/10/2011', 2, 4, 0
union all
select '10/10/2011', 3, 2, 0
union all
select '10/10/2011', 4, 1, 0
union all
select '10/11/2011', 1, 2, 0
union all
select '10/11/2011', 2, 4 , 0
union all
select '10/11/2011', 2, 2.3, 0
union all
select '10/11/2011', 1, 2, 0
union all
select '10/11/2011', 4, 1, 0
union all
select '10/12/2011', 1, 2, 0
union all
select '10/12/2011', 2, 3, 0
union all
select '10/12/2011', 3, 2, 0
union all
select '10/12/2011', 4, 1, 0
union all
select '10/13/2011', 1, 3, 0
union all
select '10/13/2011', 2, 3, 0
union all
select '10/13/2011', 3, 2, 0
union all
select '10/13/2011', 4, 1, 0

declare @rt real

set @rt = 0

insert into @Temp
select *
from @JobSchedule
where [Start Date] < GETDATE()
order by [Start Date] desc, [Order] desc, [HoursPerJob] desc

;with cte2 as
( select top 100 percent [Start Date], [Order],	[HoursPerJob], [Job Schedule],
ROW_NUMBER() over (order by [Start Date] desc, [Order] desc, [HoursPerJob] desc) RN
from @Temp
where [Start Date] < GETDATE()
order by  [Start Date] desc, [Order] desc, [HoursPerJob] desc
)

UPDATE cte2
SET  @RT = [Job Schedule] = @RT - [HoursPerJob]

set @rt = 0

insert into @Temp
select *
from @JobSchedule
where [Start Date] >= GETDATE()
order by [Start Date], [Order], [HoursPerJob]

;with cte1 as
( select [Start Date], [Order],	[HoursPerJob], [Job Schedule]
from @Temp
where [Start Date] >= GETDATE()
)

UPDATE cte1
SET  @RT = [Job Schedule] = @RT + [HoursPerJob]

select *
from @Temp
order by [Start Date], [Order],	[HoursPerJob], [Job Schedule]
``````
0

LVL 32

Expert Comment

ID: 36952931
Here is a much cleaner version
``````declare @JobSchedule table(
[Start Date] date,
[Order] int,
[HoursPerJob] real,
[Job Schedule] real)

declare @Temp table(
[Start Date] date,
[Order] int,
[HoursPerJob] real,
[Job Schedule] real)

--set sample data
insert into @JobSchedule
select '10/10/2011', 1, 2, 0
union all
select '10/10/2011', 2, 4, 0
union all
select '10/10/2011', 3, 2, 0
union all
select '10/10/2011', 4, 1, 0
union all
select '10/11/2011', 1, 2, 0
union all
select '10/11/2011', 2, 4 , 0
union all
select '10/11/2011', 2, 2.3, 0
union all
select '10/11/2011', 1, 2, 0
union all
select '10/11/2011', 4, 1, 0
union all
select '10/12/2011', 1, 2, 0
union all
select '10/12/2011', 2, 3, 0
union all
select '10/12/2011', 3, 2, 0
union all
select '10/12/2011', 4, 1, 0
union all
select '10/13/2011', 1, 3, 0
union all
select '10/13/2011', 2, 3, 0
union all
select '10/13/2011', 3, 2, 0
union all
select '10/13/2011', 4, 1, 0

declare @rt real

set @rt = 0

insert into @Temp
select *
from @JobSchedule
where [Start Date] < GETDATE()
order by [Start Date] desc, [Order] desc, [HoursPerJob] desc

;with cte2 as
( select [Start Date], [Order],	[HoursPerJob], [Job Schedule]
from @Temp
where [Start Date] < GETDATE()
)

UPDATE cte2
SET  @RT = [Job Schedule] = @RT - [HoursPerJob]

set @rt = 0

insert into @Temp
select *
from @JobSchedule
where [Start Date] >= GETDATE()
order by [Start Date], [Order], [HoursPerJob]

;with cte1 as
( select [Start Date], [Order],	[HoursPerJob], [Job Schedule]
from @Temp
where [Start Date] >= GETDATE()
)

UPDATE cte1
SET  @RT = [Job Schedule] = @RT + [HoursPerJob]

select *
from @Temp
order by [Start Date], [Order],	[HoursPerJob], [Job Schedule]
``````
0

LVL 32

Accepted Solution

Ephraim Wangoya earned 2000 total points
ID: 36952956
From delphi (Again which I do not recommend)
You could do
``````procedure TForm1.UpdateRecords;
var
Field: TField;
Total: real;
begin
Field := JobSchedule.FieldByName('Job Schedule');
JobSchedule.IndexFieldNames := 'Start Date';

//set the negative values (Before today)
Total := 0;
JobSchedule.Filtered := False;
JobSchedule.Filter := Format('[Start Date] < %s', [DateToStr(Date)]);
JobSchedule.Filtered := True;
JobSchedule.Last;
while not JobSchedule.BOF do
begin
Field.AsFloat := Total - Field.AsFloat;
Total := Total - Field.AsFloat;
end;

//set positive value (Today and future)
Total := 0;
JobSchedule.Filtered := False;
JobSchedule.Filter := Format('[Start Date] >= %s', [DateToStr(Date)]);
JobSchedule.Filtered := True;
JobSchedule.Last;
while not JobSchedule.BOF do
begin
Field.AsFloat := Total + Field.AsFloat;
Total := Total + Field.AsFloat;
end;

JobSchedule.Filtered := False;
end;
``````
0

LVL 38

Expert Comment

ID: 36954449
or write a query ... (i don't know if your database is capable of this ... but oracle is)

``````CREATE TABLE TEST ( DATE_START DATE, ORDERNUM NUMBER(10), HOURS NUMBER(10,2) );
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/10/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 2);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/10/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 4);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/10/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 2);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/10/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 1);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 2);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 4);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 2.3);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 1);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 3);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 3);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 2);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 1);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/13/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 3);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/13/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 3);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/13/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 2);
Insert into TEST
(DATE_START,
ORDERNUM, HOURS)
Values
(TO_DATE('10/13/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 1);
COMMIT;
``````

sample query:
``````select
case when trunc(date_start) = trunc(sysdate) and ordernum = 1 then 'Today' end remark,
date_start "Start Date",
ordernum "Order",
hours "Hours Per Job",
case when trunc(date_start-sysdate) >= 0 then
sum(hours) over (
partition by
case when trunc(date_start-sysdate) >= 0 then 1 else 2 end
order by date_start asc, ordernum asc)
else
0 - sum(hours) over (
partition by
case when trunc(date_start-sysdate) >= 0 then 1 else 2 end
order by date_start desc, ordernum desc)
end job_schedule
from
(select date_start, ordernum, hours
from test) a
order by date_start, ordernum
``````

data output:

``````REMARK  START DATE      ORDER   HOURS   JOB_SCHEDULE
10/10/2011	1	2	-18.3
10/10/2011	2	4	-16.3
10/10/2011	3	2	-12.3
10/10/2011	4	1	-10.3
11/10/2011	1	2	-9.3
11/10/2011	2	4	-7.3
11/10/2011	3	2.3	-3.3
11/10/2011	4	1	-1
Today	12/10/2011	1	3	3
12/10/2011	2	3	6
12/10/2011	3	2	8
12/10/2011	4	1	9
13/10/2011	1	3	12
13/10/2011	2	3	15
13/10/2011	3	2	17
13/10/2011	4	1	18
``````
0

LVL 38

Expert Comment

ID: 36954518
don't you have a status field for a order ?

like
PLANNED, ACQUIRING_RESOURCES, READY_TO_START, STARTED, WAITING_FOR_RESOURCES, PAUSED, TECHNICAL_FAILURE, FINISHED, CANCELLED, POSTPONED

when you go from 1 status to another you could also add a status_change record
> lets you produce a report of how long technical_failures lasted
> or how long you had to wait for resources, etc
0

Author Comment

ID: 36957519
@ewangoya, I am getting an undeclared identifier for IndexFieldName...

Field := gvSchedulingJobSchedule.DataBinding.DataController.DataSet.FieldByName('Job Schedule');
JobSchedule.IndexFieldNames := 'Start Date'; <---- error here

THanks!
0

LVL 32

Expert Comment

ID: 36957618

The IndexFieldNames property really depends on what type of dataset you are using. My example is based on TClientDataset

You could also use IndexName,

And also a correction to the code
``````procedure TForm1.UpdateRecords;
var
Field: TField;
Total: real;
begin
Field := JobSchedule.FieldByName('Job Schedule');
JobSchedule.IndexFieldNames := 'Start Date';  //This could be IndexName, its just used for sorting

//set the negative values (Before today)
Total := 0;
JobSchedule.Filtered := False;
JobSchedule.Filter := Format('[Start Date] < %s', [DateToStr(Date)]);
JobSchedule.Filtered := True;
JobSchedule.Last;
while not JobSchedule.BOF do
begin
Field.AsFloat := Total - Field.AsFloat;
Total := Total - Field.AsFloat;
JobSchedule.Prior;
end;

//set positive value (Today and future)
Total := 0;
JobSchedule.Filtered := False;
JobSchedule.Filter := Format('[Start Date] >= %s', [DateToStr(Date)]);
JobSchedule.Filtered := True;
JobSchedule.First;
while not JobSchedule.EOF do
begin
Field.AsFloat := Total + Field.AsFloat;
Total := Total + Field.AsFloat;
JobSchedule.Next;
end;

JobSchedule.Filtered := False;
end;
``````
0

Author Comment

ID: 36957656
I'm using TcxGridDBDataController
0

Author Comment

ID: 36957886
I got this exception:
Additional Error Message: Current provider does not support the necessary interface for Index functionality

at this line:
dmMfgQuantities.qryScheduling.IndexName := 'Start Date';  //This could be IndexName, its just used for sorting

I commented that out along with the filters and it looked like it got 'stuck' here after it ran through one time:
begin
Field.AsFloat := Total - Field.AsFloat;
Total := Total - Field.AsFloat;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Prior;
0

Author Comment

ID: 36957912
I just checked the table, it actually posted '0' for the very last record when ordered by Mfg_Start, Orders but that records start date is not todays date, it is 10/19/11 then gets 'stuck' as I posted earlier...

Thanks again for all your help!
0

Author Comment

ID: 36958093
Hmm... here's my code and what it did was post '0' in ALL the JobSchedule fields.

procedure TfrmMfgQuantities.UpdateJobSchedule;
var
Field: TField;
Total: real;
begin
Field := gvSchedulingJobSchedule.DataBinding.DataController.DataSet.FieldByName('JobSchedule');
//  dmMfgQuantities.tblScheduling.IndexFieldNames := 'Start Date';  //This could be IndexName, its just used for sorting

//set the negative values (Before today)
Total := 0;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := False;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filter := Format('[Mfg_Start] < %s', [DateToStr(Date)]);
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := True;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Last;
while not gvSchedulingJobSchedule.DataBinding.DataController.DataSet.BOF do
begin
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Edit;
Field.AsFloat := Total - Field.AsFloat;
Total := Total - Field.AsFloat;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Prior;
end;

//set positive value (Today and future)
Total := 0;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := False;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filter := Format('[Mfg_Start] >= %s', [DateToStr(Date)]);
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := True;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.First;
while not gvSchedulingJobSchedule.DataBinding.DataController.DataSet.EOF do
begin
Field.AsFloat := Total + Field.AsFloat;
Total := Total + Field.AsFloat;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Next;
end;

gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := False;
end;
0

Author Comment

ID: 36958749
IT WORKS sort of: The only problem left is that the dataset is not sorted correctly (should be Mfg_Start, Orders) and so the JobSchedule is updating records in the wrong order. How do I order the data set?

Total := 0;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := False;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filter := Format('[Mfg_Start] < %s', [DateToStr(Date)]);
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Filtered := True;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Last;
while not gvSchedulingJobSchedule.DataBinding.DataController.DataSet.BOF do
begin
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Edit;
Schedule.AsFloat := Total - Hours.AsFloat;
Total := Schedule.AsFloat;
gvSchedulingJobSchedule.DataBinding.DataController.DataSet.Prior;
end;
0

LVL 38

Expert Comment

ID: 36962418
How do I order the data set?
use my solution :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small printâ€¦
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy reâ€¦
Integration Management Part 2
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month15 days, 10 hours left to enroll