title: Removing weekends and Bank Holidays (UK) from TSQL SELECT with a function
body: <p>I have a TSQL function that i think(im not strong with SQL syntax) will remove weekends when called:</p>
  
  <pre><code>ALTER FUNCTION dbo.fn_WorkDays (@StartDate AS DATETIME, @EndDate AS DATETIME)
  --Define the output data type.
  RETURNS INT
  AS
  --Calculate the RETURN of the function.
  BEGIN
      RETURN (
       SELECT
          (DATEDIFF(dd,@StartDate, @EndDate)+1)--Start with total number of days including weekends +1 Includes the day run
          -(DATEDIFF(wk,@StartDate, @EndDate)*2)--Subtact 2 days for each full weekend
          -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' --If StartDate is a Sunday, Subtract 1
              THEN 1 
              ELSE 0 
          END) 
          -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'--If EndDate is a Saturday, Subtract 1 
              THEN 1 
              ELSE 0 
          END))       
  END
  </code></pre>
  
  <p>But i would also like to take away any existing bank holidays that i can get from a table using similar code to this:</p>
  
  <pre><code>  SELECT COUNT([Date])
    FROM [InvoiceManagement].[dbo].[tblBankHolidays]
    WHERE [Date] BETWEEN '2006-04-14' AND '2006-05-29'--eventually replace dates with @StartDate, @EndDate 
  </code></pre>
  
  <p>Is it possible to stitch the above select into the function so that it will minus any existing bank hols from the result before returning the INT? if so i would be appreciate a walk through on how as i am not strong in TSQL</p>