Workday calculations

Question

Post reply

Workday calculations

Animal Magic

SSChampion

Points: 13964
More actions
January 29, 2007 at 12:42 pm

#171947

Hi All,
Firstly sorry if i have posted in the wrong forum here, i'm new to this place so go easy!
I have a problem whereby i need to work out a date given the starting date and the number of workdays (5 day working week - monday to friday).
Searching around i have found out how to work out the number of workdays between two dates, but am struggling to find any help on a solution to my problem. If anyone could point me to an article they know of i would be extremely grateful!
John

Viewing 15 posts - 1 through 15 (of 17 total)

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

dclark SSC Eights! Points: 843 More actions · Answer 1

I don't understand what the problem you are trying to solve is.

Please explain and give examples.

Animal Magic SSChampion Points: 13964 More actions · Answer 2

I am working on a critical path project where i know the date i should be starting a step. Knowing that this step will take 10 working days i want to know the finish date of the step.

So if the first step starts on the 1st Jan 2007 and takes 10 working days, using dateadd (-1) would give me 10th Jan 2007, although the date i am looking to calculate would give me 12th Jan 2007 because of the two weekend days on the 6th and 7th.

Does this make any more sense?

dclark SSC Eights! Points: 843 More actions · Answer 3

Try this function:

drop function fn_Last_Workday
go
create function fn_Last_Workday
(@start_day datetime,
@work_days int)
returns datetime
as
begin

declare @end_day datetime
declare @prior_workday datetime
declare @weeks  int
declare @remaining_days int

-- Return null if input parameters are invalid
if @start_day is null or @work_days is null or @work_days < 1
begin return null end

-- Find first workday on or before the start date
set @prior_workday =
 case datediff(dd,'17530101',@start_day)%7
 when 6
 then dateadd(dd,-2,@start_day)
 when 0
 then dateadd(dd,-3,@start_day)
 else dateadd(dd,-1,@start_day)
 end

-- Find number of whole weeks
select @weeks = @work_days/5
-- Find the remaining days after subtracting whole weeks
set @remaining_days = @work_days-(@weeks*5)
-- Find the last workday of whole weeks
set @end_day = dateadd(dd,(@weeks*7)+@remaining_days,@prior_workday)

set @end_day =
 -- Add 2 days if adding remaining days includes a weekend
 case
 when datediff(dd,'17530101',@end_day)%7 in (5,6)
 then dateadd(dd,2,@end_day)
 when datediff(dd,'17530101',@end_day)%7 = 0 and @remaining_days > 0
 then dateadd(dd,2,@end_day)
 when datediff(dd,'17530101',@end_day)%7 = 1 and @remaining_days > 1
 then dateadd(dd,2,@end_day)
 when datediff(dd,'17530101',@end_day)%7 = 2 and @remaining_days > 2
 then dateadd(dd,2,@end_day)
 when datediff(dd,'17530101',@end_day)%7 = 3 and @remaining_days > 3
 then dateadd(dd,2,@end_day)
 else @end_day
 end

return @end_day

end
go

select Last_Date=dbo.fn_Last_Workday('20010101',10)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

What about holidays?

Take a look at this article

http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

declare @startdate datetime,

@enddate datetime,

@workdays smallint

select @startdate = '20070101',

@workdays = 10

select @enddate = dateadd(day, 3 + 1.4 * @workdays, @startdate)

select seqdate

from dbo.fnSeqDates(@startdate, @enddate)

where datepart(dw, seqdate) in (2, 3, 4, 5, 6)

and row_number() over (order by seqdate) = @workdays

N 56°04'39.16"
E 12°55'05.25"

Animal Magic SSChampion Points: 13964 More actions · Answer 5

Thanks very much dclark! works a treat.

Peter - Many thanks for your reply as well, although at the moment we are not bothered about holidays as the company it is for generally work bank hols.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

If you have a number of these kinds of calculations to make, you may want to make a calendar table. Many different examples on SQLServerCentral... do a search. Well worth the effort.

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

If you want a date table, you can use my own personal favorite.

Date Table Function F_TABLE_DATE:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Jeremy-412101 Valued Member Points: 56 More actions · Answer 8

I am trying to accomplish something similar to the original post however I would like to use a business day calendar. I need to take a date like 2/21/2007 and add it against number -5 to return date 2/14/2007, or add against 4 to return day 2/27/2007. I have succesfully created the calendar table and the structure of it is below. I am able to calculate the number of business days between two days using the calendar but I am stuck with the above task. Any help would be greatly appreciated.

Date isWeekday isWorkday

2007-02-21 00:00:00 1 1

2007-02-22 00:00:00 1 1

2007-02-23 00:00:00 1 1

