How can I exclude weekends and holidays in SQL Server query
<p>I'm creating a query which will adjust the date excluding holidays and weekends.</p>

<p>Example data:</p>

<p><code>Adjusted Date | Adjusted Date(Excluding Holidays and weekends)</code>  </p>

<p><code>02/06/16      | 02/09/16</code></p>

<p>On my example, The date is a weekend and adjusted date becomes Feb 9 because Feb 8 is a holiday, so it needs to adjust so that the adjusted date would be a working day. Currently, I have a separated table of all the weekends and holidays in a fiscal year. </p>

<pre><code>select  case when (
               select   count(dbo.WeekendsHoliday.[Weekends &amp; Holidays])
               from     dbo.WeekendsHoliday
               where    dbo.WeekendsHoliday.[Weekends &amp; Holidays] 
                    = case when convert(time, [Time Received]) &gt; convert(time, '5:00:00 PM') 
                           then dateadd(day, 1, [Date Received]) 
                           else [Date Received] 
                      end
              ) &gt; 0 
         then case (datename(DW, 
                             case when convert(time, [Time Received]) &gt; convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end))
                when 'Saturday'
                then dateadd(day, 2, 
                             case when convert(time, [Time Received]) &gt; convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end)
                else dateadd(day, 1, 
                             case when convert(time, [Time Received]) &gt; convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end)
              end
    end as [Adjusted Date Excluding holidays and weekends]
</code></pre>

<p>What happens here is if the holiday is 2 consecutive days (Thursday and Friday), adjusted date would be Saturday which is still not valid because it's a weekend.</p>

<p><em>Adjusted date is an alias here</em></p>