?
Solved

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

Posted on 2011-09-28
6
Medium Priority
?
423 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 23

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 23

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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

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 2000 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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

830 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