# 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;
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+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''103'' WHERE Mfg_Start = '''+s4+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''103'' WHERE Mfg_Start = '''+s3+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''104'' WHERE Mfg_Start = '''+s2+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''105'' WHERE Mfg_Start = '''+s1+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''106'' WHERE Mfg_Start = '''+sToday+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''107'' WHERE Mfg_Start = '''+s1add+''' ';

sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''108'' WHERE Mfg_Start = '''+s2add+''' ';
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;

###### 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.

Oracle 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
Network 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
Oracle 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.

###### 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.