Solved

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

Posted on 2011-09-28
6
413 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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