Solved

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

Posted on 2011-09-28
6
415 Views
Last Modified: 2012-05-12
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
Comment
Question by:Bianca
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 36720443
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
 
LVL 19

Expert Comment

by:Thommy
ID: 36813333
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
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 36813648
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:Bianca
ID: 36815474
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
 

Author Comment

by:Bianca
ID: 36893944
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
 
LVL 19

Accepted Solution

by:
Thommy earned 500 total points
ID: 36930534
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question