?
Solved

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

Posted on 2011-09-28
6
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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