1. Member
Join Date
Dec 1969
Posts
59

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

## How exact does it need to be?

If (a) all your dates in the DB occur on Monday thru Friday and (b) you are willing to ignore the problem of holidays, then you can do an only moderately hairy calculation to get the number.<BR><BR>But if you need to take into account holidays...ugh. The only way I see to do it is create a calendar that starts at BusinessDay1 and assigns business day numbers to every date in the possible range. And then you do a "lookup" of the two business day numbers and subtract them.<BR><BR>To pick an example: Let&#039;s say that July 4th is a holiday. So we might have a calendar like this:<BR><BR>Table: BizDays<BR><BR>CalDate -- BizDay<BR>30 June 2003 -- 1377<BR>1 July 2003 -- 1378<BR>2 July 2003 -- 1379<BR>3 July 2003 -- 1380<BR>4 July 2003 -- 1380 [holiday, so biz day same as preceding day]<BR>5 July 2003 -- 1380 [Saturday, so ditto]<BR>6 July 2003 -- 1380 [Sunday, ditto]<BR>7 July 2003 -- 1381<BR>8 July 2003 -- 1382<BR><BR>So now you could do something like:<BR><BR>SELECT InvNum, InvAmt, BZ2.BizDay - BZ1.BizDay AS NumDays<BR>FROM Inovices, BizDays AS BZ1, BizDays AS BZ2<BR>WHERE BZ1.CalDate = InvNum.BillDate<BR>AND BZ2.CalDate = Date()<BR><BR>By the way, what&#039;s with the LEFT( RTNTIME, 8 )?????<BR><BR>UGH! <BR><BR>What DB are you using????<BR><BR>If Access, that should be DATEVALUE( RTNTIME )<BR>If SQL Server, then you need a CONVERT function in there.<BR><BR>

3. Member
Join Date
Dec 1969
Posts
59

## RE: How exact does it need to be?

All our data entry dates are between Monday - Friday and our company observes 6 holidays in a given year so must take holidays into an account when counting business days gap. I have a calendar table that has the whole year 2002 and each date has a field that marks the date "B" if it was a business day so I got that, but how do I join it or rather on what basis could I join the calendar table with fil1 table, since there isn&#039;t any common field between the two tables. The fil1 table could have entry dates ranging from 1991 - current.<BR><BR>The reason behind using LEFT function on RTNTIME is that though it&#039;s a timestamp field, it&#039;s data type is nvarchar and the value in it is something like "20030604155654" or it could be NULL. The query doesn&#039;t do an implicit conversion and when I try to specify a conversion with Convert or Cast function, it gives an error so I resorted to using the LEFT function to get only the date values after which I am able to convert or assign RTNTIME a datetime data type. If you have any other suggestions or know why it would fail, let me know.<BR><BR>I am using SQL Server 2000.

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

## Now go fix that calendar table...

To do what I suggested.<BR><BR>Start with 1991/1/1 being day 0 (say).<BR><BR>Add a "BizDayNumber" field to the calendar table.<BR><BR>And then increment the counter by one for each business day but don&#039;t increment it for non business days.<BR><BR>So something like this:<BR><BR>&#060;%<BR>SQL = "SELECT * FROM BizDaysCalendar ORDER BY Calendar.[Date]"<BR>Set RS = Server.CreateObject( "ADODB.Recordset" )<BR>RS.Open SQL, yourConnection, adOpenStatic, adLockPessimistic<BR><BR>counter = 0<BR><BR>Do Until RS.EOF<BR> If RS("BizFlag") = "B" Then counter = counter + 1<BR> RS("BizDayNumber") = counter<BR> RS.Update()<BR> RS.MoveNext<BR>Loop<BR>%&#062;<BR><BR>Luckily, you&#039;ll only need to do that once (well, and then update it when you add future years to it).<BR><BR>*NOW* you can use the technique I showed you in last msg, where you join to that BizDaysCalendar *twice* in your query. And it all works nice and neat.<BR><BR>

5. Member
Join Date
Dec 1969
Posts
59

## RE: Now go fix that calendar table...

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

## YOU are the one who said...

...and I quote: "The fil1 table could have entry dates ranging from 1991 - current."<BR><BR>What was I supposed to presume but that you might have queries that involved data going back that far?<BR><BR>***************<BR>"I just want to calculate how many business days passed between, let&#039;s say 6/1/03 and 6/9/03."<BR><BR>I *GAVE* you an example of that in my FIRST POST:<BR><BR>CalDate -- BizDay <BR>30 June 2003 -- 1377 <BR>1 July 2003 -- 1378 <BR>2 July 2003 -- 1379 <BR>3 July 2003 -- 1380 <BR>4 July 2003 -- 1380 [holiday, so biz day same as preceding day] <BR>5 July 2003 -- 1380 [Saturday, so ditto] <BR>6 July 2003 -- 1380 [Sunday, ditto] <BR>7 July 2003 -- 1381 <BR>8 July 2003 -- 1382 <BR><BR>If the two days in question were, say, 30 June 2003 to 8 July 2003, then you&#039;d do 1382 minus 1377 and get a difference of 5 business days. <BR><BR>You "numbered" calendar only has to go back as far as you will handle queries for.<BR><BR>***************<BR><BR>Having said all that... I *do* see the other way to do this! Using JUST your "B" flags in that calendar table!<BR><BR>DOH on me for not seeing it sooner:<BR><BR>SELECT Count(*)-1 FROM BizCalendar<BR>WHERE Flag=&#039;B&#039;<BR>AND BizDate BETWEEN &#039;6/1/2003&#039; AND &#039;6/9/2003&#039;<BR><BR>See it??? You simply count the NUMBER of "B" flags in the range!<BR><BR>TOO SIMPLE! My apologies for coming up with the other silly scheme!<BR><BR>

#### Posting Permissions

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