Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create a function to update workingdays column Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 8:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:15 AM
Points: 56, Visits: 240
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,
[Q] [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


  Post Attachments 
Capture.JPG (3 views, 95.47 KB)
looklikethis.JPG (5 views, 109.71 KB)
fn_WhatDate.txt (5 views, 2.44 KB)
Post #1464688
Posted Wednesday, June 19, 2013 4:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1465048
Posted Wednesday, June 19, 2013 10:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:15 AM
Points: 56, Visits: 240
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
Post #1465281
Posted Wednesday, June 19, 2013 10:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1465289
Posted Wednesday, June 19, 2013 12:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:15 AM
Points: 56, Visits: 240
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
Post #1465319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse