?
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
13
Medium Priority
?
264 Views
Last Modified: 2012-05-12
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
Comment
Question by:Bianca
  • 6
  • 4
  • 3
13 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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]

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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]

Open in new window

0
 
LVL 32

Accepted Solution

by:
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;

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

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

your sample table data
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;

Open in new window


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

Open in new window


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

Open in new window

0
 
LVL 38

Expert Comment

by:Geert Gruwez
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

by:Bianca
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

by:Ephraim Wangoya
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;

Open in new window

0
 

Author Comment

by:Bianca
ID: 36957656
I'm using TcxGridDBDataController
0
 

Author Comment

by:Bianca
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

by:Bianca
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

by:Bianca
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

by:Bianca
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

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question