title: sql calculate business hours for weekdays by opening and closing hours range
body: <p>I want to calculate the business hour between given date range (Opening day and closing day)</p>
  
  <p>for example: Business working hours</p>
  
  <blockquote>
    <p><code>ID        WeekName     OpeningHour    ClosingHour</code></p>
    
    <p><code>01        Monday       8.00AM        4.00PM</code></p>
    
    <p><code>02        Tuesday      8.00AM        4.00PM</code></p>
    
    <p><code>03        Wednesday    8.00AM        4.00PM</code></p>
    
    <p><code>04        Thursday     8.00AM        4.00PM</code></p>
    
    <p><code>05        Friday       8.00AM        4.00PM</code></p>
  </blockquote>
  
  <p>If I give a date range for example <br>
  Opening day: 01/04/2014 09.00AM <br>
  Closing day: 03/04/2014 14.00 AM </p>
  
  <p>I want to calculate the business hour from given date range
  there are 3 days between given range </p>
  
  <p>01/04/2014 </p>
  
  <p>02/04/2014 </p>
  
  <p>03/04/2014 </p>
  
  <p>first I want to check the day name and calculate the hour from business hour (from above table) .</p>
  
  <p>01/04/2014 - Tuesday</p>
  
  <p>02/04/2014 - Wednesday </p>
  
  <p>03/04/2014 - Thursday </p>
  
  <p>Results I expecting:</p>
  
  <blockquote>
    <p><code>GivenDate    DayID    DayName    DateDiff</code></p>
    
    <p><code>01/04/2014     01        Tuesday         7</code></p>
    
    <p><code>02/04/2014     02        Wednesday       8</code></p>
    
    <p><code>03/04/2014     03        Thursday        6</code></p>
  </blockquote>