Calculating Work Days

Question

Post reply

Calculating Work Days

Viewing 15 posts - 136 through 150 (of 156 total)

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

OceanDiscovery Old Hand Points: 335 More actions · Answer 1

I've added both the UDF's udfHolidayDays, and addBusinessDays, as well as the table tblSysHolidays

The table has three dates.

If I query the udfHolidayDays as

SELECT [dbo].[udfHolidayDays] (



   '2009-Nov-17'

  ,'2009-Dec-31')

SELECT [SysHolidayID]

      ,[HolidayDate]

      ,[Description]

FROM [dbo].[tblSysHolidays]

I get

-----------

3

(1 row(s) affected)

SysHolidayID HolidayDate Description

------------ ----------------------- ----------------------------------------

1 2009-12-02 00:00:00.000 Test December 2nd

2 2009-12-25 00:00:00.000 Christmas Day

3 2009-12-26 00:00:00.000 Boxing Day

(3 row(s) affected)

However, when I run

SELECT [dbo].[addBusinessDays] ('20091117', 12)

I get

-----------------------

2009-11-29 00:00:00.000

(1 row(s) affected)

But by my reckoning it should be December 4th

What have I done wrong??

OceanDiscovery Old Hand Points: 335 More actions · Answer 2

I've actually found this article http://qa.sqlservercentral.com/articles/T-SQL/65423/ to be really helpful as well.

I added the criteria

in (SELECT HolidayDate FROM DbHolidays WHERE Location = @LocationId)))

and

@LocationId int

to that on this page.

And this allows me to have customised dates for various Locations included in the holiday calculation.

In this way, if one region has a 'region only' public holiday, this can be included in the calculation.

Two really good articles that have helped me immensely

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Sorry I didn't have an answer for your previous question, Mac. I sure do appreciate you posting the URL that sorted it for you, though. Thanks for the great feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

scottsimpson SSC Journeyman Points: 75 More actions · Answer 4

I agree with the principle of avoiding cursors and row based operations. However I'm trying to do this same type of calculation, but given a start date, add a number of weekdays and return the date.

I don't see a clean way of doing this without doing a while loop counting the days as I go.

Anyone got any idea of how I could avoid this

function would be something like

GetEndDate(@StartDate datetime, @Weekdays int, @EndDate datetime OUT)

subscriptions-524733 SSC Rookie Points: 28 More actions · Answer 5

Robert Stewart (1/5/2005)

Actually, if you are willing to use a "Date Table," make it a date dimension table. Do all of the date calculations that can be done to a date ahead of time, calculate the day of week, week of year, day of month, month name and number, etc. You can add a column for something like "HolidayFlag" and another one for "WeekendFlag." Now, all you have to do is go to this table with the beginning and ending dates and count the number of No's in the weekend flag and subtract the number of Yes's in the Holiday flag for the date range you want to check.

I agree with R.Stewart. I've used this type of approach in the past very successfully, especially in ETLs or DW applications.

nigel. SSChampion Points: 11636 More actions · Answer 6

scottsimpson (2/4/2010)

...

I don't see a clean way of doing this without doing a while loop counting the days as I go.

Yes, use the DATEADD function, see: http://msdn.microsoft.com/en-us/library/ms186819.aspx

Edit: Sorry, misread your question. Missed the important word 'weekdays'.

As suggested above use a Date Table or Calendar table.

Nigel
How to post questions

scottsimpson SSC Journeyman Points: 75 More actions · Answer 7

That doesn't work, it gives the same result if you add a day or weekday.These two sets of statements return the same result.

select dateadd(weekday,1, getdate())

select dateadd(weekday,2, getdate())

select dateadd(weekday,3, getdate())

select dateadd(weekday,4, getdate())

select dateadd(weekday,5, getdate())

select dateadd(weekday,6, getdate())

select dateadd(weekday,7, getdate())

select dateadd(weekday,8, getdate())

select dateadd(day,1, getdate())

select dateadd(day,2, getdate())

select dateadd(day,3, getdate())

select dateadd(day,4, getdate())

select dateadd(day,5, getdate())

select dateadd(day,6, getdate())

select dateadd(day,7, getdate())

select dateadd(day,8, getdate())

scottsimpson SSC Journeyman Points: 75 More actions · Answer 8

Saw your update after you posted mine. The problem with populating a table is you don't know how many days to populate it with. It seems you would have to populate an arbitrary number of days that will guarantee to be at least enough to cover weekends, then count.

It seems like its probably more efficient to just loop through adding days and counting them when its a weekday.

