Solved

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

Posted on 2011-10-10
300 Views
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;

0
Question by:Bianca

LVL 36

Expert Comment

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

Author Comment

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

LVL 36

Accepted Solution

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

> 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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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 this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…