473,545 Members | 2,284 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 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
Dec 23 '05 #1
3 7714
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:

@dateFieldOrVar iable + 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.co m

"Jason Huang" <Ja************ @hotmail.com> wrote in message
news:uE******** ******@TK2MSFTN GP15.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 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

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(dat e, workDays, holidaysAreWork Days) 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******** ******@TK2MSFTN GP15.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 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

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 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;

}
}
}

Dec 28 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3306
by: Larry Woods | last post by:
I have a site that works fine for days, then suddenly, I start getting ASP 0115 errors with an indication that session variables IN SEPARATE SESSIONS have disappeared! First, for background information, I have a customized 500-100 page that sends the value of various session variables via email to my support site. The situation: On the...
0
2496
by: Bill Gates | last post by:
i have two servers this is happenning on. each has a maintenance plan with the same options selected... General Tab - applies to all databases Optimizations Tab - reorganize data and index pages - change free space per page to: 10% - remove unused space from database files
3
2186
by: aqualizard | last post by:
I am working on a project using classic ASP. Every time I install Windows and IIS, it works for 2-3 days and then starts having problems. Specifically, things that have to do with the FileSystemObject stop working. I have tried Win2k, WinXP, the IISs that go with them, different partitions (fresh install), turning Windows Update Off as...
5
14874
by: BlackFireNova | last post by:
I need to write a report in which one part shows a count of how many total records fall within the working days (Monday - Friday) inside of a (prompted) given date range, in a particular geographical region. I have written a query which prompts the user for the start and end dates. It also filters for entries which pertain to the particular...
10
3327
by: F6GGR | last post by:
Hello any body, Here is my problem; i use Access 2000. I have found a function named "Nb.Jours.Ouvres" (in english : "number of working days" , all days but Saturday and Sunday). The help says that the "reference" "msowcf.dll" has to be activated. I did it but it does not work and this file is for "MS office Web Components function...
5
1822
by: Basildk | last post by:
Hi. I have a strange problem. We have an asp.net application running on several server with different setups. On 2 of our servers we experience that the globalization settings are misbehaving. We have boiled the problem down to this: (exemplified by a very simple page)
4
11195
by: shilpareddy2787 | last post by:
Hello, I have some total values, I want to calculate percenatge of these Total Values. I want to divide the total with No. Of working Days Excluding Saturdays and Sundays in a given period. How to calculate the Total Number of working Days in a given period . Let us say If i give the period as 08/01/2008 to 08/15/2008, I want total number...
1
2193
by: almurph | last post by:
Hi, I have a trickly little problem that I hope you can help me with. I need to add *working* days to a DateTime object of the format: yyyy/MM/ dd For example: say I have a timestamp of the following date: 2008/10/24 <--tomorrow
2
2782
by: angi35 | last post by:
Hi, I'm working in Access 2000. I have a form with a series of date fields, showing the progress of a project from start to completion. There's a set of fields/controls for projected dates (when the project is projected to enter each stage of the process) and another set for actual dates. For my purposes, I'm only working with the projected...
0
7487
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7446
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6003
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5349
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3476
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3459
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1033
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
731
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.