On Fri, 23 Dec 2005 09:20:22 +0800, "Jason Huang"
<Ja************ @hotmail.com> wrote:
Hi,
In our C# Windows Form application, we are using the SQL Server 2000 as the
database server.
The Database table MyTable has a field RegistrationDat e which represents the
Date a client comes to our company to deliver his product. We have some
Working days for processing his product, like 20, 30, 40 days.
My question is how do I handle the working days problem, e.g., a client
comes at the Days February 15 where we have 40 Working days to process his
product, or a client comes at November 25 where we have 40 Working days to
process his product. We need to know what's the last day for a special
Working day period.
We are looking for a most simple way to handle this problem.
Would anyone give me some advice? Any help will be appreciated.
Jason
Jason,
The code below will do what you want, but there is a better way, if
your company does a lot of things like this.
Pick a date (Jan 3, 2006 would be a good one, since the 2nd is
probably a holiday) and call it WDay 1.
Make a table on a database with the following columns:
WDayID int, (PK)
Date datetime, (index)
WDay int,
IsHoliday bit
Now that you have the table populate it like this:
Beginning with Jan 3, 1006 as WDay 1, if a day is a working day (M-F
and not a holiday) then increment WDay by 1 and set the IsHoliday flag
to false. Holidays have the same WDay number as the previous work day
as do Saturday and Sunday. Here is what next week will look like in
your table:
1 1/03/2006 1 false (tue)
2 1/04/2006 2 false (wed)
3 1/05/2006 3 false (thu)
4 1/06/2006 4 false (fri)
5 1/07/2006 4 false (sat - not a work day)
6 1/08/2006 4 false (sun - not a work day)
7 1/09/2006 5 false (mon)
Carry this out for a couple of years or more.
What you have built is known by industrial engineers as a
manufacturing day calendar. Manufacturing Day Calendars allow you to
calculate completion dates etc. by adding and subtracting WDay
numbers.
To use the calendar search for the beginning WDay number by querying
for the start date in the WDay column. When you find the WDay number
add the number of days "Span" you have to get the job done and query
the table for the WDay you calculated. If you happen to query for a
WDay that is a friday you will get three days in you result set (if
friday is a holiday, you will get four). Use the earliest date in the
list of dates as your scheduled completion date.
Here is the code to do it with brute force:
*************** *************** *************** ************
using System;
using System.Collecti ons.Generic;
using System.Text;
namespace Workdays
{
class Program
{
static void Main(string[] args)
{
DateTime date = DueDate(DateTim e.Now, 30, false);
Console.WriteLi ne("Due date is {0:MM/dd/yyyy} - {1}", date,
date.DayOfWeek) ;
Console.ReadLin e();
}
static DateTime DueDate(DateTim e receivedDate, int workDays,
bool startSameDay)
{
DateTime dueDate = DateTime.Today;
DateTime startDate = DateTime.Today;
DayOfWeek dayOfWeek = DateTime.Now.Da yOfWeek;
switch (dayOfWeek)
{
case DayOfWeek.Sunda y:
startDate = startDate.AddDa ys(1);
break;
case DayOfWeek.Satur day:
startDate = startDate.AddDa ys(2);
break;
case DayOfWeek.Frida y:
if(! startSameDay)
{
startDate = startDate.AddDa ys(3);
}
break;
default:
if(! startSameDay)
{
startDate = startDate.AddDa ys(1);
}
break;
}
int dayCount = 0;
int thisDay = 0;
DateTime testDate = DateTime.Today;
while (dayCount != workDays)
{
testDate = startDate.AddDa ys(thisDay++);
if (testDate.DayOf Week != DayOfWeek.Satur day &
testDate.DayOfW eek != DayOfWeek.Sunda y)
{
Console.WriteLi ne("{0:MM/dd/yyyy} - {1} {2}", testDate,
testDate.DayOfW eek, dayCount + 1);
dayCount += 1;
}
else
{
Console.WriteLi ne("{0:MM/dd/yyyy} - {1}", testDate,
testDate.DayOfW eek);
}
}
dueDate = testDate;
return dueDate;
}
}
}