2007-02-24 00:00:00 0 0

2007-02-25 00:00:00 0 0

2007-02-26 00:00:00 1 1

2007-02-27 00:00:00 1 1

2007-02-28 00:00:00 1 1

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

I see you're online right now, Jeremy... gimme a couple minutes more and I'll show you one way to do this...

--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)

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

Lot's of folks will do a calculation like this...

--===== A way without the WorkDayCountColumn

SELECT c.Date

FROM #Calendar c

WHERE

c.IsWorkDay = 1

AND 4+1 = (

SELECT COUNT(*)

FROM #Calendar c2

WHERE c2.Date >= '20070221'

AND c2.Date <= c.Date

AND c2.IsWorkDay=1

)

... where the "4" is the number of business days you want to look ahead. If you want to look back, you're out of luck unless you write another chunk of logic. And, although very small, the method above uses a triangular self join in a correlated sub-query... my experience has been that they don't perform as well as direct set-based logic because of the number of internal rows they generate to solve the problem. The formula for the number of rows touched by a triangular join is (N²+N)/2... when looking for 4 (really, 5 in this case, 4+1), then the triangular join has to internally generate (5²+5)/2 or 15 rows... why the hell would I want to burden the CPU with looking at 15 rows when I really need to look at just 2? Think of the performance gain if you could look at only two rows instead of 15 a couple of thousand times... and that's just for 5 days!

As you can tell, I'm pretty sour on triangular joins and correlated sub-queries... there are a few places (like, finding ranges of missing IDs) where they can be VERY fast, but in most cases, their performance is worse than a cursor

Soooooooooo.... with all that in mind, when I create a calendar table for a client, I usually add a column called "WorkDayCount" which is nothing more than a running count of workdays starting at the earliest date in the calendar table... here's a very small hard-coded example...

--===== Create a small example Calendar table

-- Do notice the primary key constraint

CREATE TABLE #Calendar

(

DATE DATETIME,

IsWeekday INT,

IsWorkday INT,

WorkDayCount INT,

CONSTRAINT PK_Calendar_Date

PRIMARY KEY CLUSTERED (Date)

WITH FILLFACTOR = 100

)

--===== Populate it with a small sample of data (all of Feb 2007)

INSERT INTO #Calendar (DATE,IsWeekday,IsWorkday)

SELECT '2007-02-01 00:00:00',1,1 UNION ALL

SELECT '2007-02-02 00:00:00',1,1 UNION ALL

SELECT '2007-02-03 00:00:00',0,0 UNION ALL

SELECT '2007-02-04 00:00:00',0,0 UNION ALL

SELECT '2007-02-05 00:00:00',1,1 UNION ALL

SELECT '2007-02-06 00:00:00',1,1 UNION ALL

SELECT '2007-02-07 00:00:00',1,1 UNION ALL

SELECT '2007-02-08 00:00:00',1,1 UNION ALL

SELECT '2007-02-09 00:00:00',1,1 UNION ALL

SELECT '2007-02-10 00:00:00',0,0 UNION ALL

SELECT '2007-02-11 00:00:00',0,0 UNION ALL

SELECT '2007-02-12 00:00:00',1,1 UNION ALL

SELECT '2007-02-13 00:00:00',1,1 UNION ALL

SELECT '2007-02-14 00:00:00',1,1 UNION ALL

SELECT '2007-02-15 00:00:00',1,1 UNION ALL

SELECT '2007-02-16 00:00:00',1,1 UNION ALL

SELECT '2007-02-17 00:00:00',0,0 UNION ALL

SELECT '2007-02-18 00:00:00',0,0 UNION ALL

SELECT '2007-02-19 00:00:00',1,1 UNION ALL

SELECT '2007-02-20 00:00:00',1,1 UNION ALL

SELECT '2007-02-21 00:00:00',1,1 UNION ALL

SELECT '2007-02-22 00:00:00',1,1 UNION ALL

SELECT '2007-02-23 00:00:00',1,1 UNION ALL

SELECT '2007-02-24 00:00:00',0,0 UNION ALL

SELECT '2007-02-25 00:00:00',0,0 UNION ALL

SELECT '2007-02-26 00:00:00',1,1 UNION ALL

SELECT '2007-02-27 00:00:00',1,1 UNION ALL

SELECT '2007-02-28 00:00:00',1,1

Notice that the WorkDayCount column hasn't been populated, yet... and most folks would do some form of triangular join or a cursor or a while loop to populate that column. Me? I use a proprietary form of the UPDATE statement to do it and it's lightning quick...

--===== Now, populate the WorkDayCount column with a running count

-- using a little SQL Server prestidigitation 😉

-- Do notice the WITH (INDEX()) notation... it's important

