creating working day calculation table.. 1

[lennym]

Some time ago, someone gave me a script to run to create a table called "time_dimension" which I used to calculate elapsed working days, excluding holidays and weekends. The table produced a value of 1 for regular days and 0 for holidays and weekends, by simply using the sum count function, I could count the number of days between two dates. Unfortunately I have lost that original information and need to re-create the table with a later end date. Does anyone remember seeing that post or have have any suggestions???

Thanks in advance...

Lennym

 

[Cagliostro]

Do you mean something as DateDiff? John Fill

ivfmd@mail.md

 

[redlam]

is this it?
lennym (TechnicalUser) Mar 15, 2000
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.


Let lennym know
this post was helpful!


Is this post offensive?
If so, Red Flag it!


Check out the FAQ
area for this forum!


jnicho02 (Programmer) Mar 16, 2000
If you want to exclude holidays as well as weekends you probably need to create a table of dates and whether they are valid.
The following script (adapted from one in the

http://www.Swynk.com

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:

select sum(countable_day) from time_dimension
where the_date >= &quot;1999-03-06&quot; and the_date < &quot;1999-03-15&quot;

to create the table:

CREATE TABLE [dbo].[time_dimension] (
[time_id] [int] IDENTITY (1, 1) NOT NULL
[the_date] [datetime] NULL ,
[the_day] [nvarchar] (15) NULL ,
[the_month] [nvarchar] (15) NULL ,
[the_year] [smallint] NULL ,
[day_of_month] [smallint] NULL ,
[week_of_year] [smallint] NULL ,
[month_of_year] [smallint] NULL ,
[quarter] [nvarchar] (2) NULL ,
[fiscal_period] [nvarchar] (20) NULL,
[countable_day] [int] NULL
) ON [PRIMARY]

DECLARE @WeekString varchar(12),
@dDate SMALLDATETIME,
@sMonth varchar(20),
@iYear smallint,
@iDayOfMonth smallint,
@iWeekOfYear smallint,
@iMonthOfYear smallint,
@sQuarter varchar(2),
@sSQL varchar(100),
@adddays int,
@countable_day int

SELECT @adddays = 1 --Incrementing the days by one
SELECT @dDate = '01/01/1960' --The start date

WHILE @dDate < '12/31/2001' --End Date
BEGIN

SELECT @WeekString = DATENAME (dw, @dDate)
SELECT @sMonth=DATENAME(mm,@dDate)
SELECT @iYear= DATENAME (yy, @dDate)
SELECT @iDayOfMonth=DATENAME (dd, @dDate)
SELECT @iWeekOfYear= DATENAME (week, @dDate)
SELECT @iMonthOfYear=DATEPART(month, @dDate)
SELECT @sQuarter = 'Q' + CAST(DATENAME (quarter, @dDate)as varchar(1))
SELECT @countable_day = case datepart(weekday, @dDate)
when 1 then 0
when 7 then 0
else 1
end

INSERT INTO time_dimension(the_date, the_day, the_month, the_year, day_of_month, Week_of_year, month_of_year, quarter, countable_day)
VALUES
(@dDate, @WeekString, @sMonth, @iYear, @iDayOfMonth, @iWeekOfYear, @iMonthOfYear, @sQuarter, @countable_day)
SELECT @dDate = @dDate + @adddays
END
GO

 

[lennym]

Thanks very much...you guys are fast. This is the one I was looking for...

- LennyM