title: MS SQL - Grouping Sequential Dates (ignoring weekends and holidays) returning a start date and end date whilst doing a count of hours
body: <p>I am hoping someone can help please. I have seen solutions to parts of this issue, but haven't been able to add them all together to pull the data that I need.</p>
  
  <p>I have a table with timesheet entries, each line contains an employee, transaction date, a project code and an amount of hours against it.</p>
  
  <p>Example:</p>
  
  <pre><code>Employee, Date, Code, Hours
  00001, 2016-08-01, FunProject1, 6.0
  00001, 2016-08-01, FunProject2, 2.0
  00045, 2016-08-01, FunProject5, 8.0
  00086, 2016-08-01, FunProject1, 8.0
  </code></pre>
  
  <p>I also have a table which contains our Standard Holiday/Vacation.</p>
  
  <pre><code>HolidayDate, HolidayName
  2016-08-30, SomeoneDiedDay
  2016-09-01, SomeoneWasBornDay
  </code></pre>
  
  <p>What I would like to do is create a report for the hours which sit against a certain code, and group the dates together creating a start date and an end date for the use of that code and then the total amount of hours against it (assuming 8 hours counts as 1 day).</p>
  
  <pre><code>Employee ID, Code, Start Date, End Date, Total Hours, Total Days
  00001, FunProject1, 2016-08-01, 2016-08-15, 88, 11
  00086, FunProject1, 2016-08-01, 2016-08-01, 8, 1
  </code></pre>
  
  <p>The results should ignore the weekends (starting -365 days prior to current date) and any holidays/vacation from the existing table.</p>
  
  <p>I have sort of got half way there with the below:</p>
  
  <pre><code>SELECT Employee, 
         dateadd(d, - row_number() over (partition by Employee order by Date), Date) as StartDate, Date, SUM(Hrs)AS TotalHours
         FROM HrsDetail
  WHERE (Code = 'FunProject1' AND DATEPART(YYYY, Date) = '2017' AND (TotalHours != '0.00'))
  GROUP BY Employee, Date
  </code></pre>
  
  <p>(This code doesn't include a full year but I wanted to break this down to understand each part of what the query was doing and tackle the grouping first). How do I now go about grouping the dates to exclude the holiday table and weekends?</p>
  
  <p>Any suggestions or pointers welcome!</p>