1. Join Date
Dec 1969
Posts
93

Hi,<BR><BR>I&#039;m going to create a console app that will check a sql server database to verify whether or not certain columns have been populated. I&#039;ll schedule that task to run daily using the schedule manager but I don&#039;t know how to write the sql code because it needs to check for business days. I need to verify that those fields have been populated within ten business days or a email gets sent. How do you account for business days?<BR><BR>Thanks in advance

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Very very tough to do...

...unless you have a separate special calendar table.<BR><BR>If it weren&#039;t for holidays, it would be possible. But how can you predict when holidays will fall, since they can fall on different days in different years?<BR><BR>If you can ignore holidays, then just change the code to look for *14* days, instead of 10. For many applications, that&#039;s close enough. But if you need it exact, then I&#039;d say you need to create a calendar in a separate table.<BR><BR>

3. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Not difficult to do!

If you select a base set of holidays, you can calculate them from year to year.<BR><BR>Holidays aren&#039;t randomly picked, they are either a specific day each year, the X occurence of a day in a month, X days after another holiday, or mathematically defined.<BR><BR>See my calendar:<BR>http://www.ourfamilyheart.com/Family/Events/Events.asp?FamilyId=4<BR><BR>It calculates a bunch of holidays.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## But in a SQL query???

Yes, creating the calendar using that code is easy enough.<BR><BR>But try applying the logic of "does Labor Day fall between these two dates? if so extend the business days by one" into the middle of a SQL query!<BR><BR>The calendar solution is trivial:<BR> Table: BizCalendar<BR> theDate: datetime field, but holds only date<BR> dayNumber : integer<BR><BR>You create one record for every day in the year. You put a -1 as the dayNumber for all weekend days and holidays. Then you sequentially number all the remaining days. (Do this all in an array and then insert the array into the db.)<BR><BR>So now you might have:<BR> 2003-9-30 : 178<BR> 2003-10-1 : 179<BR> 2003-10-2 : 180<BR> 2003-10-3 : 181<BR> 2003-10-4 : -1<BR> 2003-10-5 : -1<BR> 2003-10-6 : 182<BR>and to find out how many days there are between two biz dates, you just get the dayNumber of each and subtract!<BR><BR>

5. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Most of the calculations..

.. are pretty easy, so why couldn&#039;t you turn VBScript functions into SQL Server User Defined functions?<BR><BR>Then, you could use that when you&#039;re figuring your calculations (or even when populating this database table you propose)....<BR><BR>I dunno. You&#039;re probably right.

6. Senior Member
Join Date
Dec 1969
Posts
3,921

## I took a shortcut with a UDF

Granted, it means you have to hard code the holidays for every year. But you can easily spend 15 minutes and handle the next several years. It also allows for the addition of dates for special occasions or circumstances.<BR><BR>http://www.eggheadcafe.com/articles/20030626.asp<BR><BR>

7. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Exactly the start..

.. of what you could do.<BR><BR>This really wouldn&#039;t be that difficult to do. Maybe I&#039;ll use your code as a basis and implement it in T-SQL....

8. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Egg, if you are interested..

.. I could send you the VBScript, as well.

9. Senior Member
Join Date
Dec 1969
Posts
3,921

## Sure

10. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Sent, but for everyone else..

It&#039;s available here:<BR>http://localhost/Clients/AspMbExamples/USHolidays/USHolidays.asp

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•