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

SQL statement to determine number of days to add or subtract based on work calendar

I have several update queries that update a Mfg_Stage field based on a record's Mfg_Start Date and it's relationship to today's date.  For example, if a job's Mfg_Start Date = 9/27/11 (today), the update will mark it as Mfg_Stage = 106.

I have the code working correctly based on the criteria I have BUT the kicker is that I need the calculations in my variables to add or subtract days ONLY if in our WorkCalendar states that the day is a 'workday'.  

The WorkCalendar has a field for CalendarDays(all the dates) and OfficeWorkday which states if its a workday, closure, holiday or weekend. SO the variables should add or subtract days BUT only calculate for the days that are marked 'Workday' in the WorkCalendar.

Attached is the code for the update queries I have AND below that is how I use the WorkCalendar for a different application.

Thanks for your help!
//UPDATE Mfg_Stages
    sToday     := DateToStr(Now);
    s1         := DateToStr(StrToDate(sToday) - 1);
    s2         := DateToStr(StrToDate(sToday) - 2);
    s3         := DateToStr(StrToDate(sToday) - 3);
    s4         := DateToStr(StrToDate(sToday) - 4);
    s5         := DateToStr(StrToDate(sToday) - 5);
    s1add      := DateToStr(StrToDate(sToday) + 1);
    s2add      := DateToStr(StrToDate(sToday) + 2);
    s3add      := DateToStr(StrToDate(sToday) + 3);
    s4add      := DateToStr(StrToDate(sToday) + 4);
    s5add      := DateToStr(StrToDate(sToday) + 5);
    s6add      := DateToStr(StrToDate(sToday) + 6);
    s7add      := DateToStr(StrToDate(sToday) + 7);
    s8add      := DateToStr(StrToDate(sToday) + 8);
    s9add      := DateToStr(StrToDate(sToday) + 9);


    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;

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

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

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

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

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

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

    sSql1 := ' UPDATE PCJobData SET Mfg_Stage = ''110'' WHERE Mfg_Start >= '''+s9add+''' ';
    dmMfgSchedule.ADOCommand1.CommandText := sSql1;
    dmMfgSchedule.ADOCommand1.Execute;


//How I use the WorkCalendar in a different application to add # of days to Mfg Start Date to calculate its Mfg Complete Date ONLY using workdays.
dMfgStart     := StrToDate(edMfgStart.Text);
  fDayToAdd     := StrToFloat(edMfgDays.Text);

  //find the date using MfgStart + Days in Mfg
  with dmMfgSchedule.qrySearch do begin
        Close;
        sql.Clear;
        sql.Add('SELECT MAX(CalendarDays) AS EndDate FROM ' );
        sql.Add(' (SELECT TOP '+FloatToStr(fDayToAdd)+' CalendarDays FROM Calendar WHERE (CalendarDays >= '''+DateToStr(dMfgStart)+''') AND (OfficeWorkDays = ''Workday'')) AS Calendar_1');
        Open;
        end;
        sMfgComplete := dmMfgSchedule.qrySearch.FieldValues['EndDate'];

Open in new window

0
Norm-al
Asked:
Norm-al
  • 2
  • 2
  • 2
1 Solution
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
Could you please explain a little more what is the value of Mfg_Stage and how is it assigned? I see some queries with the same value for different dates, and I'm in misunderstanding about this fact
0
 
ThommyCommented:
Check my function to calculate number of days from current date only regarding workdays...
//UPDATE Mfg_Stages
    sToday     := DateToStr(Now);
    s1         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-1);
    s2         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-2);
    s3         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-3);
    s4         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-4);
    s5         := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow(-5);
    s1add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 1);
    s2add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 2);
    s3add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 3);
    s4add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 4);
    s5add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 5);
    s6add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 6);
    s7add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 7);
    s8add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 8);
    s9add      := DateToStr(StrToDate(sToday) + GetNumberOfCalendarDaysFromNow( 9);
  ...



function GetNumberOfCalendarDaysFromNow(WorkDaysOffset: integer):integer;
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;
 
 result:= dmMfgSchedule.qrySearch.FieldByName('EndDate').AsDateTime-Now;
end;

Open in new window

0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
mmm, I think that the matter is not related to the enddate value, that is already right retrieved.

I guess that he'd like to update just those records where the date is a WorkDay.
What is not clear is how the Mfg_Stage value is updated and what is it before the update

Knowing this the original queries could be mixed in just one like, for example,

UPDATE PCJobData SET Mfg_Stage = (previous value+some constant, let's say 1) WHERE Mfg_Start in (SELECT TOP '+FloatToStr(fDayToAdd)+' CalendarDays FROM Calendar WHERE (CalendarDays >= '''+DateToStr(dMfgStart)+''') AND (OfficeWorkDays = ''Workday''))
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Norm-alNetwork EngineerAuthor Commented:
To clarify, each record has a Mfg Start Date. Depending on the difference between that Mfg Start Date and TODAY's date, that is how the record is placed into a Mfg Stage. For example, if Mfg Start Date = TODAY, it should be in Mfg Stage 106.  If the Mfg Start Date = (Today - 2), it should be in Mfg Stage 104 BUT where I got stuck is that the minus 2 part should subtract 2 days based on the Calendar where OfficeWorkday = 'Workday'

Oh wow, your last comment looks like I should be able to accomplish those updates into 1 update statement instead of my individual ones...! I will try the calculation first fo the correct days and see if it skips the non-Workdays.

Thanks guys!
Bianca (she)
0
 
Norm-alNetwork EngineerAuthor Commented:
Thommy, I am getting this error: Incompatible types Interger and Extended on the below line...

result:= (dmMfgSchedule.qrySearch.FieldByName('EndDate').AsDateTime) - Now;

Thanks for your assistance!!!
0
 
ThommyCommented:
Sorry, for late answer!!!

Should work with following changes:

function GetNumberOfCalendarDaysFromNow(WorkDaysOffset: integer):INT64;
begin
  ...
  result:= Trunc(dmMfgSchedule.qrySearch.FieldByName('EndDate').AsDateTime-Now);
end;

function GetNumberOfCalendarDaysFromNow(WorkDaysOffset: integer):INT64;
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;
 
 result:= Trunc(dmMfgSchedule.qrySearch.FieldByName('EndDate').AsDateTime-Now);
end;

Open in new window

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

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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