1

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
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Tag the dbms you're using. (That code is far from ANSI SQL.) – jarlh Nov 30 '16 at 14:01
  • 2
    Have you considered creating a date table in which you can mark these as non working days? https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ – Rich Benner Nov 30 '16 at 14:02
  • No i havent, can you help me how i can create them and exclude them? –  Nov 30 '16 at 14:03
  • Ok, so the link I gave gives you an idea on how to create a calendar table. You want this to be a proper table in your database. You can create a column that's a bit data type that says whether a day is a working day or not. You can then link to this table and sum the 1's between two dates to work out how many working days between them. – Rich Benner Nov 30 '16 at 14:07
  • With regards to what the holidays are, I've no idea what country you're in. But you want to get a list (uk would be something like https://www.gov.uk/bank-holidays) and update your data to show that these aren't working days. – Rich Benner Nov 30 '16 at 14:08
  • Ok i see the table setup, but can you give me an example code to understand? –  Nov 30 '16 at 14:15
  • @UnknownDev read that link thoroughly, it contains all of the code you'll need to create the date table. – Rich Benner Nov 30 '16 at 14:28
  • It isnt showing me how to use the tables made to be ignored in the code i made above? Help? –  Dec 01 '16 at 09:46

1 Answers1

1

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
someguy76
  • 415
  • 4
  • 22