How to use DATEDIFF with code that only counts weekdays M-F?? 1

[lennym]

I am using an SQL version7 database that contains tables with date columns. I would like to add a column to a table that calculates in days, the difference between two other columns, taking into account that I only want to count days where its Monday to Friday. Also, I would like to take into account Holidays and remove them from the calculation. Should I have a separate table for the Holidays that I can update for each year? Any assistance is appreciated.<br>

 

[jnicho02]

If you want to exclude holidays as well as weekends you probably need to create a table of dates and whether they are valid.<br>
The following script (adapted from one in the <A HREF="

http://www.Swynk.com"

TARGET="_new">

http://www.Swynk.com</A>

library) has a column called countable_day that i've set to 1 for a working day and 0 for a weekend. Update the table setting it to 0 for holidays. You can then do statements summing the countable_days like:<br>
<br>
<FONT FACE=monospace>select sum(countable_day) from time_dimension<br>
where the_date &gt;= &quot;1999-03-06&quot; and the_date &lt; &quot;1999-03-15&quot;</font><br>
<br>
to create the table:<br>
<FONT FACE=monospace><br>
CREATE TABLE [dbo].[time_dimension] (<br>
&nbsp;&nbsp;&nbsp;&nbsp;[time_id] [int] IDENTITY (1, 1) NOT NULL<br>
&nbsp;&nbsp;&nbsp;&nbsp;[the_date] [datetime] NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[the_day] [nvarchar] (15) NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[the_month] [nvarchar] (15) NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[the_year] [smallint] NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[day_of_month] [smallint] NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[week_of_year] [smallint] NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[month_of_year] [smallint] NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[quarter] [nvarchar] (2) NULL ,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[fiscal_period] [nvarchar] (20) NULL,<br>
&nbsp;&nbsp;&nbsp;&nbsp;[countable_day] [int] NULL<br>
&nbsp;&nbsp;&nbsp;&nbsp ON [PRIMARY]<br>
<br>
DECLARE @WeekString varchar(12),<br>
&nbsp;&nbsp;&nbsp;&nbsp;@dDate SMALLDATETIME,<br>
&nbsp;&nbsp;&nbsp;&nbsp;@sMonth varchar(20),<br>
&nbsp;&nbsp;&nbsp;&nbsp;@iYear smallint,<br>
&nbsp;&nbsp;&nbsp;&nbsp;@iDayOfMonth smallint,<br>
&nbsp;&nbsp;&nbsp;&nbsp;@iWeekOfYear smallint,<br>
&nbsp;&nbsp;&nbsp;&nbsp;@iMonthOfYear smallint,<br>
&nbsp;&nbsp;&nbsp;&nbsp;@sQuarter varchar(2),<br>
&nbsp;&nbsp;&nbsp;&nbsp;@sSQL varchar(100),<br>
&nbsp;&nbsp;&nbsp;&nbsp;@adddays int,<br>
&nbsp;&nbsp;&nbsp;&nbsp;@countable_day int<br>
<br>
SELECT @adddays = 1 --Incrementing the days by one<br>
SELECT @dDate = '01/01/1960' --The start date<br>
<br>
WHILE @dDate &lt; '12/31/2001' --End Date<br>
&nbsp;&nbsp;&nbsp;&nbsp;BEGIN<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @WeekString = DATENAME (dw, @dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @sMonth=DATENAME(mm,@dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @iYear= DATENAME (yy, @dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @iDayOfMonth=DATENAME (dd, @dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @iWeekOfYear= DATENAME (week, @dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @iMonthOfYear=DATEPART(month, @dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @sQuarter = 'Q' + CAST(DATENAME (quarter, @dDate)as varchar(1))<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @countable_day = case datepart(weekday, @dDate)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when 1 then 0 <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when 7 then 0 <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else 1 <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INSERT INTO time_dimension(the_date, the_day, the_month, the_year, day_of_month, Week_of_year, month_of_year, quarter, countable_day) <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(@dDate, @WeekString, @sMonth, @iYear, @iDayOfMonth, @iWeekOfYear, @iMonthOfYear, @sQuarter, @countable_day)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT @dDate = @dDate + @adddays<br>
&nbsp;&nbsp;&nbsp;&nbsp;END<br>
GO<br>
</font>