DECLARE @CurrentCount INT

SET @CurrentCount = 0

UPDATE #Calendar

SET @CurrentCount = WorkDayCount = CASE

WHEN IsWorkDay = 1

THEN @CurrentCount + 1

ELSE @CurrentCount

END

FROM #Calendar WITH (INDEX(PK_Calendar_Date))

--===== Just verify what we have in the calendar table

SELECT * FROM #Calendar

From then on, the kinds of calculations you're asking for become very simple and very fast... recommend you create a function for it but here's an almost hard-coded example to solve the two problems you posted... and more...

--===== Setup a couple of variables to simulate what could be done in a function...

DECLARE @GivenDate DATETIME

DECLARE @NumberOfDays INT

SET @GivenDate = '20070221'

--===== Solve the first problem: Find business day 5 days BEFORE given date

SET @NumberOfDays = -5

SELECT DATE

FROM #Calendar

WHERE WorkDayCount = (SELECT WorkdayCount FROM #Calendar WHERE <A href="mailtoate=@GivenDate)+@NumberOfDays">Date=@GivenDate)+@NumberOfDays

AND IsWorkDay = 1

--===== Solve the second problem: Find business day 4 days AFTER given date

-- (Notice that the SELECT is exactly the same so we could turn this into a function)

SET @NumberOfDays = 4

SELECT DATE

FROM #Calendar

WHERE WorkDayCount = (SELECT WorkdayCount FROM #Calendar WHERE <A href="mailtoate=@GivenDate)+@NumberOfDays">Date=@GivenDate)+@NumberOfDays

AND IsWorkDay = 1

--===== Solve an additional problem: Find the next business day after given date

-- (Notice that the SELECT is exactly the same so we could turn this into a function)

SET @GivenDate = '20070217' --A SATURDAY

SET @NumberOfDays = 1

SELECT DATE

FROM #Calendar

WHERE WorkDayCount = (SELECT WorkdayCount FROM #Calendar WHERE <A href="mailtoate=@GivenDate)+@NumberOfDays">Date=@GivenDate)+@NumberOfDays

AND IsWorkDay = 1

--===== Solve an additional problem: Find the next business day after given date

-- (Notice that the SELECT is exactly the same so we could turn this into a function)

SET @GivenDate = '20070216' --A FRIDAY

SET @NumberOfDays = 1

SELECT DATE

FROM #Calendar

WHERE WorkDayCount = (SELECT WorkdayCount FROM #Calendar WHERE <A href="mailtoate=@GivenDate)+@NumberOfDays">Date=@GivenDate)+@NumberOfDays

AND IsWorkDay = 1

--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)

Jeremy-412101 Valued Member Points: 56 More actions · Answer 11

Hello Jeff,

Thank you for this, it works great.

I was hoping you could point me in the right direction to using this in my query. I have a Orders table that contains the date factor and is joined to transit time table that contains the number factor(-1,-5,4,3 etc). How can I pass this against the query the uses the calendar table to calculate the dates?

Jeremy

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

Like I said... make a function using the SELECT examples I gave... then just do a joined select and use the function passing the date from one table and the transit time from the other...

Since you haven't given me any info about how your two tables relate, I can be much more of a help than that.

--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)

Jeremy-412101 Valued Member Points: 56 More actions · Answer 13

Jeff,

I have the fuction portion written:

create Function dbo.CalcDate

(

@GivenDate DATETIME,

@NumberOfDays INT

)

Returns table

as

Return (SELECT dt

FROM dbo.workcalendar

WHERE WorkDayCount = (SELECT WorkdayCount FROM dbo.workcalendar WHERE dt=@GivenDate)+@NumberOfDays

AND IsWorkDay = 1)

My tables are Order, TransitTime, and WorkCalendar

Below is the query I am using:

SELECT order.Order_Num, order.CustomerNumber, order.ItemNum, order.RequireDate, Order.State, TransitTime.PrepTime, WorkCalendar.date

FROM Order LEFT OUTER JOIN

WorkCalendar ON order.RequireDate = WorkCalendar.date LEFT OUTER JOIN

TransitTime ON Order.State = TransitTime.State AND order.CustomerNumber = TransitTime.CustomerNumber

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

Don't need the WorkCalendar join anymore... you have the function...

SELECT o.Order_Num,

o.CustomerNumber,

o.ItemNum,

o.RequireDate,

o.State,

tt.PrepTime,

dbo.CalcDate(o.RequireDate,-tt.PrepTime) AS RequiredShipDate

FROM dbo.Order o

LEFT OUTER JOIN dbo.TransitTime tt

ON o.State = tt.State

AND o.CustomerNumber = tt.CustomerNumber

--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)