I'm wondering if there is a formula you can use to account for the weekends and just add the days and subtract weekdays. The more I think about it, the more complications I see with the start date and end date spanning weekends though.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

I saw a tight little forumula for this about a year ago... I'm not sure where I put it but I'll try to find it this weekend.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Jeff Moden (2/5/2010)

I saw a tight little forumula for this about a year ago... I'm not sure where I put it but I'll try to find it this weekend.

Ah... I remember where I saw it... Ironically, it was in this very discussion thread!

http://qa.sqlservercentral.com/Forums/Topic153606-203-3.aspx#BM591629

There's also another article for various functions of this nature. One of them is a function to look ahead or look back. Please test them before you use them because I have not.

http://www.mssqlcity.com/Articles/UDF/DateTimeUDF.htm#part_2_4

Obviously, they could use a bit of optimizing but it's betterr than trying to "correct a blank piece of paper". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

rick.myers Newbie Points: 6 More actions · Answer 11

rick.myers

Newbie

Points: 6

February 6, 2012 at 12:54 pm

#1443390

This functions doesn't actually work. Just try Jan 1, 2012 to Jan 31, 2012. Or February 2012.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

rick.myers (2/6/2012)

This functions doesn't actually work. Just try Jan 1, 2012 to Jan 31, 2012. Or February 2012.

Since you tried it, how about sharing what you're getting for results and why you think it's not working.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

chaminda_chandrasekara Valued Member Points: 59 More actions · Answer 13

Great function to get the weekdays. I had a need to calculate hour differenct between two datetimes excluding weekends (exclude 48 hours for each week end) to calculate age of a document to be mailed disregaring holidays (as holidays are not so important compared with complexity it will involve and given the context of the document, according to my team lead)

I managed to change a little bit of your function for my need. Thanks for the idea you gave in your article. saved my day.

/****** Object: UserDefinedFunction [dbo].[fn_HourDiffExcudeWeekend] Script Date: 04/04/2012 03:08:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Description: Calculate the hour gap between two given datetimes

-- excluding weekend 48 hours (from Saturday 00:00 hours to Sunday 24:00 hours)

-- =============================================

CREATE FUNCTION [dbo].[fn_HourDiffExcudeWeekend]

(

@StartDate datetime,

@EndDate datetime

)

RETURNS int

AS

BEGIN

DECLARE @HourGap int

--Temporarily holds @EndDate during date reversal

DECLARE @Swap DATETIME

--===== If the inputs are in the wrong order, reverse them

IF @StartDate > @EndDate

BEGIN

SELECT @Swap = @EndDate,

@EndDate = @StartDate,

@StartDate = @Swap

END

--====== Calculate hour gap excluding weekend 48 hours

SELECT @HourGap =

-- hour difference betwenn the two days

(DATEDIFF(hh, @StartDate, @EndDate))

-- substract 48 hours for each weekend

--The DATEPART(dw,date) does not actually count weeks... It counts the

-- transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts

-- only whole weekends in any given date range.

-(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24)

-- Substract the additional hours added by the @StartDate of Sunday to get the hour diff from starting Next Monday 00:00 Hours

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN (24 - DATEDIFF(hh,CAST(@StartDate as date), @StartDate) ) ELSE 0 END)

-- Substract the additional hours added by the @EndDate of Saturday to get the hour diff to @EndDate 00:00 Hours (Prevoius Friday 24:00 hours)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEDIFF(hh,CAST(@EndDate as date), @EndDate) ELSE 0 END)

-- Add hours from @StartDate 00:00 to the @StartDate time (xx:00 hours), to adjust the efect(substracting 48 more hours) of counting additional 1 weekend in --> -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24)

+(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEDIFF(hh,CAST(@StartDate as date), @StartDate) ELSE 0 END)

-- Add hours from @EndDate time xx:00 hours to @EndDate 24:00 hours to adjust the efect(substracting 48 more hours) of counting additional 1 weekend in --> -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24)

+(CASE WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN (24 -DATEDIFF(hh,CAST(@EndDate as date), @EndDate)) ELSE 0 END)

RETURN @HourGap

END

GO

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Apologies for the delayed response. Thank you for the feedback and for posting the function you built. It might help others having the same problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Xedni SSCertifiable Points: 6948 More actions · Answer 15

Xedni

SSCertifiable

Points: 6948

March 3, 2015 at 7:55 pm

#1781522

I know this is a super old thread, but it was extremely helpful in a problem I had at work. Thanks!!

Executive Junior Cowboy Developer, Esq.[/url]