title: Select Query- exclude holidays count from a totaldays count obtain from date range
body: <p>I have a project table having field as <code>name,startdate,enddate</code>,
  using <code>datediff</code> I calculated totaldays and then filter sundays as colname <code>TotalDay_sundayremoved</code>.</p>
  
  <p>I have another table as holidays , now have to filter these holidays over TotalDay_sundayremoved column</p>
  
  <p><strong>DDL:</strong></p>
  
  <pre><code>CREATE TABLE holiday_hd (
        date_hd DATETIME
      , enent_hd VARCHAR(200)
  );
  INSERT INTO holiday_hd
  VALUES 
      ('2013-03-15 00:00:00.000', 'Aniversry Leave'),
      ('2013-08-15 00:00:00.000', 'Independence Day'),
      ('2014-08-15 00:00:00.000', 'Independence Day');
  
  CREATE TABLE Project_pj (
        Name VARCHAR(200)
      , start_date_pj DATETIME
      , ed_date_pj DATETIME
      , expectedend_date_pj DATETIME
  );
  INSERT INTO Project_pj
  VALUES 
      ('aaa', '2012-12-01 00:00:00.000', '2023-07-22 00:00:00.000', NULL),
      ('bbb', '2012-12-01 00:00:00.000', NULL, '2023-07-22 00:00:00.000'),
      ('ccc', '2013-12-01 00:00:00.000', NULL, '2014-07-22 00:00:00.000'),
      ('ddd', '2013-07-01 00:00:00.000', NULL, '2016-07-22 00:00:00.000');
  </code></pre>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>SELECT
        Name
      , start_date_pj
      , ISNULL(ed_date_pj, expectedend_date_pj) AS enddate
      , DATEDIFF(DAY, start_date_pj, ISNULL(ed_date_pj, expectedend_date_pj)) AS No_Days
      , (DATEDIFF(dd, start_date_pj, ISNULL(ed_date_pj, expectedend_date_pj)) + 1)
        - (DATEDIFF(wk, start_date_pj, ISNULL(ed_date_pj, expectedend_date_pj)) * 1)
        - (CASE WHEN DATENAME(dw, start_date_pj) = 'Sunday' THEN 1 ELSE 0 END) AS TotalDay_sundayremoved
  FROM Project_pj
  </code></pre>
  
  <p><strong><a href="http://sqlfiddle.com/#!3/b9d9d/1" rel="nofollow">SQL FIDDLE DEMO</a></strong></p>