471,319 Members | 1,750 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

working days problem

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 RegistrationDate 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
Dec 23 '05 #1
3 7569
Jason,

Both SQL Server and the DateTime instance will allow you to add days to
a date to get another date. So, in SQL, you can just do:

@dateFieldOrVariable + 15

And that will give you the day 15 days out from the field or variable in
SQL.

In .NET, you would just call the AddDays method on the DateTime
instance, and that will return a new DateTime instance representing the
number of days forward.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:uE**************@TK2MSFTNGP15.phx.gbl...
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 RegistrationDate 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

Dec 23 '05 #2
I added a pretty extensive lib of Date functions such as SqlDateRange,
SqlTimeSpan, and functions like AddWorkDays() that call these classes.
The AddWorkDays(date, workDays, holidaysAreWorkDays) UDF, for example, takes
a date and adds number of workDays to it. Work days are defined as M-F. You
can also count (or not) holidays (the 10 US) as workdays. This requires Sql
2005 however. You could, however, reverse a solution for 2000 using the
logic, but doing this only in TSQL may get hairy.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390

--
William Stacey [MVP]

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:uE**************@TK2MSFTNGP15.phx.gbl...
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 RegistrationDate 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

Dec 23 '05 #3
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 RegistrationDate 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.Collections.Generic;
using System.Text;

namespace Workdays
{
class Program
{
static void Main(string[] args)
{
DateTime date = DueDate(DateTime.Now, 30, false);
Console.WriteLine("Due date is {0:MM/dd/yyyy} - {1}", date,
date.DayOfWeek);
Console.ReadLine();
}

static DateTime DueDate(DateTime receivedDate, int workDays,
bool startSameDay)
{
DateTime dueDate = DateTime.Today;
DateTime startDate = DateTime.Today;
DayOfWeek dayOfWeek = DateTime.Now.DayOfWeek;

switch (dayOfWeek)
{
case DayOfWeek.Sunday:
startDate = startDate.AddDays(1);
break;
case DayOfWeek.Saturday:
startDate = startDate.AddDays(2);
break;
case DayOfWeek.Friday:
if(! startSameDay)
{
startDate = startDate.AddDays(3);
}
break;
default:
if(! startSameDay)
{
startDate = startDate.AddDays(1);
}
break;
}

int dayCount = 0;
int thisDay = 0;
DateTime testDate = DateTime.Today;
while (dayCount != workDays)
{
testDate = startDate.AddDays(thisDay++);
if (testDate.DayOfWeek != DayOfWeek.Saturday &
testDate.DayOfWeek != DayOfWeek.Sunday)
{
Console.WriteLine("{0:MM/dd/yyyy} - {1} {2}", testDate,
testDate.DayOfWeek, dayCount + 1);
dayCount += 1;
}
else
{
Console.WriteLine("{0:MM/dd/yyyy} - {1}", testDate,
testDate.DayOfWeek);
}

}

dueDate = testDate;

return dueDate;

}
}
}

Dec 28 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bill Gates | last post: by
3 posts views Thread by aqualizard | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.