create a function to update workingdays column

Question

create a function to update workingdays column

gissah

SSCommitted

Points: 1815
More actions
June 18, 2013 at 8:24 am

#276469

I do have a calendar table I want to update the businessday/workingdays column to be represented as (1-22) workingdays in a month. below is my table and I have also attached some few codes I am working with
CREATE TABLE [dbo].[Calendar](
[dt] [smalldatetime] NOT NULL,
[isWeekday] [bit] NULL,
[isHoliday] [bit] NULL,
[Y] [smallint] NULL,
[FY] [smallint] NULL,
[tinyint] NULL,
[M] [tinyint] NULL,
[D] [tinyint] NULL,
[DW] [tinyint] NULL,
[monthname] [varchar](9) NULL,
[dayname] [varchar](9) NULL,
[W] [tinyint] NULL,
[UTCOffset] [tinyint] NULL,
[BusinessDay] [int] NULL,
[HolidayDescription] [varchar](32) NULL,
PRIMARY KEY CLUSTERED
(
[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Create FUNCTION [dbo].[ISOWeek]
(
@dt SMALLDATETIME
)
RETURNS TINYINT
AS
BEGIN
DECLARE @ISOweek TINYINT
SET @ISOweek = DATEPART(WEEK,@dt)+1
-DATEPART(WEEK,RTRIM(YEAR(@dt))+'0104')
IF @ISOweek = 0
BEGIN
SET @ISOweek = dbo.ISOweek
(
RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24+DAY(@dt))
) + 1
END
IF MONTH(@dt) = 12 AND DAY(@dt)-DATEPART(DW,@dt) >= 28
BEGIN
SET @ISOweek=1
END
RETURN(@ISOweek)
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

I just read your post and have no idea what you are asking for - or were you just informing people of what you are working on at the moment?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

gissah SSCommitted Points: 1815 More actions · Answer 2

Thank you sir, All I want to do is just populate the [BusinessDay] column on my table sequentially(1234...) if a day qualify as a business day or workday(MTWTF). Thanks

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

gissah (6/19/2013)
Thank you sir, All I want to do is just populate the [BusinessDay] column on my table sequentially(1234...) if a day qualify as a business day or workday(MTWTF). Thanks

What is day 1 - Jan 1st? Do you restart numbering every year? Are you only concerned with Monday-Friday - what about statutory holidays?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

gissah SSCommitted Points: 1815 More actions · Answer 4

day 1 will be 2 January since 1 is a holiday. I have already updated the table with all US holidays and my company holidays.

Thanks