Accounting work days

11 Aug 2006 10:10 PM

zzx375

If anyone is familiar with the accounting concept of work days, that is
what I am trying to accomplish.

As an example of what I am mean, 9/1/06 is work day 1, 9/2, and 9/3
would both be work day 1 (weekend), 9/4 would still be work day 1
(holiday). 9/5 -9/8 would be days 2-5 followed by 9/9 and 9/10 which
would both be work day 5, 9/11 would be work day 6, etc.

I have created a calendar table, so I know which day of the week it is,
plus my holidays are flagged. The algorithm to assign the workday is
probably very straight forward but I am just having a mental block.

Thanks in advance for your suggestions.

11 Aug 2006 10:26 PM

Michael Keating

Hi,

I'm unsure of your exact requirements, but have a look at the WEEKDAY
helpfile data, along with DATEPART.
These two should be able to provide the info you need given any combination
of day1 (usually Sunday in US, Monday in UK) and a given date.

MFK

<zzx***@cox.net> wrote in message
Show quote

news:1155334202.074019.85570@75g2000cwc.googlegroups.com...
> If anyone is familiar with the accounting concept of work days, that is
> what I am trying to accomplish.
>
> As an example of what I am mean, 9/1/06 is work day 1, 9/2, and 9/3
> would both be work day 1 (weekend), 9/4 would still be work day 1
> (holiday). 9/5 -9/8 would be days 2-5 followed by 9/9 and 9/10 which
> would both be work day 5, 9/11 would be work day 6, etc.
>
> I have created a calendar table, so I know which day of the week it is,
> plus my holidays are flagged. The algorithm to assign the workday is
> probably very straight forward but I am just having a mental block.
>
> Thanks in advance for your suggestions.
>

11 Aug 2006 10:55 PM

Mike C#

Aaron has some samples of queries against calendar tables at aspfaq:
http://www.aspfaq.com/show.asp?id=2453 and
http://www.aspfaq.com/show.asp?id=2519

<zzx***@cox.net> wrote in message
Show quote

news:1155334202.074019.85570@75g2000cwc.googlegroups.com...
> If anyone is familiar with the accounting concept of work days, that is
> what I am trying to accomplish.
>
> As an example of what I am mean, 9/1/06 is work day 1, 9/2, and 9/3
> would both be work day 1 (weekend), 9/4 would still be work day 1
> (holiday). 9/5 -9/8 would be days 2-5 followed by 9/9 and 9/10 which
> would both be work day 5, 9/11 would be work day 6, etc.
>
> I have created a calendar table, so I know which day of the week it is,
> plus my holidays are flagged. The algorithm to assign the workday is
> probably very straight forward but I am just having a mental block.
>
> Thanks in advance for your suggestions.
>

11 Aug 2006 11:15 PM

Vern Rabe

If your calendar table is something like this:

CREATE TABLE dbo.Calendar (CalendarDate smalldatetime, WorkDayFlag bit);

Then something like this might work:

SELECT C.CalendarDate,
   (SELECT COUNT(*)
      FROM dbo.Calendar W
      WHERE YEAR(W.CalendarDate) = YEAR(C.CalendarDate)
      AND MONTH(W.CalendarDate) = MONTH(C.CalendarDate)
      AND W.WorkDayFlag = 1
      AND W.CalendarDate <= C.CalendarDate) AS WorkDayNumber
   FROM dbo.Calendar C;

HTH
Vern Rabe

Show quote

"zzx***@cox.net" wrote:

> If anyone is familiar with the accounting concept of work days, that is
> what I am trying to accomplish.
>
> As an example of what I am mean, 9/1/06 is work day 1, 9/2, and 9/3
> would both be work day 1 (weekend), 9/4 would still be work day 1
> (holiday). 9/5 -9/8 would be days 2-5 followed by 9/9 and 9/10 which
> would both be work day 5, 9/11 would be work day 6, etc.
>
> I have created a calendar table, so I know which day of the week it is,
> plus my holidays are flagged. The algorithm to assign the workday is
> probably very straight forward but I am just having a mental block.
>
> Thanks in advance for your suggestions.
>
>

14 Aug 2006 1:38 PM

zzx375

Thx for feedback everyone. I got me over the mental hump!

Vern Rabe wrote:
Show quote

> If your calendar table is something like this:
>
> CREATE TABLE dbo.Calendar (CalendarDate smalldatetime, WorkDayFlag bit);
>
> Then something like this might work:
>
> SELECT C.CalendarDate,
>    (SELECT COUNT(*)
>       FROM dbo.Calendar W
>       WHERE YEAR(W.CalendarDate) = YEAR(C.CalendarDate)
>       AND MONTH(W.CalendarDate) = MONTH(C.CalendarDate)
>       AND W.WorkDayFlag = 1
>       AND W.CalendarDate <= C.CalendarDate) AS WorkDayNumber
>    FROM dbo.Calendar C;
>
> HTH
> Vern Rabe
>
> "zzx***@cox.net" wrote:
>
> > If anyone is familiar with the accounting concept of work days, that is
> > what I am trying to accomplish.
> >
> > As an example of what I am mean, 9/1/06 is work day 1, 9/2, and 9/3
> > would both be work day 1 (weekend), 9/4 would still be work day 1
> > (holiday). 9/5 -9/8 would be days 2-5 followed by 9/9 and 9/10 which
> > would both be work day 5, 9/11 would be work day 6, etc.
> >
> > I have created a calendar table, so I know which day of the week it is,
> > plus my holidays are flagged. The algorithm to assign the workday is
> > probably very straight forward but I am just having a mental block.
> >
> > Thanks in advance for your suggestions.
> >
> >