SQL to calculate number of working days (inc Sat)

Question

Post reply

SQL to calculate number of working days (inc Sat)

tim.mathews2

SSC Rookie

Points: 28
More actions
February 19, 2009 at 5:22 am

#131134

Folks,

I've been racking my brains to find a solution to this. Here, Saturdays are counted as working days so maybe you can help me construct some SQL to calculate the number of days between two dates EXCLUDING Sundays. The following *almost* works but not quite...

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2008/10/01'

SET @EndDate = '2008/10/31'

SELECT

(DATEDIFF(dd, @StartDate, @EndDate) + 1)

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

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Any help gratefully accepted.

Thanks,

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Gail Shaw

SSC Guru

Points: 1004494

February 19, 2009 at 6:20 am

#946599

What number do you want back for the dates given?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

The query is reducing 2 days for a week from total days. Since, Saturday is a business day for you, it should be reduced by just 1 day. i.e.



DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2008/10/01'

SET @EndDate = '2008/10/31'

SELECT

   (DATEDIFF(dd, @StartDate, @EndDate) + 1)

  -(DATEDIFF(wk, @StartDate, @EndDate) * 1)

  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

--Ramesh

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

andrewd.smith

SSCrazy Eights

Points: 8444

February 19, 2009 at 6:47 am

#946619

What about national holidays?

If you need to take account of these then a Calendar table where working days can be identified would be the way to go.

tim.mathews2 SSC Rookie Points: 28 More actions · Answer 4

tim.mathews2

SSC Rookie

Points: 28

February 19, 2009 at 6:54 am

#946625

Thanks for all your help folks. I'm going to do what I should have done a long time ago and ask for a working days calendar to be added with a 'working_days_num' field.

Kind regards,

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 5

For future reference, a very thorough discussion of this topic can be found in the discussion of the following article. A variety of solutions, performance considerations, and other issues were covered in the melee that ensued.

http://qa.sqlservercentral.com/articles/T-SQL/65423/

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

neela_shashi Grasshopper Points: 11 More actions · Answer 6

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2009/02/08'

SET @EndDate = '2009/02/24'

SELECT

(DATEDIFF(dd, @StartDate, @EndDate) + 1)

-(DATEDIFF(wk, @StartDate, @EndDate) )

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'and DATENAME(dw, @EndDate) != 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) = 'Sunday' THEN 0 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' and DATENAME(dw, @EndDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) != 'Sunday' THEN 0 ELSE 0 END)

hey , try this out..

nigel. SSChampion Points: 11636 More actions · Answer 7

I don't see the pont in these two in the query above since they will both always evaluate to 0

CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) = 'Sunday' THEN 0 ELSE 0 END

CASE WHEN DATENAME(dw, @StartDate) != 'Sunday' and DATENAME(dw, @EndDate) != 'Sunday' THEN 0 ELSE 0 END

Nigel
How to post questions

Kumar Katakam-462492 SSC Enthusiast Points: 151 More actions · Answer 8

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2008/03/01'

SET @EndDate = '2008/03/31'

set DATEFIRST 7

select DATEPART(dw, @StartDate) as firstday, (datediff(dd, @StartDate, @EndDate)/7)

select

case when DATEPART(dw, @StartDate) > 6 then

datediff(dd, @StartDate, @EndDate) - ((datediff(dd, @StartDate, @EndDate)/7) + 1 )

else

datediff(dd, @StartDate, @EndDate) - (datediff(dd, @StartDate, @EndDate)/7)

end

tim.mathews2 SSC Rookie Points: 28 More actions · Answer 9

Thanks for all the posts folks. I think the definitive answer is as follows:-

select datediff(dd,start_date, getdate())-(( floor((datepart(dw,start_date)+datediff(dd,start_date ,getdate())) /7 )*1))+ case datepart(dw,getdate()) when 7 then 1 else 0 end

Seems to work fine, but of course no bank hols are included, thus strengthening the argument for a function based on a working day calendar.

Thanks again.

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 10

My one comment about your definitive answer is that it is limited in its application. You're definitely smart to look into implementing a calendar table. Calendar tables don't have to list working days. Most contain only weekend days and holidays. They can be prepopulated easily and the queries against them are fairly fast and straighforward. They are also flexible in that the code to just omit Sundays will be similar to the code to omit Saturdays, Sundays, and Holidays etc.

I posted the link to an article that started an excellent discussion of these kinds of problems earlier. The link below is to the start of the discussion, which is worth walking through for the code examples and variety of issues covered. It was definitely an education for me, anyway. Best of luck 🙂

http://qa.sqlservercentral.com/Forums/Topic649517-1472-10.aspx

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Nawar.Tabaa Ten Centuries Points: 1352 More actions · Answer 11

CREATE FUNCTION [dbo].[GetWorkingDays]

( @StartDate datetime,

@EndDate datetime )

RETURNS INT

AS

BEGIN

DECLARE @varDate datetime --placeholder for c

DECLARE @wdCount int

SELECT @wdCount = 0

SELECT @varDate = @StartDate

WHILE (@varDate Between @StartDate AND @EndDate)

BEGIN

IF (DATEPART(dw, @varDate) 6

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 12

Nawar, although your function may get the desired result, using a while loop is going to run much slower than a set based solution. If you will read up a bit on calendar tables, you will find that they will run much more efficiently over the long term. Thank you for contributing! 🙂

Regards,

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jack Corbett SSC Guru Points: 184388 More actions · Answer 13

Here's a link I found on Jeremiah Peschka's blog[/url] that creates and populates a calendar table

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question