How to calculate Task Start Date based on 2 related fields that change based on sql table values

I have what seems to be a complex problem...  I can barely get my head around it actually.  Here goes...

I have a grid view that shows Job records and 9 tasks per record. Each Job record has a Start Date and an Order field (1,2,3,4).  I need highlighting on the grid (see attached image) which corresponds to the different tasks per job related to that job's start date and order. For example, Cutting, Flutes and Corbels are always Order 1-4 where Start Date = todays date. Whereas Kitting is always the first 3 for todays date AND the last 1 (order #4) of yesterdays date.  

Thanks!

The tricky part too is that the date calculation needs to be based off of a calendar table.

Here's code I have used in the past to calculate the workdays based on our calendar table that I use in a different app:
procedure TfrmMfgScheduleForm.UpdateMfgStages;
var sSql1, sMfgStart, sToday, s1, s2, s3, s4, s5, s6, s7, s8, s1add, s2add,s3add, s4add, s5add, s6add, s7add, s8add, s9add : String;
begin
  //UPDATE Mfg_Stages
    sToday     := DateToStr(Now);
    s1         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-2));
    s2         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-3));
    s3         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-4));
    s4         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-5));
    s5         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-6));
    s1add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 2));
    s2add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 3));
    s3add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 4));
    s4add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 5));
    s5add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 6));
    s6add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 7));
    s7add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 8));
    s8add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 9));
    s9add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 10));


    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''102'' WHERE Mfg_Start <= '''+s5+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''103'' WHERE Mfg_Start = '''+s4+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''103'' WHERE Mfg_Start = '''+s3+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''104'' WHERE Mfg_Start = '''+s2+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''105'' WHERE Mfg_Start = '''+s1+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''106'' WHERE Mfg_Start = '''+sToday+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''107'' WHERE Mfg_Start = '''+s1add+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''108'' WHERE Mfg_Start = '''+s2add+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;
end;


function TfrmMfgScheduleForm.GetNumberOfCalendarDaysFromNow(WorkDaysOffset: integer):integer;
var iEndDate, iToday : Integer; sToday : String;
begin
 with dmMfgSchedule.qrySearch do begin
   Close;
   sql.Clear;
   if WorkDaysOffset>=0 then begin
     sql.Add('SELECT MAX(CalendarDays) AS EndDate FROM ');
     sql.Add(' (SELECT TOP '+IntToStr(WorkDaysOffset)+' CalendarDays FROM Calendar ');
     sql.Add(' WHERE (CalendarDays >= '''+DateToStr(Now)+''') AND (OfficeWorkDays = ''Workday'')');
     sql.Add(' ORDER BY CalendarDays ASC) AS Calendar_1');
   end
   else begin
     sql.Add('SELECT MIN(CalendarDays) AS EndDate FROM ');
     sql.Add(' (SELECT TOP '+IntToStr(abs(WorkDaysOffset))+' CalendarDays FROM Calendar ');
     sql.Add(' WHERE (CalendarDays <= '''+DateToStr(Now)+''') AND (OfficeWorkDays = ''Workday'')');
     sql.Add(' ORDER BY CalendarDays DESC) AS Calendar_1');
   end;
   open;
 end;
 iEndDate := dmMfgSchedule.qrySearch.FieldValues['EndDate'];

 with dmMfgSchedule.qrySearch do begin
   Close;
   sql.Clear;
   sql.Add('SELECT CalendarDays AS Today FROM Calendar WHERE CalendarDays = '''+DateToStr(Now)+''' ');
   open;
 end;
 iToday   := dmMfgSchedule.qrySearch.FieldValues['Today'];

 result   := iEndDate - iToday;
// result:= (DateToStr(dmMfgSchedule.qrySearch.FieldByName('EndDate').AsDateTime));

end;

 what the grid should look like what the grid should look like
Norm-alNetwork EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
didn't you see my response in your earlier question
(if you want to keep performance high you'll have to take it into account at the beginning of the project, not at the end)

http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_27324618.html#36714856
0
Norm-alNetwork EngineerAuthor Commented:
Hi Geert, previous responses worked with how it calculates the work calendar. Actually just a few minutes ago the direction of this application was changed.  I will close this question and post the new one. Thank you for all you help in the past.
0
Geert GOracle dbaCommented:
are you aware that you are doing a lot of the sql work in delphi
you should look at a stored procedure approach for some things

your approach in general:
> query for info to db over network
> return lots of data to delphi app
> process data by delphi app
> post data back over network to db

better approach:
> call stored proc in db to process some data over network passing parameter values
> stored proc does calculation and updates db tables
> result data returned to delphi app

i am aware that you may need to do some investigating for sql stored proc skills
it will give your app a 2 to 500% increase in performance
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.