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 @ 5:15 PM
Points: 5,245, Visits: 12,163
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.
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 @ 5:15 PM
Points: 5,245, Visits: 12,163
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.
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