• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
0
Bianca
Asked:
Bianca
  • 2
1 Solution
 
Geert GruwezOracle 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
 
BiancaAuthor 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 GruwezOracle 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now