CODEWITHSUNDEEP
sqldaysbank
user7231191
user7231191

Reputation:

How to exclude bank holidays from working days calculation?

I have been able to show working days excluding weekdays. But now i need to know how to also exclude bank holiday. Please help?

This is my current code: 

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2016/01/01'
SET @EndDate = '2017/01/01'

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)
   DECLARE @TotalDays INT,@WorkDays INT
   SET @TotalDays = (DATEDIFF(dd, @StartDate, @EndDate) +1)
  print @TotalDays

Upvotes: 1

Views: 1134

Answers (1)

someguy76
someguy76

Reputation: 425

Try this out, i think it should work

SELECT COUNT(1)
    FROM  bankholidays
    WHERE (@Region = 1 and Column1 BETWEEN @StartDate AND @EndDate)
    or (@Region = 2 and Column2 BETWEEN @StartDate AND @EndDate
    or (@Region not in (1,2) and Column3 BETWEEN @StartDate AND @EndDate

Upvotes: 1

Related Questions