SQL to work out if there is 5 work days in a week or 4 work days

Hello Experts Exchange
I need a T-SQL script to workout if there is 5 or 4 days in a given week, so I need to be able to workout if there is a bank holiday in a week and set it to only 4 work days for that week.

Is there a way to do this?  I am based in the UK so will need UK bank holidays.

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can only do that with a table where you'll store the holidays. The GETDATE() only returns the date and weekdate but not if it's an holiday or not.
0
Éric MoreauSenior .Net ConsultantCommented:
holidays are not built into SQL Server.

Here's a user defined function that does what you need. You will have to create tblHolidays and populate it with all the holidays you want excluded.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int 
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours)
between two dates.
*/
DECLARE 
@Days int
,@WeekDays int
,@Holidays int
,@Hours int


SELECT @Hours = DATEDIFF(Hour,@Start,@End)
WHILE (DATEPART(WeekDay,@Start)-1) % 6 = 0
BEGIN
SELECT @Start = DATEADD(Day,1,@Start)
SELECT @Hours = @Hours - 24
END
WHILE (DATEPART(WeekDay,@End)-1) % 6 = 0
BEGIN
SELECT @End = DATEADD(Day,1,@End)
SELECT @Hours = @Hours - 24
END

SELECT @WeekDays = @Hours -ABS(DATEDIFF(Week,@End,@Start) * 48)


SELECT @Holidays = COUNT(*) FROM tblHolidays WHERE (HolidayDate BETWEEN @Start AND @End) 
AND DATEPART(Weekday,HolidayDate)-1 % 6 <> 0 *24

SELECT @Hours = @WeekDays - @Holidays
RETURN(@Hours)

END

Open in new window

0
SQLSearcherAuthor Commented:
Hello Eric
How do I use your function?  How should the dates be stored in the holidays table?

Regards

SQLSearcher
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Éric MoreauSenior .Net ConsultantCommented:
here is a full sample:

--Step 1. Create a Holiday table
IF OBJECT_ID('dbo.tblHolidays','U') IS NOT NULL
	DROP TABLE  dbo.tblHolidays

CREATE TABLE tblHolidays (
	Holiday DATETIME NOT NULL PRIMARY KEY CLUSTERED,
	Description VARCHAR(50)
)
GO 

--Step 2. Fill with some values
INSERT INTO dbo.tblHolidays ( Holiday, Description )
VALUES  ('2015-04-01', 'April Fool'),
		('2015-04-03', 'Good Friday'),
		('2015-04-05', 'Easter') ,
		('2015-12-25', 'Christmas')
GO


--Step 3. Query business days
DECLARE @date1 DATETIME = '2015-03-30'
;
WITH CTEDates AS (
	SELECT @date1 AS dt

	UNION ALL

	SELECT DATEADD(dd, 1, dt)
	FROM CTEDates s
	WHERE DATEADD(dd, 1, dt) <= @date1 + 4
)

SELECT * FROM CTEDates WHERE NOT EXISTS (SELECT * FROM dbo.tblHolidays WHERE Holiday = dt)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
SQLSearcher, you have it working now?
0
SQLSearcherAuthor Commented:
Hello Eric
Is there a quick way to put all weekend dates into the holidays table?

Regards

SQLSearcher
0
Éric MoreauSenior .Net ConsultantCommented:
you could do something like this:
DECLARE @startdate DATETIME = '2015-01-01'
DECLARE @enddate DATETIME = '2015-12-31'

DECLARE @dates TABLE ([date] DATETIME)

WHILE @startdate <= @enddate
BEGIN
	IF DATEPART(WEEKDAY, @startdate) = 1 OR DATEPART(WEEKDAY, @startdate) = 7
	BEGIN 
		INSERT INTO @dates ( date )
		VALUES  ( @startdate)
	END 
	SET @startdate = @startdate+1
END

SELECT * FROM @dates

Open in new window


Instead of just selecting from @dates, you could do
"insert into tbl_holidays(...yourfield...) select [date] from @dates"

Open in new window

0
SQLSearcherAuthor Commented:
Hello Eric
The SQL Code you have given me sets the weekend to be a Sunday and a Monday, how do I get the code to work on a Saturday and Sunday dates?

Regards

SQLSearcher
0
Éric MoreauSenior .Net ConsultantCommented:
Your setting for the first day of the week is surely different than mine. I have Sunday (7) as the first day.

To find it out, try this:
SELECT @@DATEFIRST, DATEPART(dw, GETDATE()), DATEPART(WEEKDAY, GETDATE()) 

Open in new window


This returns me 7 3 3.

You have 2 options:
1- change your first day of week but this might temper other process you have:
SET DATEFIRST 7

Open in new window


For more info on DateFirst, check https://msdn.microsoft.com/en-us/library/ms181598.aspx

2- Change the day numbers in my query, instead of :
IF DATEPART(WEEKDAY, @startdate) = 1 OR DATEPART(WEEKDAY, @startdate) = 7

Open in new window

it will surely be
IF DATEPART(WEEKDAY, @startdate) = 6 OR DATEPART(WEEKDAY, @startdate) = 7

Open in new window

0
SQLSearcherAuthor Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.