Solved

Find date record in calendar table based on Start Date and # of Days to add

Posted on 2011-09-27
7
327 Views
Last Modified: 2012-05-12
I have a calendar table with calendar.calendardays (every day of the year), calendar.Weekdays (Monday - Sunday) and calendar.OfficeWorkdays (states whether it is a workday, holiday, weekend or closure).

I have a delphi form where user inputs the Start Date and # of Days in Mfg.  What I would like to happen on EditValueChange is to calculate the End Date based on (Start Date + # of Days in Mfg) BUT it should only add days that are marked as 'Workday' in the Calendar.OfficeWorkdays field.

For example, start date on my test record is 11/15/11. When I enter 2, the result should be 11/16/11 BECAUSE the Start Date is INCLUDED in the 2 days. Another example with the same start date of 11/15 and user enters in 5, resulting date should be 11/21/11. 15th is day 1, 16th is day 2, 17th is day 3, 18th is day 4, SKIPS 19th and 20th because they are in Calendar.OfficeWorkdays as 'Weekend' and the 21st is the next 'Workday' and that is day 5.

Below code is my attempt... but as soon as I hit a non-Workday, I get incorrect results. What I tried to do is get the count of non-Workdays, get the start date and get the # of days to add, then ADD that all together but what happens is I end up on a Sunday. I also have a CalendarView I was going to test out and it is simply the Calendar table but filtering only for OfficeWorkdays = 'Workday'.

Thank you for your help!
procedure TfrmMfgScheduleForm.edMfgDaysPropertiesEditValueChanged(
  Sender: TObject);
var sMfgStart, sDays, sSql, sDaysToAdd, sMfgComplete : String;  dMfgStart, dDays, dMfgComplete, dDaysToAdd :TDateTime;
        fDayToAdd : Variant;
begin
  inherited;

  dMfgStart     := StrToDate(edMfgStart.Text);
//  dMfgComplete  := StrToDate(edMfgTarget.Text);
  fDayToAdd     := StrToFloat(edMfgDays.Text);
//  dDays         := StrToDate(DateToStr(dMfgStart + StrToFloat(edMfgDays.Text)));
  dDays         := dMfgStart + fDayToAdd;

  //how many non-workdays are there between start of mfg plus days to add?
  with dmMfgSchedule.qrySearch do begin
        Close;
        sql.Clear;
//        sql.Add('SELECT CalendarDays FROM CalendarView WHERE CalendarDays = ('''+DateToStr(dDays)+''') ');
        sql.Add('SELECT COUNT(ID) AS Expr1 FROM Calendar ');
        sql.Add('WHERE (CalendarDays BETWEEN '''+DateToStr(dMfgStart)+''' AND '''+DateToStr(dDays)+''') ');
        sql.Add('AND OfficeWorkDays <> ''Workday'' ');
        Open;
        end;
        sDaysToAdd      := dmMfgSchedule.qrySearch.FieldValues['CalendarDays'];
        sMfgComplete    := DateToStr( (fDayToAdd - 1) + (dMfgStart) + (StrToFloat(sDaysToAdd)) );

  //Calculate Mfg Start plus Days in Mfg
  sSql := ' UPDATE PCJobData SET Mfg_Complete = '''+DateToStr(dMfgComplete)+''' ';
  sSql := sSql + ' WHERE ID = '''+sJobID+''' ' ;
  dmMfgSchedule.ADOCommand1.CommandText:= sSql;
  dmMfgSchedule.ADOCommand1.Execute;

Open in new window

0
Comment
Question by:Bianca
  • 5
  • 2
7 Comments
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 500 total points
ID: 36710786
IMHO you should change your query using TOP fDayToAdd to extract just the top desidered records starting from a cartain date

something like




sql.Add('SELECT TOP '+StrToFloat(edMfgDays.Text)+' ID FROM Calendar ');
sql.Add('WHERE (CalendarDays >= '''+DateToStr(dMfgStart)+''') ');
sql.Add('AND OfficeWorkDays <> ''Workday'' ');

Open in new window

0
 

Author Comment

by:Bianca
ID: 36711415
SELECT     TOP (5) CalendarDays
FROM         Calendar
WHERE     (CalendarDays >= '11/15/11') AND (OfficeWorkdays <> 'Workday')

Using my example of Start Date = 11/15/11 and Days in Mfg = 5, the resulting date SHOULD be 11/21/11.

The result of the above query returns the top 5 records that are NOT workdays... I am unsure how to apply it to retrieve the result I am looking for... ??

Thanks!
0
 

Author Comment

by:Bianca
ID: 36711462
I changed the query to:

SELECT     TOP (5) CalendarDays
FROM         Calendar
WHERE     (CalendarDays >= '11/15/11') AND (OfficeWorkdays <> 'Workday')

and the result shows the last record as THE DATE I am looking for... how do I take the LAST record from the resulting dataset?

Instead of TOP(5) is there a way to get LAST(1) of the TOP(5) or something like that?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Assisted Solution

by:Bianca
Bianca earned 0 total points
ID: 36712057
SELECT     MAX(CalendarDays) AS CalendarDays
FROM         (SELECT     TOP (5) CalendarDays
                       FROM          Calendar
                       WHERE      (CalendarDays >= '11/15/11') AND (OfficeWorkdays = 'Workday')) AS Calendar_1

This gives me the MAX Result from my TOP select! Thanks for your assistance!
0
 

Author Closing Comment

by:Bianca
ID: 36898957
I found the answer during my testing.
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 36714935
Back to my office now.

I didn't know the data type so I've suggested to use Top to get the first matching records.
Now I understand exactly what was the need and yes, Top combined with Max do the trick
Glad to have helped you.

Just one thing: you can close the q. by yourself accepting and grading what you prefere instead of asking for a closing. Same result, but a little bit time and job saved by moderators ;-)
0
 

Author Comment

by:Bianca
ID: 36719854
Thanks Ferrucio! Check out my other question if you have time... similar to this one...

http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_27346575.html
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

Suggested Solutions

Title # Comments Views Activity
how to update exe applicatio from internet ? 6 80
Query 14 55
INSERT DATE FROM STRING COLUMN 18 56
Whats wrong in this query - Select * from tableA,tableA 11 31
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.
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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